Duplicate Query Failing to return the correct count

Question

Post reply

Duplicate Query Failing to return the correct count

barry.nielson

SSC Eights!

Points: 912

June 24, 2019 at 12:08 am

#3656760

Hi All,

WITH TempTable (
DuplicateCount, 
CustomerID, 
ActivityID, 
[RelatedReferenceID],
[RelatedReferenceType], 
[Due Date],
[TableType], 
[Team Member], 
[TableStatus]
)
AS
(
SELECT ROW_NUMBER() OVER(PARTITION by CustomerID, ActivityID,[RelatedReferenceType], [Due Date] ORDER BY [Due Date]), 
CustomerID, 
ActivityID, 
[RelatedReferenceID],
[RelatedReferenceType], 
[Due Date],
[TableType], 
[Team Member], 
[tableStatus] 
AS DuplicateCount
FROM Tbl_MyTable
)
Select * from TempTable

The problem here is that the correct duplicates are not being returned. Records matching many of these criteria are being returned as duplicate, but they are not. For example, the CustomerID is being grouped where the ActivityID, ReferenceType and DueDate all match.

There has to be a better way. What is it cluey person?

Cheers

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 1

use "distinct" at the bottom of your CTE, right after "select" on the line where you have the "windowing" (over.. partition..)

I am not 100% sure, but, give it a shot

This reply was modified 5 years, 5 months ago by John Esraelo-498130.

Cheers,
John Esraelo

barry.nielson SSC Eights! Points: 912 More actions · Answer 2

Thanks for your quick reply John,

Unfortunately that did not yield a result.

However, I tried something else.

SELECT
T.QTY,
T.CustomerID, 
T.ActivityID,
T.RelatedReferenceID, 
T.[Due Date], 
T.[RecordStarted],
T.[TableSummary], 
T.[TableType], 
T.[Team Member], 
T.[TableStatus]
FROM (
SELECT
S.*
, ROW_NUMBER() OVER (PARTITION by CustomerID, ActivityID, RelatedReferenceID, [Due Date] ORDER BY [Due Date]) AS QTY
FROM Tbl_MyTable S
) T
WHERE T.QTY > 1 and [TableStatus] <>'Closed' AND ActivityID = 58
ORDER BY T.CustomerID, T.ActivityID

This appeared to work, However, I do not know how to delete the second returned record in the query. whereas with my first posting I could just say:

Delete From Tbl_MyTable rather than Select From Tbl_MyTable

I do not want to delete the first instance of the record. only the second instance.

Thanks again

c.johnson 47724 Grasshopper Points: 22 More actions · Answer 3

Could you add TableStatus to the window function. Perhaps a customer has the same issue as before - It's hard to say exactly without knowing more about the table structure... like is ReferenceID a FK or PK? - I'm probably wrong but figured I'd try to help. Goodluck!