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'
Can't you just decrement the 'count' statement by 1 before assigning it to Dupes?
1Dupes = (SELECTcount(id)
2FROM CallLogs
3WHERE(CustomerHomePhone !=''AND cl.CustomerHomePhone = CustomerHomePhone)
4 OR (CustomerBusinessPhone !=''AND cl.CustomerBusinessPhone = CustomerBusinessPhone)
5AND DealerId='hdsh'
6 AND CSRStatusISNULL
7 ANDdatediff(d, logdate,getdate()) <= 21))- 1
That way it just accounts for itself by subtracting 1 from the total|||
The problem is that both records get the same Dupe number.
I need the 2nd "dupe" to be marked as 2, the 3rd "dupe" to be marked as 3 etc
No comments:
Post a Comment