June 27, 2008 at 12:33 pm
How can I convert first result set to look like second one??? SQL to create and populate table is at bottom.
Result Set I (sql for result set I is provided at bottom)
(client_id) (event_desc) (effective_date)
----------- ------------------------------ -----------------------
(123432) (admission) (2008-03-21)
(123432) (new payer start) (2008-03-24)
(123432) (new payer start) (2008-03-27)
(123432) (discharge) (2008-04-10)
(123432) (admission) (2008-04-21)
(123432) (new payer start) (2008-04-24)
(123432) (new payer start) (2008-04-27)
(123432) (discharge) (2008-05-10)
Result set II
(client_id) (stay_number) (start_date) (end_date)
----------- ----------- ----------------- -----------------------
(123432) (1) (2008-03-21) (2008-03-24)
(123432) (2) (2008-03-24) (2008-03-27)
(123432) (3) (2008-03-27) (2008-04-10)
(123432) (4) (2008-04-21) (2008-04-24)
(123432) (5) (2008-04-24) (2008-04-27)
(123432) (6) (2008-04-27) (2008-05-10)
Note:
1)admission to new payer is one stay
2)new payer to next new payer is one stay
3)new payer to discharge is one stay
SQL to generate table and test data
create table temp_census(client_id int, event_desc varchar(30),effective_Date datetime, ineffective_Date datetime, eoc_flag char(1))
--truncate table temp_census
insert into temp_census values(123432, 'admission', '2008-3-21', '2008-3-24','S')
insert into temp_census values(123432, 'new payer start', '2008-3-24', '2008-3-25',null)
insert into temp_census values(123432, null, '2008-3-25', '2008-3-26',null)
insert into temp_census values(123432, null, '2008-3-26', '2008-3-27',null)
insert into temp_census values(123432, 'new payer start', '2008-3-27', '2008-3-28',null)
insert into temp_census values(123432, null, '2008-3-28', '2008-3-29',null)
insert into temp_census values(123432, 'leave start', '2008-3-29', '2008-4-1',null)
insert into temp_census values(123432, 'return from leave', '2008-4-1', '2008-4-3',null)
insert into temp_census values(123432, null, '2008-4-3', '2008-4-5',null)
insert into temp_census values(123432, 'transfer OUT hospital', '2008-4-5', '2008-4-7',null)
insert into temp_census values(123432, 'transfer IN hospital', '2008-4-7', '2008-4-8',null)
insert into temp_census values(123432, null, '2008-4-8', '2008-4-10',null)
insert into temp_census values(123432, 'discharge', '2008-4-10','2008-4-10','E')
insert into temp_census values(123432, 'admission', '2008-4-21', '2008-4-24','S')
insert into temp_census values(123432, 'new payer start', '2008-4-24', '2008-4-25',null)
insert into temp_census values(123432, null, '2008-4-25', '2008-4-26',null)
insert into temp_census values(123432, null, '2008-4-26', '2008-4-27',null)
insert into temp_census values(123432, 'new payer start', '2008-4-27', '2008-4-28',null)
insert into temp_census values(123432, null, '2008-4-28', '2008-4-29',null)
insert into temp_census values(123432, 'leave start', '2008-4-29', '2008-5-1',null)
insert into temp_census values(123432, 'return from leave', '2008-5-1', '2008-5-3',null)
insert into temp_census values(123432, null, '2008-5-3', '2008-5-5',null)
insert into temp_census values(123432, 'transfer OUT hospital', '2008-5-5', '2008-5-7',null)
insert into temp_census values(123432, 'transfer IN hospital', '2008-5-7', '2008-5-8',null)
insert into temp_census values(123432, null, '2008-5-8', '2008-5-10',null)
insert into temp_census values(123432, 'discharge', '2008-5-10','2008-5-10','E')
SQL for Result set I
select client_id, event_desc,effective_date
from temp_census
where event_desc in ('admission', 'new payer start', 'discharge')
order by effective_Date asc
June 27, 2008 at 12:53 pm
You could do a join between the table and itself, something like:
;with
CTE1 as
(select client_id, t1.effective_date, t2.effective_date as end_date
from dbo.temp_census t1
inner join dbo.temp_census t2
on t1.client_id = t2.client_id
and t1.effective_date < t2.effective_date
and
(t1.event_description = 'admission'
and
t2.event_description = 'new player start'
or
t1.event_description = 'new player start'
and
t2.event_description = 'new player start')),
CTE2 as
(select client_id, effective_date, min(end_date) as end_date
from CTE1
group by client_id, effective_date)
select client_id,
row_number() over
(partition by client_id
order by effective_date),
effective_date, end_date
from CTE2
You'll have to expand that to include all your "next event" conditions, etc. But the principle is sound.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 30, 2008 at 3:25 am
--Try This
--However this is not much optimized but will serve your purpose
SELECT client_id,Row_Number() OVER (ORDER BY start_date ASC) stay_number,start_date,end_date
FROM (
SELECT client_id,
Case When event_desc='discharge' Then Null Else effective_date End start_date,
(SELECT MIN(effective_date) From temp_census
WHERE client_id=A.client_id AND effective_date>A.effective_date AND
event_desc in ('admission', 'new payer start', 'discharge')) end_date
FROM temp_census A
where event_desc in ('admission', 'new payer start', 'discharge')
) AS Temp
WHERE start_date IS NOT NULL
July 2, 2008 at 11:43 am
Thanks:)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply