Mark duplicate records in a Select
-
I have a requirement to mark duplicate records when I pull them from the database. However, I only want to mark the 2nd, 3rd, 4th etc record - not the first one. The code I have below creates a column called Dupes but marks all the duplicates - including the first one. Is there a way to only mark the 2nd, 3rd, 4th etc record ? SELECT *, cs.CallStatusDescription as CSRStatusDesc, cs2.CallStatusDescription as CustomerStatusDesc, (Select MAX(CallAttemptNumber)From CallResults cr Where cl.Id = cr.CallLogId) as CallAttemptNumber, Dupes = (select count(id) from CallLogs where (CustomerHomePhone != '' AND cl.CustomerHomePhone = CustomerHomePhone) OR (CustomerBusinessPhone != '' AND cl.CustomerBusinessPhone = CustomerBusinessPhone) AND DealerId= 'hdsh' AND CSRStatus IS NULL and datediff(d, logdate, getdate()) <= 21), FROM CallLogs cl left Join CallStatus cs on cs.Id = cl.CSRstatus left Join CallStatus cs2 on cs2.Id = cl.Customerstatus Where SaleStage IN ('1', '2', '3', '4', '5', '6') And (LogProcessFlag = 1 Or LogProcessFlag = 0) And DealerId='hdsh' And Logdate Between '08/01/2007' And '08/31/2007'
-
I have a requirement to mark duplicate records when I pull them from the database. However, I only want to mark the 2nd, 3rd, 4th etc record - not the first one. The code I have below creates a column called Dupes but marks all the duplicates - including the first one. Is there a way to only mark the 2nd, 3rd, 4th etc record ? SELECT *, cs.CallStatusDescription as CSRStatusDesc, cs2.CallStatusDescription as CustomerStatusDesc, (Select MAX(CallAttemptNumber)From CallResults cr Where cl.Id = cr.CallLogId) as CallAttemptNumber, Dupes = (select count(id) from CallLogs where (CustomerHomePhone != '' AND cl.CustomerHomePhone = CustomerHomePhone) OR (CustomerBusinessPhone != '' AND cl.CustomerBusinessPhone = CustomerBusinessPhone) AND DealerId= 'hdsh' AND CSRStatus IS NULL and datediff(d, logdate, getdate()) <= 21), FROM CallLogs cl left Join CallStatus cs on cs.Id = cl.CSRstatus left Join CallStatus cs2 on cs2.Id = cl.Customerstatus Where SaleStage IN ('1', '2', '3', '4', '5', '6') And (LogProcessFlag = 1 Or LogProcessFlag = 0) And DealerId='hdsh' And Logdate Between '08/01/2007' And '08/31/2007'
id<=c1.id add one more condition (id<=c1.id) to the subquery which Dupes column will generate a sequence num for the subsequent duplicate records. All Dupes with value (1) should be considered as first occurance and rest Dupes value with 2, 3, 4... are 2,3,4... occurances Not sure. just check
Regards
John