Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    12

    Unanswered: Not Your Standard Time Query

    Hello,

    I am looking for some help on this query.. I need to find how many minutes are in each 1/2 hour interval for each exception code.. Take a look at the raw data in the attached jpeg..

    What I need is by 1/2 hour interval to sum how many minutes of each of the exception codes.. I can use datediff to get the total sum but I need to know by interval..

    So for Granted Unpd it would be for example:
    Exception Interval Minutes
    Granted Unpd 17:00 30
    Granted Unpd 17:30 30
    Granted Unpd 18:00 30
    Attached Thumbnails Attached Thumbnails sample data.jpg  

  2. #2
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Use a table of time slots set to one more decimal second of precision than your data. You can now use temporal math to add it to a DATE to TIME(1) get a full DATETIME2(0). Here is the basic skeleton.

    CREATE TABLE Timeslots
    (slot_start_time TIME(1) NOT NULL PRIMARY KEY,
    slot_end_time TIME(1) NOT NULL,
    CHECK (start_time < end_time));

    INSERT INTO Timeslots --15 min intervals
    VALUES ('00:00:00.0', '00:14:59.9'),
    ('00:15:00.0', '00:29:59.9'),
    ('00:30:00.0', '00:44:59.9'),
    ('00:45:00.0', '01:00:59.9'),
    ..
    ('23:45:00.0', '23:59:59.9');

    Here is the basic query for rounding down to a time slot.

    SELECT CAST (@in_timestamp AS DATE), T.start_time
    FROM Timeslots AS T
    WHERE CAST (@in_timestamp AS TIME)
    BETWEEN T.slot_start_time
    AND T.slot_end_time;

  3. #3
    Join Date
    Jun 2009
    Posts
    12
    Thanks Celko..

    I get the timeslots table but am not so sure I understand how the query helps me find how many minutes are in each inteval..

    Example, if the agent was in Coaching from 9am to 12am I need to figure out how to get it so the query brings back that there were 30 minutes used from 9 to 930, 30 minutes from 930 to 10, 30 minutes from 10 to 1030, etc etc..

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Use a table of time slots set to one more decimal second of precision than your data.

    CREATE TABLE Timeslots
    (slot_nbr SMALLINT NOT NULL PRIMARY KEY
    slot_start_time TIME(1) NOT NULL,
    slot_end_time TIME(1) NOT NULL,
    CHECK (start_time < end_time));

    INSERT INTO Timeslots --30 min intervals
    VALUES (0, '00:00:00.0', '00:29:59.9'),
    (1, '00:30:00.0', '00:59:59.9'),
    (2, '01:00:00.0', '01:29:59.9'),
    (3, '01:30:00.0', '01:59:59.9'),
    ..
    (47, '23:30:00.0', '23:59:59.9');

    Since you want counts rounded to half hours, we can build another look up table for the math. The second table is keyed on all possible properly ordered pairs of time slots. But now we need a business rule about how to count a session that lasts less than one half hour. I will say zero, because you cannot get anything done.

    CREATE TABLE Session_Hours
    (slot_start_nbr SMALLINT NOT NULL,
    slot_end_nbr SMALLINT NOT NULL,
    CHECK (slot_start_nbr, slot_end_nbr),
    PRIMARY KEY (slot_start_nbr <= slot_end_nbr),
    session_hrs DECIMAL(4,2) NOT NULL);

    INSERT INTO Session_Hours - ~1000 rows
    VALUES
    (0, 0, 0.00),
    (0, 1, 0.50),
    ..
    (0, 47, 24.00),
    (1, 1, 0.00),
    etc;

    Now we take two clock times and fit them into time slots. The slot numbers tell us the hourly count in the second look up table. This will be handy for other scheduling problems, so it is not a redundant as it first looks. You can set any rule you want with this table! Make sessions after 18:00 Hrs count double! Bill them triple for 00:00 to 06:00 Hrs, etc.

    Here is a query done with a CTE to show the nesting of the code clearly.

    CREATE PROCEDURE Session_Billing_Duration
    (@in_start_time TIME(0), @in_end_time TIME(0))
    AS
    WITH Slots (slot_start_nbr, slot_end_nbr)
    AS
    (SELECT T1.timeslot_nbr, T@.timeslot_nbr
    FROM timeslots AS T1, Timeslots AS T2
    WHERE @in_start_time BETWEEN T1.start_time AND T1.end_time
    AND @in_end_time BETWEEN T2.start_time AND T2.end_time),

    SELECT H.session_hrs AS billing_hrs
    FROM Session_Hours AS H, Slots AS S
    WHERE S.slot_start_nbr = H.slot_start_nbr
    AND S.slot_end_nbr = H.slot_end_nbr;

    If you want to change the temporal granularity, it is easy to do with a spreadsheet or a quick query.

Posting Permissions

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