tricky SQL

Last Post 27 Jun 2008 11:43 AM by pccuser. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
pccuser
New Member
New Member

--
27 Jun 2008 11:43 AM
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
You are not authorized to post a reply.

Acceptable Use Policy