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 2005 Forums
 Transact-SQL (2005)
 tricky SQL - health care business

Author  Topic 

safderalimd
Starting Member

32 Posts
Posted - 2008-06-27 : 14:16:28
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

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts
Posted - 2008-06-27 : 15:12:44
[code]
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')



select client_id,Row_Number() over (order by Effective_Date) as StayNumber,effective_date as StartDate
into #Tmp
from #Temp_Census a
where event_desc in ('admission', 'new payer start', 'discharge')

Select *, (select StartDate from #Tmp aa where aa.StayNumber = a.StayNumber + 1) as EndDate
from #Tmp a
[/code]
Go to Top of Page

safderalimd
Starting Member

32 Posts
Posted - 2008-06-27 : 17:23:57
Thanks Vinnie881 for the SQL.

By any chance can I come up with single Select SQL directly against #Temp_Census table? I want to avoid extra insert into #tmp table.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-28 : 02:28:38
[code];With Patient_CTE(RowNo,client_id,event_desc,effective_date) AS
(SELECT ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY effective_date),
client_id,event_desc,effective_date
FROM temp_census)

SELECT c1.client_id,
row_number() over(partition by c1.client_id order by c1.effective_date) as stay_number,
c1.effective_date as start_date,
c2.effective_date as end_date,
FROM Patient_CTE c1
CROSS APPLY(SELECT effective_date
FROM Patient_CTE c1
WHERE RowNo =c1.RowNo + 1
AND client_id=c1.client_id
AND event_desc<>'admission')c2[/code]
Go to Top of Page

safderalimd
Starting Member

32 Posts
Posted - 2008-06-28 : 03:55:34
THANKS A LOT:)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-28 : 04:05:04
quote:
Originally posted by safderalimd

THANKS A LOT:)

You're welcome
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site