Finding Duplicated Data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Finding Duplicated Data

Author

Topic

cardnal0602
Starting Member

11 Posts

Posted - 2012-09-14 : 10:51:36

Hey

I have a table that has invoice and batch columns, and our old system sometimes shoots our the same invoices into multiple batches. (which is bad because we could end up double-charging our customers).

So the data would simply be:

InvoiceID BatchID
---------- --------
12345 44444
22345 55555
33345 66666
44445 77777
12345 88888

So using the data above, the result of the query I need to write will output the following:

InvoiceID BatchID
---------- ---------
12345 44444
12345 88888

I have tried using "having count(batchID) > 1" in my where clause of my test queries, but it's not quite right.

Can you help?
Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 10:56:32

[code]
SELECT InvoiceID,BatchID
FROM
(
SELECT COUNT(BatchID) OVER (PARTITION BY InvoiceID) AS Occ,*
FROM table
)t
WHERE Occ > 1

or

SELECT t.InvoiceID,t.BatchID
FROM Table t
INNER JOIN (SELECT InvoiceID
FROM Table
GROUP BY InvoiceID
HAVING COUNT(BatchID) >1
)t1
ON t1.InvoiceID = t.InvoiceID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cardnal0602
Starting Member

11 Posts

Posted - 2012-09-14 : 10:59:35

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 11:01:05

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cardnal0602
Starting Member

11 Posts

Posted - 2012-09-14 : 11:44:19

Thanks for your help. I think I left out one particular part about the batch numbers. I am trying to find all invoice numbers(invoiceID) that are found in 'different' batches(batchID).

Is there a way to modify your query to only return results where the a particular invoiceID is found in more than one batchID?

Your queries are very close, they just end up pulling all invoiceIDs and batchID's without weeding out the good ones.

So if I had an InvoiceID of 12345, I would like it to show me how many different batchID's it's in.
But the trick is to only show me data where that invoiceID is in more than one batchID.

Thanks again for your help. You have gotten me way closer than I was!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 16:34:59

[code]
SELECT t.InvoiceID,t.BatchID
FROM Table t
INNER JOIN (SELECT InvoiceID
FROM Table
GROUP BY InvoiceID
HAVING COUNT(DISTINCT BatchID) >1
)t1
ON t1.InvoiceID = t.InvoiceID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources