Grouping with status - SQL Server Forums

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

Transact-SQL (2000)

Grouping with status

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-08 : 19:40:05

Here iam stuck to get a combination of query for reportings.....

Create table tbl_emp_status
(
emp_id varchar(20),
std_id int,
status char(1)
)

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1005370,2323,'N')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1005370,2325,'N')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1005370,2326,'Y')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1054170,3339,'N')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1054170,3337,'N')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1054170,3446,'N')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1054179,2339,'Y')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1054179,2337,'Y')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1054179,2446,'Y')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1054199,337,'Y')

insert into tbl_emp_status
(emp_id,std_id,status)
values
(1054199,446,'N')

Here I need 3 types of query :-
1.I need the emp_id and std_id with the combinations having just status as only 'Y'
2.I need the emp_id and std_id with the combinations having just status as only 'N'
3.I need the emp_id and std_id with the combinations having just status as 'N' and only one 'Y'

case 1 should give
1054179,2339,'Y'
1054179,2337,'Y'
1054179,2446,'Y'

case 2 should give

1054170,3339,'N'
1054170,3337,'N'
1054170,3446,'N'

case 3 should give

1005370,2323,'N'
1005370,2325,'N'
1005370,2326,'Y'

1054199,337,'Y'
1054199,446,'N'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-02-08 : 19:54:11

Here ya go:



set nocount on

Create table tbl_emp_status(emp_id varchar(20),std_id int,status char(1))

insert into tbl_emp_status(emp_id,std_id,status)values(1005370,2323,'N')
insert into tbl_emp_status(emp_id,std_id,status)values(1005370,2325,'N')
insert into tbl_emp_status(emp_id,std_id,status)values(1005370,2326,'Y')
insert into tbl_emp_status(emp_id,std_id,status)values(1054170,3339,'N')
insert into tbl_emp_status(emp_id,std_id,status)values(1054170,3337,'N')
insert into tbl_emp_status(emp_id,std_id,status)values(1054170,3446,'N')
insert into tbl_emp_status(emp_id,std_id,status)values(1054179,2339,'Y')
insert into tbl_emp_status(emp_id,std_id,status)values(1054179,2337,'Y')
insert into tbl_emp_status(emp_id,std_id,status)values(1054179,2446,'Y')
insert into tbl_emp_status(emp_id,std_id,status)values(1054199,337,'Y')
insert into tbl_emp_status(emp_id,std_id,status)values(1054199,446,'N')

select emp_id, std_id, status
from tbl_emp_status
where status = 'Y' and emp_id not in (select emp_id from tbl_emp_status where status = 'N')

select emp_id, std_id, status
from tbl_emp_status
where status = 'N' and emp_id not in (select emp_id from tbl_emp_status where status = 'Y')

select t1.emp_id, t1.std_id, t1.status
from tbl_emp_status t1
inner join 
(
	select emp_id
	from tbl_emp_status
	where status = 'y'
	group by emp_id
	having count(*) = 1
) t2 
ON t1.emp_id = t2.emp_id

drop table tbl_emp_status

Tara

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2005-02-08 : 22:38:17

It worked tara...Thanks a lot...........

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources