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
 General SQL Server Forums
 New to SQL Server Programming
 Calculate total time

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 data

ID room bed unit transferTime
0001 A00 A01 A 2009-03-25 00:30:00
0001 A02 A02 A 2009-03-25 06:56:00
0001 B01 B02 B 2009-03-25 17:11:00
0001 C01 C01 C 2009-03-27 12:00:00
0001 A03 A03 A 2009-03-28 12:02:00
0001 D01 D01 D 2009-04-03 15:25:00

I 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

paultech
Yak Posting Veteran

79 Posts

Posted - 2011-10-04 : 08:21:59
seethe following link of how to use datediff function and sum the result

http://bytes.com/topic/sql-server/answers/685104-calculating-total-time-using-datediff

Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

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 Time
FROM
(
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 Seq
FROM table t
OUTER APPLY(SELECT TOP 1 transferTime,unit
FROM table
WHERE transferTime<t.transferTime
ORDER BY transferTime DESC) t1
WHERE (t1.unit <> t.unit OR t1.unit IS NULL)
UNION ALL
SELECT t.unit,NULL,
COALESCE(t2.transferTime,GETDATE()),
ROW_NUMBER() OVER (PARTITION BY t.unit ORDER BY COALESCE(t2.transferTime,GETDATE()))
FROM table t
OUTER APPLY(SELECT TOP 1 transferTime,unit
FROM table
WHERE transferTime>t.transferTime
ORDER BY transferTime ASC) t2
WHERE (t2.unit<> t.unit OR t2.unit IS NULL)
)t
GROUP BY unit,Seq
)r
GROUP BY unit
[/code]





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gabivas
Starting Member

10 Posts

Posted - 2011-10-04 : 13:16:58
WOW

OK - please let me see if I can understand the code; it seems complicated.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 13:18:20
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 identify

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh
Go to Top of Page

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 identify

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh


Please stop doing this
there's no such facility in this forum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

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 below


SELECT unit,SUM(Time)
FROM
(
SELECT unit,
DATEDIFF(ss,MAX(InTime),MAX(OutTime)) AS Time
FROM
(
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 Seq
FROM table t
OUTER APPLY(SELECT TOP 1 transferTime,unit
FROM table
WHERE transferTime<t.transferTime
ORDER BY transferTime DESC) t1
WHERE (t1.unit <> t.unit OR t1.unit IS NULL)
AND t.unit = 'unit A'
UNION ALL
SELECT t.unit,NULL,
COALESCE(t2.transferTime,GETDATE()),
ROW_NUMBER() OVER (PARTITION BY t.unit ORDER BY COALESCE(t2.transferTime,GETDATE()))
FROM table t
OUTER APPLY(SELECT TOP 1 transferTime,unit
FROM table
WHERE transferTime>t.transferTime
ORDER BY transferTime ASC) t2
WHERE (t2.unit<> t.unit OR t2.unit IS NULL)
AND t.unit = 'unit A'
)t
GROUP BY unit,Seq
)r
GROUP BY unit





------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gabivas
Starting Member

10 Posts

Posted - 2011-10-05 : 08:48:09
Is "table" the name of the table used?
I'll try again.
Go to Top of Page
   

- Advertisement -