0

I am working with a dataset of inpatient data in SQL Server 2017. The fields contain anonymized ID, admission date, discharge date and other relevant fields. Since some of the patients are readmitted, the ID field is not unique. The table looks like this:

ID       AdmDate1      DischDate    
10001   2012-10-16    2012-10-26 
10001   2014-06-15    2014-06-18 
10001   2014-12-21    2014-12-29 
10002   2013-02-14    2013-02-20 
10003   2013-01-23    2013-01-31 
10004   2012-11-15    2012-11-19 
10004   2014-09-26    2014-10-06 
10005   2014-12-12    2014-12-23 
10006   2013-10-23    2013-10-28 

Since I want to calculate readmission rate and intervals between readmissions, I want to create a table like this:

ID       AdmDate1     AdmDate2    AdmDate3   DischDate1 DischDate2 DischDate3   
10001   2012-10-16   2014-06-15  2014-12-21
10002   2013-02-14   None        None
  • The problem for me is that you have no real amount of max adm dates + dischDates. While it is possible through some form of query, it will go really slow when data gets bigger. (looping over all the records). Your dataset returned will also get bigger and bigger horizontically, which is not ideal. – Randi Vertongen Nov 10 '18 at 20:53
  • It would be easy enough to do using a dynamic pivot... but... based on your use case, I don't think you want to. Having your rows pivoted to columns will make your analysis far more difficult than it needs to be. Use a pivot in the final display if you want but keep in rows to do the actual math. – Jason A. Long Nov 11 '18 at 3:45
  • Thank you @RandiVertongen. I have counted the rows in the columns of AdmDate1 and found the maximum admission frequency is 35. Since readmissions more than 4 are not many, I can remove the rows with rn>4 for AdmDate1 and DischDate. by doing this, the number of columns to be created will be reduced to manageable numbers. – Mel Nov 11 '18 at 5:20
1

I have counted the rows in the columns of AdmDate1 and found the maximum admission frequency is 35. Since readmissions more than 4 are not many, I can remove the rows with rn>4 for AdmDate1 and DischDate. by doing this, the number of columns to be created will be reduced to manageable numbers

Based on this reply i started looking for a solution which uses 4 of admdates and 4 of Dischdates.

So this works, but I don't dare look at the query plan

Create test data

create table AdmissionDates(id int, AdmDate1 date,  DischDate date)
insert into AdmissionDates(id,AdmDate1,DischDate)
VALUES(  10001 ,  '2012-10-16' ,  '2012-10-26'),
(10001 ,  '2014-06-15'   , '2014-06-18')  ,
(10001 ,  '2014-12-21'   , '2014-12-29')  ,
(10002 ,  '2013-02-14'   , '2013-02-20')  ,
(10003 ,  '2013-01-23'   , '2013-01-31')  ,
(10004 ,  '2012-11-15'   , '2012-11-19')  ,
(10004 ,  '2014-09-26'   , '2014-10-06')  ,
(10005 ,  '2014-12-12'   , '2014-12-23')  ,
(10006 ,  '2013-10-23'   , '2013-10-28')  

select id,row_number() over( partition by id order by admDate1 asc) as rownum, AdmDate1,row_number() over( partition by id order by DischDate asc) as rownum2, DischDate
into #temp
from AdmissionDates;

The Query

select DISTINCT id, 
                t2.admDate1 as admDate1,
                t3.admDate1 as admDate2,
                t4.admDate1 as admDate3,
                t5.admDate1 as admDate4, 
                T6.DischDate as DischDate1,
                T7.DischDate as DischDate2,
                T8.DischDate as DischDate3,
                T9.DischDate as DischDate4
FROM 
#temp t1
OUTER APPLY 
(select admDate1 from #temp T2 where rownum = 1 and T2.id = T1.id) as T2
OUTER APPLY 
(select admDate1 from #temp T2 where rownum = 2 and T2.id = T1.id) as T3
OUTER APPLY 
(select admDate1 from #temp T2 where rownum = 3 and T2.id = T1.id) as T4
OUTER APPLY 
(select admDate1 from #temp T2 where rownum = 4 and T2.id = T1.id) as T5
OUTER APPLY 
(select DischDate from #temp T2 where rownum2 = 1 and T2.id = T1.id) as T6
OUTER APPLY 
(select DischDate from #temp T2 where rownum2 = 2 and T2.id = T1.id) as T7
OUTER APPLY 
(select DischDate from #temp T2 where rownum2 = 3 and T2.id = T1.id) as T8
OUTER APPLY 
(select DischDate from #temp T2 where rownum2 = 4 and T2.id = T1.id) as T9;


DROP TABLE #temp;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.