December 18, 2012 at 9:48 am
I have a query that selects duplicates by using the HAVING COUNT(*) > 1 statement. I only want accounts that are not closed to be included in the results set also. See below:
SELECT Account_Number
FROM Account
WHERE Status != 'CLOSED'
GROUP BY Account_Number
HAVING COUNT (*) > 1
This seems to work great for 2 account duplicates...but when there are 3 account duplicates and atleast one of them have the status CLOSED then it still shows up in the results set.
Am I doing this correctly?
December 18, 2012 at 10:18 am
Don't have test data from you to check but I believe something like this will work. Approach is to identify all closed accounts by CTE and then exclude them in the final select.
With Closed
As
(
SELECT Account_Number
FROM Account
WHERE Status = 'CLOSED'
)
SELECT Account_Number
FROM Account a
Left Outer Join Closed c On a.Account_Number = c.Account_Number
WHERE c.Account_Number is null
GROUP BY a.Account_Number
HAVING COUNT (*) > 1
;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply