Developing Unit Hour Activity Report - Suggestions?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Developing Unit Hour Activity Report - Suggestions?

Hybrid View

  1. #1
    Join Date
    May 2006
    Posts
    9

    Developing Unit Hour Activity Report - Suggestions?

    Hi All,

    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.

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    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?
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    May 2006
    Posts
    9
    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):

    Code:
    00:00      01:00      02:00     03:00   ...   23:00
    ---------------------------------------------
    3600        3600        3600      2700   ...    3600
    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.

    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.

  4. #4
    Join Date
    May 2006
    Posts
    9
    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.

    Code:
    SELECT
            FormatNumber(CallId, 0, 0, 0, 0) AS nCallId,
            T3_Enroute,
            T7_ClearDestination,
            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]
    
    FROM CallDispatched3
    
    WHERE
            T3_Enroute IS NOT NULL AND T7_ClearDestination IS NOT NULL AND
            DatePart("yyyy", T3_Enroute)=2005
    
    ORDER BY 5 ASC
    Attached Files Attached Files

Similar Threads

  1. Replies: 1
    Last Post: 02-20-2008, 04:30 AM
  2. Replies: 2
    Last Post: 01-27-2007, 06:28 AM
  3. data report
    By RipIT in forum VB Classic
    Replies: 1
    Last Post: 04-17-2006, 08:26 AM
  4. Replies: 0
    Last Post: 08-12-2005, 11:16 AM
  5. Replies: 2
    Last Post: 06-08-2002, 11:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center