One person has different transaction histories

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)

One person has different transaction histories

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-06-28 : 18:12:00

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?

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-06-28 : 23:57:00

still can't figure it out...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources