calculating turnover time for different rows

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)

calculating turnover time for different rows

Author

Topic

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2010-03-18 : 11:15:00

i am trying to solve this query but couldnot and need some help from u folks.

I am trying to calculate the turnover time for an operating room. The time difference from previous patient out of OR to next patient into OR. so it is calculating time difference between different rows and different columns.

an example
ActNo OrRoomID OperationDate IntoOR OutOfOR Turnovertime(minutes)
123 OR1 3/1/2010 3/1/2010 8:00 3/1/2010 8:45
231 OR1 3/1/2010 3/1/2010 9:00 3/1/2010 9:30 15
121 OR1 3/1/2010 3/1/2010 9:50 3/1/2010 10:30 20
125 OR2 3/1/2010 3/1/2010 8:00 3/1/2010 8:45
234 OR2 3/1/2010 3/1/2010 9:00 3/1/2010 9:30 15
450 OR2 3/1/2010 3/1/2010 9:45 3/1/2010 10:45 15

the output should like above, turnovertime is something i calculated manually. for Actno 231 the time difference between previous record out of or 8:45 and next record into or 9:00 which is 15 min.

please post ur suggestions.
Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:02:18

[code]SELECT t.ActNo,
t.OrRoomID,
t.OperationDate,
t.IntoOR,
t.OutOfOR,
DATEDIFF(mi,t1.OutOfOR,t.IntoOR) AS Turnovertime
FROM YourTable t
OUTER APPLY (SELECT TOP 1 OutOfOR
FROM YourTable
WHERE OrRoomID= t.Or RoomID
AND OutOfOR < t.OutOfOR
ORDER BY OutOfOR DESC)t1
[/code]

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

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2010-03-18 : 12:31:53

Thanks Visakh for the qucik respone. It is working fine with one small exception.
It is calculating the turnaround when the operationDate is different for a given room it should be null for the first case of the day in a given room. so in the below example there are few more rows for the OR1 for 3/2/2010 and i want the output to look like this.

ActNo OrRoomID OperationDate IntoOR OutOfOR Turnovertime(minutes)
123 OR1 3/1/2010 3/1/2010 8:00 3/1/2010 8:45
231 OR1 3/1/2010 3/1/2010 9:00 3/1/2010 9:30 15
121 OR1 3/1/2010 3/1/2010 9:50 3/1/2010 10:30 20
120 OR1 3/2/2010 3/2/2010 8:00 3/2/2010 8:45
240 OR1 3/2/2010 3/2/2010 9:00 3/2/2010 9:30 15
333 OR1 3/2/2010 3/2/2010 9:50 3/2/2010 10:30 20
125 OR2 3/1/2010 3/1/2010 8:00 3/1/2010 8:45
234 OR2 3/1/2010 3/1/2010 9:00 3/1/2010 9:30 15
450 OR2 3/1/2010 3/1/2010 9:45 3/1/2010 10:45 15

Thanks for all of ur help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:35:27

[code]
SELECT t.ActNo,
t.OrRoomID,
t.OperationDate,
t.IntoOR,
t.OutOfOR,
DATEDIFF(mi,t1.OutOfOR,t.IntoOR) AS Turnovertime
FROM YourTable t
OUTER APPLY (SELECT TOP 1 OutOfOR
FROM YourTable
WHERE OrRoomID= t.Or RoomID
AND OperationDate = t.OperationDate
AND OutOfOR < t.OutOfOR
ORDER BY OutOfOR DESC)t1
[/code]

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

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2010-03-18 : 12:47:15

Thanks a lot Visakh; it works fine now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 12:48:56

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources