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.
| Author |
Topic |
|
Grodnik
Starting Member
1 Post |
 Posted - 2014-12-08 : 16:36:52
|
| I am trying to determine readmits to a hospital by calculating the number of hours from a discharge to the next admit for each patient.I have a table, Inpatients. The data looks like this:ID AdmitDateTime DischargeDateTime 1, 4/22/2013 12:58, 4/26/2013 11:37 1, 4/26/2013 19:41, 8/2/2013 12:58 1, 12/1/2013 10:30, 12/8/2013 12:25 2, 12/15/2013 13:37, 12/31/2013 23:04 2, 1/1/2014 9:05, 1/12/2014 10:13I want to calculate the hours so the table looks like this:ID AdmitDateTime DischargeDateTime Hours 1, 4/22/2013 12:58, 4/26/2013 11:37, NULL 1, 4/26/2013 19:41, 8/2/2013 12:58, 7 1, 12/1/2013 10:30, 12/8/2013 12:25, 2880 (my estimate) 2, 12/15/2013 13:37, 12/31/2013 23:04, NULL 2, 1/1/2014 9:05, 1/12/2014 10:13, 10Thank you for your help on this. I suspect I'm about to learn something valuable. |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-12-08 : 19:07:54
|
Try this:with cte as (select * ,row_number() over(partition by id order by id,admitdatetime) as rn from inpatients )select a.id ,a.admitdatetime ,a.dischargedatetime ,datediff(hour,b.dischargedatetime,a.admitdatetime) as hours from cte as a left outer join cte as b on b.id=a.id and b.rn=a.rn-1 |
 |
|
|
|
|
|
|
|
Datediff between rows?