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.