Query problem - 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)

Query problem

Author

Topic

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-09-24 : 12:58:04

Hi guys,

Consider the following :

KEY.......EFFDATE.......CATEGORY
1.........01/10/1996....B
1.........01/11/1996....LC
1.........01/01/2003....A

What the above 3 records represent is a person being part of a category, or in pensions (joins a benefit). It is obvious from the above that the person has joined (or switched benefits) twice. He started off in B then switched to LC and finally settled on A.

What I want to have is something like the following :

KEY.......EFFDATE.......CATEGORY
1.........01/10/1996....Joins B
1.........31/10/1996....leaves B
1.........01/11/1996....Joins LC
1.........31/12/2002....Leaves LC
1.........01/01/2003....Joins A

Note that the data that I am dealing with involves many members with some members having one single benefit (nothing needs to be done to those as they will only have one record showing them as having joined the benefit).

Here is some sample data for you to help me with this query.

[CODE]
CREATE TABLE BENEFITS (MEMBERKEY INT , EFFDATE CHAR(10), CATEGORY CHAR(2))

INSERT INTO BENEFITS(MEMBERKEY, EFFDATE, CATEGORY)
SELECT 1,'01/10/1996', 'B' UNION ALL
SELECT 1,'01/11/1996','LC' UNION ALL
SELECT 1,'01/01/2003','A' UNION ALL
SELECT 5,'02/07/1990','B' UNION ALL
SELECT 5,'01/11/1992','LC' UNION ALL
SELECT 5,'01/01/2003','A' UNION ALL
SELECT 6,'24/09/1990','B' UNION ALL
SELECT 6,'01/01/1991','LC' UNION ALL
SELECT 6,'01/01/2003','A'
[/CODE]

Thanks for your help in advance.

__________________
Make love not war!

nr
SQLTeam MVY

12543 Posts

Posted - 2003-09-24 : 13:22:55

Something like

select Key, EffDate, Category = 'Joins ' + Category
from BENEFITS
union all
select t1.Key, EffDate = dateadd(dd,-1,t2.EffDate), Category = 'Leaves ' + t1.Category
from BENEFITS t1
join BENEFITS t2
on t1.Key = t2.Key
and t2.EffDate = (select min(t3.EffDate) from BENEFITS t3 where t1.Key = t3.Key and t3.EffDate > t1.EffDate
order by Key, EffDate

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

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-09-25 : 04:51:00

Thank you very much nr. Your solution works very well.

Much appreciated.

__________________
Make love not war!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources