Eliminating Data with Multiple occurrences!!!!!

Question

Eliminating Data with Multiple occurrences!!!!!

Hakuna Matata

SSCertifiable

Points: 5577
More actions
May 28, 2011 at 1:00 am

#238269

Hi,
I have a table as shown below with three columns EmpNo,ReferenceNo & Status.
EmpNoReferenceNoStatus
106172210405Closed
106176210646Closed
116453211227Closed
117134207238Issued
134426209206Issued
135496209295Issued
135511209208Closed
135880210632Closed
136656207621Issued
142158210795Issued
142158211158Closed
161133206385Closed
161133210201Issued
The status can be either Issued or Closed in the table. The issue are :
1. For an EmpNo if there are more than 1 ReferenceNo's and if for the multiple reference no's if both the status 'Issued' & 'Closed' are present . I should not retrieve data for that particular EmpNo.
2. At the same time If the EmpNo occurs once & his status is 'Issued'. I should not retrieve the info for that EmpNo also.
For Example
1. From the above table for EmpNo's 142158 & 161133 have multiple ReferenceNo's & the status's are Isuued & Closed. So I should not retrieve info for these EmpNo's .
2. EmpNo's 117134,134426,135496,136656 occurs once & their status is issued, so I should not retrieve data for these EmpNo's
The output should be :
EmpNoReferenceNoStatus
106172210405Closed
106176210646Closed
116453211227Closed
135511209208Closed
135880210632Closed
Please help
Best Regards,
Nithin

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Something like this? It's basically nothing else than to return all rows with a single occurence of empno and the status being 'closed'. Is that what you're looking for?

There are other scenarios not covered in your requirement, e.g. multiple occurences of either 'closed' or 'Issued'. I assumed those are excluded as well.

SELECT *

FROM #temp t1

WHERE status='closed'

AND NOT EXISTS (SELECT 1 FROM #temp t2 WHERE t1.empno=t2.empno AND t1.ReferenceNo <> t2.ReferenceNo)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Damian Widera-396333 Ten Centuries Points: 1196 More actions · Answer 2

Well - you could use ROW_NUMBER() to remove dups howerver I am not sure whether you can use it in the mentioned case - it is too early to think about it and it is Saturday 🙂

Kindest Regards,

Damian Widera
SQL Server MVP,
MCT, MCSE Data Platform, MCSD.NET

Hakuna Matata SSCertifiable Points: 5577 More actions · Answer 3

Thanks a looooooooooooot!!!!!!!!!! for the timely help. Your code worked like a charm!!!!!!!!:-):-):-):-):-):-)

LutzM SSC Guru Points: 107049 More actions · Answer 4

Glad I could help. 😀

But make sure the other conditions I mentioned need to be excluded as per the business case defnition. What I usually do is to build a list of possible scenarios (9 in your case: Zero, one or multiple occurence of either 'closed' or 'Issued') and talk to the folks being responsible for the business case to define the result for each scenario. I would then test the code against each scenario.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]