SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Finding Duplicated Data		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cardnal0602
Starting Member

8 Posts
Posted - 09/14/2012 :  10:51:36  Show Profile  Reply with Quote
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

India
47069 Posts
Posted - 09/14/2012 :  10:56:32  Show Profile  Reply with Quote 

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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

cardnal0602
Starting Member

8 Posts
Posted - 09/14/2012 :  10:59:35  Show Profile  Reply with Quote
Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47069 Posts
Posted - 09/14/2012 :  11:01:05  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

cardnal0602
Starting Member

8 Posts
Posted - 09/14/2012 :  11:44:19  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47069 Posts
Posted - 09/14/2012 :  16:34:59  Show Profile  Reply with Quote 

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000