Author |
Topic |
gabivas
Starting Member
10 Posts |
Posted - 2011-10-03 : 09:35:07
|
Hello – I am new to SQl and I was wondering if anyone can provide some help or advice on how I can solve this problem.I need to determine the LOS (Length of stay) where of a person through different rooms. Here is my dataID room bed unit transferTime 0001 A00 A01 A 2009-03-25 00:30:000001 A02 A02 A 2009-03-25 06:56:000001 B01 B02 B 2009-03-25 17:11:000001 C01 C01 C 2009-03-27 12:00:000001 A03 A03 A 2009-03-28 12:02:000001 D01 D01 D 2009-04-03 15:25:00I will need to find out the total LOS of the person that spent in unit A – for example; initially was admitted on A on 2009-03-25 00:30:00 and discharged on 2009-03-25 17:11:00 ( that is when he was admitted on B – he left A); in this case the time is xx hours.Next he gets admitted again on A in 2009-03-28 12:02:00 and discharged (admitted to D) on 2009-04-03 15:25:00 – in this case the time is XX.I will need to add these times together.Thank you.Adrian |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-03 : 13:03:18
|
how do you determine which's the discharge record and which is admitted one?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
paultech
Yak Posting Veteran
79 Posts |
|
gabivas
Starting Member
10 Posts |
Posted - 2011-10-04 : 08:27:39
|
Well the first occurrence of unit A (2009-03-25 00:30:00) will be the admission time. When the person gets admitted to a different room ( B on 2009-03-25 17:11:00) that will become the discharge time from A.When, again, the person gets admitted to A on 2009-03-28 12:02:00 that will be admission time and then discharge time when he gets admitted to D on 2009-04-03 15:25:00.Thank you. |
|
|
gabivas
Starting Member
10 Posts |
Posted - 2011-10-04 : 08:29:08
|
I will need to keep track of different room - that function will not work.Thank you for your help though.Adrian |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-04 : 09:15:04
|
ok. so whats the way of identifying it then?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
gabivas
Starting Member
10 Posts |
Posted - 2011-10-04 : 09:34:38
|
Its the unit - I will need the total time spent in unit A. This will be, in our example:(2009-03-25 00:30:00 - 2009-03-25 17:11:00)+ (2009-03-28 12:02:00 - 2009-04-03 15:25:00)Thank you. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-04 : 12:35:51
|
[code]SELECT unit,SUM(Time)FROM(SELECT unit,DATEDIFF(ss,MAX(InTime),MAX(OutTime)) AS TimeFROM(SELECT t.unit,t.transferTime AS InTime,CAST(NULL AS datetime) AS OutTimeROW_NUMBER() OVER (PARTITION BY t.unit ORDER BY t.transferTime) AS SeqFROM table tOUTER APPLY(SELECT TOP 1 transferTime,unit FROM table WHERE transferTime<t.transferTime ORDER BY transferTime DESC) t1WHERE (t1.unit <> t.unit OR t1.unit IS NULL)UNION ALLSELECT t.unit,NULL,COALESCE(t2.transferTime,GETDATE()),ROW_NUMBER() OVER (PARTITION BY t.unit ORDER BY COALESCE(t2.transferTime,GETDATE()))FROM table tOUTER APPLY(SELECT TOP 1 transferTime,unit FROM table WHERE transferTime>t.transferTime ORDER BY transferTime ASC) t2WHERE (t2.unit<> t.unit OR t2.unit IS NULL))tGROUP BY unit,Seq)rGROUP BY unit[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
gabivas
Starting Member
10 Posts |
Posted - 2011-10-04 : 13:16:58
|
WOWOK - please let me see if I can understand the code; it seems complicated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-04 : 13:18:20
|
welcome ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
jassi.singh
Posting Yak Master
122 Posts |
Posted - 2011-10-05 : 02:55:03
|
Hi,You find difference i.e. amount of total time spent in each unit using group by on unit and if you are not able to identify row then use row_number to identifyPlease mark answer as accepted if it helped you.Thanks,Jassi Singh |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-05 : 03:07:33
|
quote: Originally posted by jassi.singh Hi,You find difference i.e. amount of total time spent in each unit using group by on unit and if you are not able to identify row then use row_number to identifyPlease mark answer as accepted if it helped you.Thanks,Jassi Singh
Please stop doing thisthere's no such facility in this forum------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
gabivas
Starting Member
10 Posts |
Posted - 2011-10-05 : 08:35:03
|
Well it did not work - syntax error and I can't figure out your code. Where do you have the clause that specify unit A - I did not see it anywhere. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-05 : 08:39:50
|
i gave a generic solutions which gives all units with time spent against each. if you want to restrict it only for unit A you need to add filter like belowSELECT unit,SUM(Time)FROM(SELECT unit,DATEDIFF(ss,MAX(InTime),MAX(OutTime)) AS TimeFROM(SELECT t.unit,t.transferTime AS InTime,CAST(NULL AS datetime) AS OutTime,ROW_NUMBER() OVER (PARTITION BY t.unit ORDER BY t.transferTime) AS SeqFROM table tOUTER APPLY(SELECT TOP 1 transferTime,unit FROM table WHERE transferTime<t.transferTime ORDER BY transferTime DESC) t1WHERE (t1.unit <> t.unit OR t1.unit IS NULL)AND t.unit = 'unit A'UNION ALLSELECT t.unit,NULL,COALESCE(t2.transferTime,GETDATE()),ROW_NUMBER() OVER (PARTITION BY t.unit ORDER BY COALESCE(t2.transferTime,GETDATE()))FROM table tOUTER APPLY(SELECT TOP 1 transferTime,unit FROM table WHERE transferTime>t.transferTime ORDER BY transferTime ASC) t2WHERE (t2.unit<> t.unit OR t2.unit IS NULL)AND t.unit = 'unit A')tGROUP BY unit,Seq)rGROUP BY unit ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
gabivas
Starting Member
10 Posts |
Posted - 2011-10-05 : 08:48:09
|
Is "table" the name of the table used?I'll try again. |
|
|
|