Developing Unit Hour Activity Report - Suggestions?
I'm developing a set of queries to try to represent the amount of unit activity hours used to show contrast against the number of unit activity hours I have scheduled. This is for the Peel Region Paramedic Service, in Ontario Canada.
If I have 32 ambulances scheduled to work at all times throughout the entire 24 hour day than I have 32 hours of scheduled unit activity for each hour of the day.
If an ambulance is on a call (not parked) from 1pm until 2:30pm than for the hour of 1pm that is a complete hour of unit activity logged (of the 32 available), and for 2pm I have .5 hours of activity logged. If another was on a call from 12:30pm until 1pm there was .5 hours of unit activity between 12pm and 1 pm.
I would like to come up with queries that can show me hours of activity for each hour of the day given a date range. So if my date range is 2 days, then my query should return the total hours of unit activity for each hour of the two days.
I've been mulling over how to do this for the past few hours and I'm not coming up with any great ideas. Could anyone offer any suggestions? This seems really complicated to me, but I'm sure there is a straightforward way of doing it. I've considered totalling one hour's activity and inserting the result into a table and later totalling it.
Any advice? It's for a good cause if that helps. Below is a create statement that shows a table that logs calls.
CREATE TABLE tblRecords (
RecordId INTEGER CONSTRAINT RecordIdConstraint, PRIMARY KEY,
StartDate DateTime NOT NULL,
EndDate DateTime NOT NULL,
Thanks for reading.
* The main problem is that a call could start on one day and end on the next making it difficult to determine which records span a particular hour of the day.
Last edited by KalebZen; 05-25-2006 at 05:00 PM.
If I understand your issue correctly, couldn't you just use the DateDiff function (for your StartDate and EndDate) to determine how many unit hours of activity there is per call?
Microsoft MVP (Visual Basic)
Thats very close to what I need, except I need to count the amount time used during each one hour period that the call occurred in.
So for a call from 23:00 to 03:45 (arg, the next day!) I should end with the following (values in seconds, pretend there is a column for each hour of the day):
The idea is to have a result for each call record that matches my WHERE criteria so I can use the results to make graphs in Excel etc. Graphs would be used to compare our Unit Hour Activity versus Scheduled Unit Hours to ensure our scheduling is efficient. This sort of report is very useful in graph format when talking numbers with people not intimately familiar with our service.
00:00 01:00 02:00 03:00 ... 23:00
3600 3600 3600 2700 ... 3600
I've been toying with the idea of adding generated columns (0:00-23:00) and adding the correct values to the columns, per row, but I'm not sure how to test if the call occured during each generated column's one hour period. If I can figure that out I think this should work.
Last edited by KalebZen; 05-26-2006 at 09:43 AM.
So, I've managed to write enough code that I can complete the report unless a call spanned two days. Please help!!
I've attached Create and test data statements in a text file.
FormatNumber(CallId, 0, 0, 0, 0) AS nCallId,
IIf(DatePart("d", T3_Enroute) <> DatePart("d", T7_ClearDestination), 1, 0) AS MoreThanOneDay,
IIf(DatePart("d", T3_Enroute) <> DatePart("d", T7_ClearDestination), 0, IIf(DatePart("h", T3_Enroute)<=0, DateDiff("s", Format(T3_Enroute, "yyyy-mm-dd " & "00:00:00"), T7_ClearDestination),0)) AS [0:00],
IIf(DatePart("d", T3_Enroute) <> DatePart("d", T7_ClearDestination), 0, IIf(DatePart("h", T3_Enroute)<=0, IIf(DateDiff("s", Format(T3_Enroute, "yyyy-mm-dd " & "00:00:00"), T7_ClearDestination) > 3600, 3600, DateDiff("s", Format(T3_Enroute, "yyyy-mm-dd " & "00:00:00"), T7_ClearDestination)),0)) AS [0:00 Truncated]
T3_Enroute IS NOT NULL AND T7_ClearDestination IS NOT NULL AND
ORDER BY 5 ASC
Last Post: 02-20-2008, 04:30 AM
By Mike in forum VB Classic
Last Post: 01-27-2007, 06:28 AM
By RipIT in forum VB Classic
Last Post: 04-17-2006, 08:26 AM
By jrogers in forum Database
Last Post: 08-12-2005, 11:16 AM
By Liz Romero in forum ASP.NET
Last Post: 06-08-2002, 11:30 AM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL