question

pacman2255 avatar image
pacman2255 asked

Duplicate invoice, invoice amt per vendor.

I need to display the duplicate rows on the following: vendor num, invoice num, invoice amt. No ID field. My select statement returns only 1 row for each dup SQL 2000/2005
test
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you supply the code you are using, and perhaps an example of the data in the table(s)? It would also be good if you can explain exactly what you're stuck on, so we can better help you.
1 Like 1 ·
I need to display all the duplicate rows and only the duplicate rows. Here's my code followed by my result set SELECT ApTest.VenNum AS [VenNum Field], ApTest.InvNum AS [InvNum Field], ABS(ApTest.GrossAmt) AS [GrossAmt Field] FROM ApTest GROUP BY ApTest.[VenNum], ApTest.[InvNum],ABS(ApTest.[GrossAmt]) HAVING ((Count(ApTest.VenNum)>1) AND (Count(ApTest.InvNum)>1) AND (Count(ApTest.GrossAmt)>1)); VenNum Field InvNum Field GrossAmt Field 2065 28032700 116810.67 2065 33382500 116810.67 2748 95465689 149159.47 47862 091962 171700
0 Likes 0 ·
What's your expected / required resultset?
0 Likes 0 ·
2065 28032700 116810.67 2065 28032700 116810.67 47862 091962 171700 47862 091962 171700
0 Likes 0 ·
I'm probably being daft, but I'm not sure I'm following which are your keys in that record set (i.e. what constitutes a "duplicate" record) - however I've posted some pseudocode of how I'd look to accomplish this below.
0 Likes 0 ·

1 Answer

·
Dave_Green avatar image
Dave_Green answered
Some pseudo-code to help you in the right direction: Assuming Set A is your first set, Select a set of records from Set A which have more than one row. Call that Set B. Return all the records from Set A where the key is in Set B. This should be returning only the duplicate rows. You could accomplish this using a CTE or a subquery, but other ways are possible.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Clearly a CTE is only possible in SQL 2005 and above - if you're in SQL 2000 you could look at using a subquery (your question states versions 2000 or 2005).
0 Likes 0 ·
Thanks for your help. The duplicate would include the same amount on the same invoice for the same vendor. I've been trying to come up with a subquery but no luck so far. One of my problems is I have to do this against an Access DB. This is part of an aptitude test for a SQL Developer position but have been unable to get this to work in Access.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.