Wery to identify multiple records as Y/N

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 2005 Forums

Transact-SQL (2005)

Wery to identify multiple records as Y/N

Author

Topic

PolarBear
Starting Member

5 Posts

Posted - 2009-02-17 : 16:32:13

I need to identify individuals that have multiple employers.

Data from employment table as follows:
Memb_id Employer_id
1234 856
1234 247
1234 651
444 916

Seeking results as follows:

Memb_ID Multilpe_employers
1234 Y
444 N

Any assistance greatly appreciated.

Regards,
PolarBear

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-17 : 16:34:52

[code]
select memb_id
,case when count(distinct employer_id) > 1 then 'Y' else 'N' end as [Multiple_employers]
from employment
group by memb_id
[/code]

Be One with the Optimizer
TG

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-17 : 16:47:07

[code]Select distinct Memb_id,
(Case when Count(*) over(Partition by Memb_id)>1 then 'Y' Else 'N' End)as Multiple_Employers
from Table
order by Memb_id[/code]

PolarBear
Starting Member

5 Posts

Posted - 2009-02-17 : 22:45:38

Thanks very much for your responses. I now have the information that I required. It works great. I only wish I was more skilled at scripting! Great to see others share their expertise!

Regards,

PolarBear

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources