Professional Web Applications Themes

Unit Hours of Activity vs. Unit Hours Scheduled - MySQL

Hi Everyone, I have a solution to creating a report of our company's unit hours of activity (time spent serving a call) based on an accompanying call log table, however, my queries take HOURS and HOURS to run (8+). As a result I can't provide the report as often as the powers that be would like it. Our table contains more than 1 million records and sensitive data so I can't provide test data. :/ Let me explain the situation. - Start and Stop times for each call are available USE stevensjn; DROP TABLE IF EXISTS `tbluhadata`; CREATE TABLE `tbluhadata` ...

  1. #1

    Default Unit Hours of Activity vs. Unit Hours Scheduled

    Hi Everyone,

    I have a solution to creating a report of our company's unit hours of
    activity (time spent serving a call) based on an accompanying call log
    table, however, my queries take HOURS and HOURS to run (8+). As a
    result I can't provide the report as often as the powers that be would
    like it. Our table contains more than 1 million records and sensitive
    data so I can't provide test data. :/

    Let me explain the situation.

    - Start and Stop times for each call are available
    USE stevensjn;

    DROP TABLE IF EXISTS `tbluhadata`;
    CREATE TABLE `tbluhadata` (
    `CallId` bigint(20) default NULL,
    `CallStart` datetime default NULL,
    `CallEnd` datetime default NULL,
    PRIMARY KEY (`CallId`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1;

    DROP TABLE IF EXISTS `_helper`;
    CREATE TABLE `_helper` (
    `i` tinyint(3) unsigned NOT NULL,
    PRIMARY KEY (`i`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1;

    DROP TABLE IF EXISTS `tbluhahours`;
    CREATE TABLE tbluhahours (
    StartDate datetime NOT NULL,
    EndDate datetime NOT NULL,
    PRIMARY KEY (`StartDate`,`EndDate`)
    ) ENGINE=MyISAM DEFAULT CHT=latin1;

    CREATE TABLE _helper (i TINYINT UNSIGNED NOT NULL PRIMARY KEY);
    INSERT INTO _helper (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7),
    (8), (9);

    SET counter := -1;
    SET start := '2000-01-01 00:00:00';
    SET finish := '2010-12-31 23:59:59';
    INSERT INTO tbluhahours (StartDate, EndDate)
    SELECT
    start + INTERVAL H.c HOUR,
    start + INTERVAL H.c + 1 HOUR - INTERVAL 1 SECOND

    FROM (
    SELECT counter := counter + 1 AS c
    FROM _helper h1 -- 10
    CROSS JOIN _helper h2 -- 100
    CROSS JOIN _helper h3 -- 1000
    CROSS JOIN _helper h4 -- 10000
    CROSS JOIN _helper h5 -- 100000
    ) H

    WHERE
    start + INTERVAL H.c HOUR < finish;
    ////////////////// end

    Here is a set of queries I have put together that uses joins to
    determine elapsed times for each hour. Perhaps optimizing these
    queries will be enough to get the script working at a workable speed?
    These are the 8 hour queries. Any suggestions at all would be
    fantastic.

    USE stevensjn;

    CREATE TABLE tblUHADurations(
    SELECT
    tblUHAData.CallId,
    tblUHAData.CallStart,
    tblUHAData.CallEnd,
    tblUHAHours.StartDate,
    DATE_FORMAT(tblUHAHours.StartDate, "%H") AS Hour,
    UNIX_TIMESTAMP(tblUHAHours.EndDate)+1 -
    UNIX_TIMESTAMP(tblUHAData.CallStart) AS Duration
    FROM
    tblUHAData INNER JOIN tblUHAHours ON
    tblUHAHours.StartDate <= tblUHAData.CallStart AND
    tblUHAData.CallStart <= tblUHAHours.EndDate AND
    tblUHAHours.EndDate < tblUHAData.CallEnd
    );

    INSERT INTO tblUHADurations (
    SELECT
    tblUHAData.CallId,
    tblUHAData.CallStart,
    tblUHAData.CallEnd,
    tblUHAHours.StartDate,
    DATE_FORMAT(tblUHAHours.StartDate, "%H") AS Hour,
    3600 AS Duration
    FROM
    tblUHAData INNER JOIN tblUHAHours ON
    tblUHAHours.StartDate > tblUHAData.CallStart AND
    tblUHAHours.EndDate < tblUHAData.CallEnd
    );

    INSERT INTO tblUHADurations (
    SELECT
    tblUHAData.CallId,
    tblUHAData.CallStart,
    tblUHAData.CallEnd,
    tblUHAHours.StartDate,
    DATE_FORMAT(tblUHAHours.StartDate, "%H") AS Hour,
    UNIX_TIMESTAMP(tblUHAData.CallEnd)+1 -
    UNIX_TIMESTAMP(tblUHAHours.StartDate) AS Duration
    FROM
    tblUHAData INNER JOIN tblUHAHours ON
    tblUHAHours.StartDate <= tblUHAData.CallEnd AND
    tblUHAData.CallEnd <= tblUHAHours.EndDate AND
    tblUHAHours.StartDate > tblUHAData.CallStart
    );


    INSERT INTO tblUHADurations (
    SELECT
    tblUHAData.CallId,
    tblUHAData.CallStart,
    tblUHAData.CallEnd,
    tblUHAHours.StartDate,
    DATE_FORMAT(tblUHAHours.StartDate, "%H") AS Hour,
    UNIX_TIMESTAMP(tblUHAData.CallEnd)+1 -
    UNIX_TIMESTAMP(tblUHAData.CallStart) AS Duration
    FROM
    tblUHAData INNER JOIN tblUHAHours ON
    tblUHAHours.StartDate <= tblUHAData.CallStart AND
    tblUHAData.CallEnd <= tblUHAHours.EndDate
    );

    Thanks for reading. With any luck someone has had to do something like
    this in the past, of will find the challenge interesting. :)

    - Jake

    stevensjn@gmail.com Guest

  2. #2

    Default Re: Unit Hours of Activity vs. Unit Hours Scheduled

    - a call can span several hours or occur inside of one hour
    - summing the difference between timestamps in a group by query gives
    a sum on time that BEGAN in a particular hour but doesn't figure in
    that after 3600 seconds, you are in a different hour!

    I typed this all out and accidentally pasted over much of the
    explanation I hard originally enterred. My tip for any other posted,
    author your post in Word where you can UNDO! :)

    Thanks to all of you for reading, and for any advice you can leave me.

    stevensjn@gmail.com Guest

  3. #3

    Default Re: Unit Hours of Activity vs. Unit Hours Scheduled

    com wrote: 

    I seem to remember posting about this question the other day and saying that
    group by was a better solution (I just checked and I certainly did, but you
    never responded to my poost).


    Paul Guest

  4. #4

    Default Re: Unit Hours of Activity vs. Unit Hours Scheduled

    On Feb 5, 3:35 pm, "Paul Lautman" <com> wrote: 


















    >
    > I seem to remember posting about this question the other day and saying that
    > group by was a better solution (I just checked and I certainly did, but you
    > never responded to my poost).[/ref]

    Using your advice I went ahead and wrote what I thought was a great
    solution using a group by query. Unfortunately, I ended up with a
    query that would sum elapsed seconds for calls that began in each hour
    of the day, but with disregard for calls that overlapped more than a
    single hour. So for instance with an elapsed time of > 3600 seconds
    all of those seconds would be grouped into the hour that which the
    call began in.

    Is there some way of doing the group by taking into account the
    possibility that calls may be longer than 3600 seconds? I'm not sure
    how.

    I had something like this:

    SELECT
    MAX(Year(CallStart)) AS theYear,
    MAX(Month(CallStart)) AS theMonth,
    MAX(Hour(CallStart) AS theHour,
    COUNT(*) AS NumCalls,
    SUM(UNIX_TIMESTAMP(CallEnd)-UNIX_TIMESTAMP(CallStart)) AS
    ElapsedSecond
    FROM tblUHAData
    WHERE
    Year(CallStart)=2006
    GROUP BY
    Year(CallStart), Month(CallStart), Hour(CallStart)
    ORDER BY
    1,2,3

    Which of course SUMs total elapsed second for calls that start in each
    of the hours of the 24 hour clock.

    Can we change the group by to somehow reflect the beginning and ends
    of each call? I would love to know how.

    Thanks.

    - Jake

    stevensjn@gmail.com Guest

  5. #5

    Default Re: Unit Hours of Activity vs. Unit Hours Scheduled

    On Feb 6, 1:09 am, com wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > Using your advice I went ahead and wrote what I thought was a great
    > solution using a group by query. Unfortunately, I ended up with a
    > query that would sum elapsed seconds for calls that began in each hour
    > of the day, but with disregard for calls that overlapped more than a
    > single hour. So for instance with an elapsed time of > 3600 seconds
    > all of those seconds would be grouped into the hour that which the
    > call began in.
    >
    > Is there some way of doing the group by taking into account the
    > possibility that calls may be longer than 3600 seconds? I'm not sure
    > how.
    >
    > I had something like this:
    >
    > SELECT
    > MAX(Year(CallStart)) AS theYear,
    > MAX(Month(CallStart)) AS theMonth,
    > MAX(Hour(CallStart) AS theHour,
    > COUNT(*) AS NumCalls,
    > SUM(UNIX_TIMESTAMP(CallEnd)-UNIX_TIMESTAMP(CallStart)) AS
    > ElapsedSecond
    > FROM tblUHAData
    > WHERE
    > Year(CallStart)=2006
    > GROUP BY
    > Year(CallStart), Month(CallStart), Hour(CallStart)
    > ORDER BY
    > 1,2,3
    >
    > Which of course SUMs total elapsed second for calls that start in each
    > of the hours of the 24 hour clock.
    >
    > Can we change the group by to somehow reflect the beginning and ends
    > of each call? I would love to know how.
    >
    > Thanks.
    >
    > - Jake- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    So a call that lasted 2.5 hours would appear in three of the hourly
    slots?

    Captain Guest

  6. #6

    Default Re: Unit Hours of Activity vs. Unit Hours Scheduled

    On Feb 6, 5:34 am, "Captain Paralytic" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]









    >
    > So a call that lasted 2.5 hours would appear in three of the hourly
    > slots?[/ref]

    No, the group by would only count it for the hour that in which it
    started, and then for the entire duration of the call. So a 2.5 hour
    call would SUM the entire duration of that call and add it to the
    grouping of the hour in which it started. This would add an extra 1.5
    hours of time to that hour, a bad thing. :)




    stevensjn@gmail.com Guest

  7. #7

    Default Re: Unit Hours of Activity vs. Unit Hours Scheduled

    On 6 Feb, 15:38, com wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    >
    > No, the group by would only count it for the hour that in which it
    > started, and then for the entire duration of the call. So a 2.5 hour
    > call would SUM the entire duration of that call and add it to the
    > grouping of the hour in which it started. This would add an extra 1.5
    > hours of time to that hour, a bad thing. :)- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Confused.
    Are you saying that for a 2.5 hour call you want (upto) 60 minutes
    counted in the hour slot in which the call began and the rest of the
    time you don't want counted at all? So if a 2.5 hour call started at
    14:55 you would only ever count 5 minutes of the call?

    Captain Guest

  8. #8

    Default Re: Unit Hours of Activity vs. Unit Hours Scheduled

     


    I need to count all time that occurs servicing calls, but for the hour
    that in which it occurred. If a Call starts at 11:30:00 and ends at
    13:30:00, I need to count the 30 minutes for the 11:00 hour, the 60
    minutes for the 12:00 hour and the 30 minutes for the 13:00 hour. This
    way I will be collecting ALL time used for each hour in the
    appropriate hours that the time servicing occurred in.

    Here is an excerpt from a query that shows the result set I would like
    to receive from the query:

    Month, Hour, Total Elapsed Seconds of Calls Serviced During Row's Hour

    January, 0, 123456
    January, 1, 123456
    .....
    January, 31, 123456
    February, 1, 123456
    ....
    February, 28, 123456

    Comparing this data versus a staff schedule allows you to determine
    your efficiency in scheduling vehicles in a logistics company, or in
    this case, an EMS service.

    Thanks a lot for spending time working through this with me.
    Ultimately it will have a great impact on our ability to have
    ambulances available at the right times of the day to save people's
    lives.

    Regards,

    Jake

    stevensjn@gmail.com Guest

  9. #9

    Default Re: Unit Hours of Activity vs. Unit Hours Scheduled

    On 6 Feb, 16:26, com wrote: 
    >
    > I need to count all time that occurs servicing calls, but for the hour
    > that in which it occurred. If a Call starts at 11:30:00 and ends at
    > 13:30:00, I need to count the 30 minutes for the 11:00 hour, the 60
    > minutes for the 12:00 hour and the 30 minutes for the 13:00 hour. This
    > way I will be collecting ALL time used for each hour in the
    > appropriate hours that the time servicing occurred in.
    >
    > Here is an excerpt from a query that shows the result set I would like
    > to receive from the query:
    >
    > Month, Hour, Total Elapsed Seconds of Calls Serviced During Row's Hour
    >
    > January, 0, 123456
    > January, 1, 123456
    > ....
    > January, 31, 123456
    > February, 1, 123456
    > ...
    > February, 28, 123456
    >
    > Comparing this data versus a staff schedule allows you to determine
    > your efficiency in scheduling vehicles in a logistics company, or in
    > this case, an EMS service.
    >
    > Thanks a lot for spending time working through this with me.
    > Ultimately it will have a great impact on our ability to have
    > ambulances available at the right times of the day to save people's
    > lives.
    >
    > Regards,
    >
    > Jake[/ref]

    A couple of posts ago, I said "So a call that lasted 2.5 hours would
    appear in three of the hourly slots?"
    Your reply in the following post was "No"

    Now you are saying "If a Call starts at 11:30:00 and ends at
    13:30:00, I need to count the 30 minutes for the 11:00 hour, the 60
    minutes for the 12:00 hour and the 30 minutes for the 13:00 hour.
    This
    way I will be collecting ALL time used for each hour in the
    appropriate hours that the time servicing occurred in."

    Which is what I said and you said no to!

    Captain Guest

  10. #10

    Default Re: Unit Hours of Activity vs. Unit Hours Scheduled

    I misread your post in thinking that you asking at that time about how
    the QUERY actually worked and responded no, because it would actually
    count all of the time for the hour that it started in.

    My apologies, I only responded to what I thought I was being asked. :/
     

    Yes. :)

    stevensjn@gmail.com Guest

Similar Threads

  1. Extended unit
    By Pailloncy Jean-Gerard in forum PostgreSQL / PGSQL
    Replies: 31
    Last Post: January 31st, 01:18 PM
  2. changing unit
    By Reza Saheban webforumsuser@macromedia.com in forum Macromedia Freehand
    Replies: 1
    Last Post: October 8th, 12:31 PM
  3. Test::Unit in 1.8
    By Joel VanderWerf in forum Ruby
    Replies: 1
    Last Post: August 10th, 12:23 AM
  4. I think I've ed my unit up again!!! Help!
    By Rob in forum Windows XP/2000/ME
    Replies: 0
    Last Post: July 23rd, 09:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139