July 8, 2011 at 2:59 pm
I have 6 procedure rooms that perform multiple cases per day. I want to know, for each room, what the time difference is between the end of one case to the start of the next case. I wrote a query that gives me all the cases from a single room and a single day, but I'm struggling how to identify the "previous" case end time. The PK is an automnumber but it is not sequential by room so I can't ask it to look at the next lowest PK. I have an MS Access solution that would work if my PK's were sequential but my goal is to solve this with an SSRS report instead of Access.
Here's some sample data
IDDate RoomTimeOfCaseStartTimeOfCaseEnd
299407/7/2011 Lab 7 8:52:48 AM11:25:00 AM
299447/7/2011 Lab 72:02:08 PM3:40:14 PM
299497/7/2011 Lab 74:25:58 PM4:50:29 PM
July 8, 2011 at 4:31 pm
I wrote a query that gives me all the cases from a single room and a single day, but I'm struggling how to identify the "previous" case end time.
This is NOT a complete solution, but I believe it will allow you to identify the "previous" case.
;WITH CTE AS
(SELECT Row_Number() OVER(PARTITION BY Room
ORDER BY TimeofCaseEnd DESC) AS Rn,
ID,[Date],Room, TimeOfCaseStart, TimeOfCaseEnd
FROM #R)
SELECT * FROM CTE
Result:
Rn ID Date Room Case start Case end
1299492011-07-07 00:00:00.000Lab 7 1900-01-01 16:25:58.0001900-01-01 16:50:29.000
2299442011-07-07 00:00:00.000Lab 7 1900-01-01 14:02:08.0001900-01-01 15:40:14.000
3299402011-07-07 00:00:00.000Lab 7 1900-01-01 08:52:48.0001900-01-01 11:25:00.000
This of course does not solve your problem as to the time differences, but hopefully it is a start in the correct direction for doing so.
have an MS Access solution that would work if my PK's were sequential
s
The CTE Rn (Row number) are in sequential order and can be substituted for the Access PK value.
July 8, 2011 at 4:56 pm
This might work too. You'd have to figure out how to do the calculation.
select ID, Today, Room, StartTime,
(select Max(Endtime) as PreviousStartTime from dbo.TurnaroundTimeData
where Starttime < T.StartTime and Room = T.Room) as PreviousEndTime
from dbo.TurnaroundTimeData T
order by StartTime
It returns:
IDToday RoomStartTimePreviousEndTime
299402011-07-07Lab 708:52:48.0000000NULL
299442011-07-07Lab 714:02:08.000000011:25:00.0000000
299492011-07-07Lab 716:25:58.000000015:40:14.0000000
July 10, 2011 at 9:50 pm
Thanks very much. This solution worked well!
July 19, 2011 at 2:58 pm
Chuck - I'm hoping you can help me with a similar problem. I want to calculate the downtime at the start at end of each day in each procedure room which means I need to identify the first and last cases of the day in each room.
July 20, 2011 at 1:21 pm
Sharon,
I'm not sure that I completely understand what you're asking for. Can you provide an example? Thanks.
July 21, 2011 at 2:05 pm
Here's some sample data from one day - sorry about the alignment, but if you paste into Excel it lines up. For Lab 10, the first case of the started at 9:21:20am and the last case of the day ended at 5:04:42. Operating hours start at 8:30am and end at 6:00pm so I want to calculate the non-productive time from 8:30 to 9:21 for the start of the day and 5:04 to 6:00pm for the end of the day. I'd like to calculate this for each room.
EventDateProcedureLocationSSCaseNumberTimeOfCaseStartTimeOfCaseEnd
7/18/2011Lab 1SSHK-IRL61310:25:02 AM10:40:17 AM
7/18/2011Lab 1SSHK-IRL61411:30:44 AM11:45:14 AM
7/18/2011Lab 1SSHK-IRL6153:05:02 PM3:59:14 PM
7/18/2011Lab 10SSNJ-IRL17699:21:20 AM11:08:50 AM
7/18/2011Lab 10SSNJ-IRL177012:12:58 PM12:32:40 PM
7/18/2011Lab 10SSNJ-IRL17711:25:19 PM1:32:29 PM
7/18/2011Lab 10SSNJ-IRL17722:22:31 PM2:57:41 PM
7/18/2011Lab 10SSNJ-IRL17734:32:03 PM5:04:42 PM
7/18/2011Lab 3SS301178:20:00 AM8:45:00 AM
7/18/2011Lab 3SS301199:35:00 AM9:45:00 AM
7/18/2011Lab 3SS3012010:45:00 AM11:00:00 AM
7/18/2011Lab 3SS3012311:30:00 AM11:50:00 AM
7/18/2011Lab 3SS301311:10:00 PM1:30:00 PM
7/18/2011Lab 3SS301342:00:00 PM2:15:00 PM
7/18/2011Lab 3SS301362:45:00 PM3:00:00 PM
7/18/2011Lab 3SS301383:35:00 PM3:55:00 PM
7/18/2011Lab 3SS301424:25:00 PM4:35:00 PM
7/18/2011Lab 3SS301435:00:00 PM5:20:00 PM
7/18/2011Lab 7SSNG-IRL14689:13:54 AM11:23:05 AM
7/18/2011Lab 7SSNG-IRL14691:06:21 PM8:02:52 PM
7/18/2011Lab 8SSNH-IRL17799:16:19 AM10:04:45 AM
7/18/2011Lab 8SSNH-IRL178011:10:16 AM12:19:17 PM
7/18/2011Lab 8SSNH-IRL17811:17:19 PM1:38:02 PM
7/18/2011Lab 8SSNH-IRL17822:26:22 PM3:05:36 PM
7/18/2011Lab 8SSNH-IRL17835:06:51 PM5:35:19 PM
7/18/2011Lab 9SSNI-IRL16669:13:19 AM10:45:38 AM
7/18/2011Lab 9SSNI-IRL166711:40:20 AM12:20:30 PM
7/18/2011Lab 9SSNI-IRL16681:06:40 PM1:35:25 PM
7/18/2011Lab 9SSNI-IRL16692:35:02 PM3:00:37 PM
7/18/2011Lab 9SSNI-IRL16704:08:49 PM5:05:48 PM
7/18/2011Lab 9SSNI-IRL16716:11:49 PM6:44:58 PM
7/18/2011Lab 9SSNI-IRL16727:25:17 PM8:05:12 PM
7/18/2011Lab 9SSNI-IRL16738:40:00 PM9:03:59 PM
July 22, 2011 at 10:50 am
This does that with a union. You could alternately join the two queries to put the results on one row. I'm using SQL Server 2008 for this. If you need to use this with an older version, you'd have to combine the date and time for the DATEDIFF calculation.
Declare @StartTime time(2) = '08:30', @EndTime time(2) = '18:00'
SELECT [EventDate]
,[ProcedureLocation]
,[SSCaseNumber]
,@StartTime AS StartOfDay
,[TimeOfCaseStart]
,DATEDIFF(mi,@StartTime, [TimeOfCaseStart]) As StartOfDayDowntime
,@EndTime AS EndOfDay
,[TimeOfCaseEnd]
,'' As EndOfDayDowntime
FROM [dbo].[TurnaroundTimeData2] D
WHERE [TimeOfCaseStart] = (SELECT MIN([TimeOfCaseStart]) FROM [dbo].[TurnaroundTimeData2]
WHERE [ProcedureLocation] = D.[ProcedureLocation]
AND [EventDate] = D.[EventDate])
UNION ALL
SELECT [EventDate]
,[ProcedureLocation]
,[SSCaseNumber]
,@StartTime AS StartOfDay
,[TimeOfCaseStart]
,'' As StartOfDayDowntime
,@EndTime AS EndOfDay
,[TimeOfCaseEnd]
,DATEDIFF(mi,[TimeOfCaseEnd], @EndTime) As EndOfDayDowntime
FROM [dbo].[TurnaroundTimeData2] D
WHERE [TimeOfCaseStart] = (SELECT MAX([TimeOfCaseStart]) FROM [dbo].[TurnaroundTimeData2]
WHERE [ProcedureLocation] = D.[ProcedureLocation]
AND [EventDate] = D.[EventDate])
ORDER BY [ProcedureLocation], [TimeOfCaseStart]
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply