Eliminating Data with Multiple occurrences!!!!!

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)

Eliminating Data with Multiple occurrences!!!!!

Author

Topic

rajan.nithin
Starting Member

42 Posts

Posted - 2011-05-28 : 04:00:20

Hi,

I have a table as shown below with three columns EmpNo,ReferenceNo & Status.

EmpNo ReferenceNo Status
106172 210405 Closed
106176 210646 Closed
116453 211227 Closed
117134 207238 Issued
134426 209206 Issued
135496 209295 Issued
135511 209208 Closed
135880 210632 Closed
136656 207621 Issued
142158 210795 Issued
142158 211158 Closed
161133 206385 Closed
161133 210201 Issued

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 :
EmpNo ReferenceNo Status
106172 210405 Closed
106176 210646 Closed
116453 211227 Closed
135511 209208 Closed
135880 210632 Closed

Please help

Best Regards,
Nithin

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-28 : 06:11:29

What if the status

select *
from tbl
where EmpNo in
(
select EmpNm
from tbl
group by EmpNo
Having (count(distinct ReferenceNo) = 1 and (max(status) <> 'Issued' or min(Status) <> 'Issued'))
or (count(distinct ReferenceNo) > 1 and (max(status) <> min(Status))
)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rajan.nithin
Starting Member

42 Posts

Posted - 2011-05-28 : 07:08:14

Thanks a looooooooooooot!!!!!!!!!! for the timely help. You Rock

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-06-03 : 15:27:38

If I got the requirement right, I think it can be simpler:

select distinct empNo, referenceNo, 'Closed' as status
from tbl a
where a.status = 'Closed' and
  not exists(select *
               from tbl b
               where b.empNo = a.empNo and
                     b.status = 'Issued'
             )

The question is if it could be employee with muliple rows with status = 'Issued'.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources