Set Based Search

[jung]

CREATE TABLE [dbo].[test] (
[p_id] [float] NULL ,
[ref_id] [float] NULL ,
[status] [nvarchar] (255) ,
[group] [nvarchar] (255),
[change_status_date] [smalldatetime]
) ON [PRIMARY]
GO


insert test values ( 1,1001,'Pending','H','2003-07-04')
insert test values ( 1,1002,'Attend','L','2004-04-24')
insert test values ( 2,1003,'Attend','H','2004-05-24')
insert test values ( 3,1004,'Pending','L','2001-01-04')
insert test values ( 3,1005,'Attend','H','2004-07-04')
insert test values ( 4,1006,'drop','L','2004-07-08')
insert test values ( 5,1007,'Pending','H','2004-01-04')
insert test values ( 5,1008,'Attend','M','2004-07-04')
insert test values ( 6,1009,'Pending','H','2004-01-04')
insert test values ( 6,10010,'Attend','L','2004-01-01')
insert test values ( 7,10011,'Pending','H','2003-07-04')
insert test values ( 7,10011,'drop','H','2003-09-04')

The table looks like:


p_id ref_id status group change_status_date
1 1001 pending H 3/24/2003
1 1002 Attend L 4/24/2004
2 1003 Attend H 5/24/2004
3 1004 pending L 1/4/2001
3 1005 Attend H 7/4/2004
4 1006 Attend H 7/8/2004
5 1007 Pending H 1/1/2004
5 1008 Attend M 2/2/2004
6 1009 Pending H 1/1/2004
6 1010 Drop L 3/2/2004
7 1011 Pending H 4/1/2004
7 1012 Attend L 7/1/2004


I would like to retrieve the P_ids ( person) who have the history of changing status: from pending to Attend. And, count it by group categories.

First, retrive the p_ids who have different ref_ids, changed status(pending to Attend), and changed group category( H to L, L to H, or H to M).

p_id ref_id status group change_status_date
1 1001 pending H 3/24/2003
1 1002 Attend L 4/24/2004
3 1004 pending L 1/2/2004
3 1005 Attend H 7/4/2004
5 1007 Pending H 1/1/2004
5 1008 Attend M 2/2/2004
7 1011 Pending H 4/1/2004
7 1012 Attend L 7/1/2004



And count it by group categories.

So, the final output should be:


Group number
H to L 2
L to H 1
H to M 1



I am looking for the set based solution...
any better idea?

[MAK]

--TRY THIS
CREATE TABLE [dbo].[test] (
[p_id] [float] NULL ,
[ref_id] [float] NULL ,
[status] [nvarchar] (255) ,
[group] [nvarchar] (255),
[change_status_date] [smalldatetime]
) ON [PRIMARY]
GO

insert test values ( 1,1001,'Pending','H','2003-07-04')
insert test values ( 1,1002,'Attend','L','2004-04-24')
insert test values ( 2,1003,'Attend','H','2004-05-24')
insert test values ( 3,1004,'Pending','L','2001-01-04')
insert test values ( 3,1005,'Attend','H','2004-07-04')
insert test values ( 4,1006,'drop','L','2004-07-08')
insert test values ( 5,1007,'Pending','H','2004-01-04')
insert test values ( 5,1008,'Attend','M','2004-07-04')
insert test values ( 6,1009,'Pending','H','2004-01-04')
insert test values ( 6,10010,'Attend','L','2004-01-01')
insert test values ( 7,10011,'Pending','H','2003-07-04')
insert test values ( 7,10011,'drop','H','2003-09-04')
insert test values ( 10,1001,'Pending','H','2003-07-04')
insert test values ( 10,1002,'Attend','L','2004-04-24')
insert test values ( 21,1001,'Pending','H','2003-07-04')
insert test values ( 21,1002,'Attend','L','2004-04-24')




select GroupA,GroupB,count(*) as Number from (
select a.p_id,
MAX( case when b.mindate=[change_status_date] then [GROUP] else NULL end) as GroupA,
MAX(case when b.maxdate=[change_status_date] then [GROUP] else NULL end) as GroupB,
MAX(case when b.mindate=[change_status_date] then status else NULL end) as StatusA,
MAX(case when b.maxdate=[change_status_date] then status else NULL end) as StatusB from
test a,
(select p_id, min([change_status_date] ) as MinDate ,max([change_status_date] ) as MaxDate from test
group by p_id ) as b where a.p_id =b.p_id
group by a.p_id ) as statustable
WHERE statusA='pending' and statusb='attend'
group by GroupA,GroupB