Time worked on a ticket Query

  • I am trying to get a report on time spent working on a ticket. Basically I have multiple employees working on a single ticket and want to report on how much time they worked on a ticket.

    For example:

    Employee A has worked on a ticket from 12 PM till 5 PM and during this ticket was put on "pending" for an hour i.e. from 3 PM-4 PM..which need to be excluded from total time spent by Employee A on the ticket.

    Calculation:

    Time difference in hours: 5

    Ticket in Pending state hours: 1

    Final time in hours: 4

    Need your help on how can this be done SQL.

    Attached file contains sample data for reference.

    Thanks in advance

    Attachments:
    You must be logged in to view attached files.
  • Please will you show us what you've tried?  You can get time spent by subtracting Mi_Start from Mi_End where Mi_Definition is Assigned to Duration.  You can get the Pending time by doing something similar for Incident State Duration Pending.  You'll need to join one result set to the other in order to get the assignee for the Pending status.

    By the way, a lot of people are reluctant to open spreadsheets from people they don't know.  You'll get better answers if you provide, in text in your post, table DDL in the form of CREATE TABLE statements and sample data in the form of INSERT statements.

    John

  • Hi nsm2909,

    Give this a try, it should get you started.

    Note my figures are slightly different to your example as I truncated the timestamps in my table

    ;WITH CTE AS
    (
    SELECT d.IncidentNumber
    , d.Mi_Definition
    , d.Mi_Value
    , d.Mi_Start
    , d.Mi_End
    , DATEDIFF(MINUTE, d.Mi_Start, d.Mi_End) AS DurationMinutes
    FROM dbo.Data d
    WHERE
    (
    d.Mi_Definition = 'Assigned to Duration'
    OR
    d.Mi_Value = 'Pending'
    )
    ), Total AS
    (

    SELECT
    C.IncidentNumber
    , C.Mi_ValueAS [Assigned / Pending]
    , SUM(C.DurationMinutes)AS [Total (Min)]
    FROM CTE C
    GROUP BY C.IncidentNumber
    , C.Mi_Value
    )

    SELECT T.IncidentNumber
    , T.[Assigned / Pending]
    , T.[Total (Min)]
    , CASE
    WHEN T.[Total (Min)] < 0 AND T.[Total (Min)] > -60
    THEN '-'
    ELSE TRY_CAST(T.[Total (Min)]/60 AS VARCHAR) + ':' + right('0' + CAST(ABS(T.[Total (Min)]) % 60 AS VARCHAR(2)),2)
    END
    [Total (HH:mm:ss)]
    FROM Total T
    ORDER BY
    T.IncidentNumber
    , T.[Assigned / Pending]

    • This reply was modified 4 years, 1 month ago by  m-scally.
    • This reply was modified 4 years, 1 month ago by  m-scally.
    Attachments:
    You must be logged in to view attached files.
  • This is a variation on packing intervals.  Itzik Ben-Gan has written a number of articles that might be helpful.  If you post consumable data I can help you with it.  I am not going to open an Excel spreadsheet.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Hi John,

    I had tried sharing the sample data but was unable to put it in table format; but your point is valid nobody would want to open an excel.:| Also I'm new to SQL...wanted to get idea from the forum on how to get started on the calculation.

    m-Scally,

    Thanks so much for the query. It does what I was looking for..I just have to figure out a way to exclude the "pending" time from the Employee's time.

    drew.allen,

    Thank you. Here's the sample data:

    DROP TABLE IF EXISTS #INC_Data;
    CREATE TABLE #INC_Data (
    IncidentNumber NVARCHAR (20) NOT NULL,
    Mi_Definition VARCHAR (255) NOT NULL,
    Mi_Value VARCHAR (255),
    Mi_Start Datetime,
    Mi_End Datetime
    )
    INSERT INTO #INC_Data (IncidentNumber, Mi_Definition,Mi_Value,Mi_Start,Mi_End)
    VALUES
    ('INC8574030','Create to Resolve Duration',NULL, '10/30/2019 7:26:20 PM','10/30/2019 7:56:40 PM'),
    ('INC8574030','Incident State Duration', 'Active', '10/30/2019 7:26:22 PM','10/30/2019 7:28:21 PM'),
    ('INC8574030','Assignment Group','Unix-Lev-L1-team2', '10/30/2019 7:26:22 PM','11/8/2019 8:00:28 PM '),
    ('INC8574030','Incident State Duration','In Progress', '10/30/2019 7:28:21 PM','10/30/2019 7:56:30 PM'),
    ('INC8574030','Assigned to Duration','Ayush Chauhan', '10/30/2019 7:28:21 PM','11/8/2019 8:00:28 PM '),
    ('INC8574030','Incident State Duration','Pending', '10/30/2019 7:56:28 PM','10/30/2019 7:56:40 PM'),
    ('INC8574030','Incident State Duration','Resolved', '10/30/2019 7:56:40 PM','11/8/2019 8:00:28 PM '),
    ('INC8574030','Incident State Duration','Closed', '11/8/2019 8:00:28 PM' ,NULL ),
    ('INC8909432','Create to Resolve Duration',NULL, '12/23/2019 7:31:14 PM','12/24/2019 12:59:29 PM'),
    ('INC8909432','Incident State Duration','Active', '12/23/2019 7:31:18 PM','12/23/2019 7:42:59 PM'),
    ('INC8909432','Assignment Group','TDS-IS Data Compliance', '12/23/2019 7:31:18 PM','1/2/2020 2:00:18 PM'),
    ('INC8909432','Incident State Duration','In Progress', '12/23/2019 7:42:59 PM','12/23/2019 7:57:33 PM'),
    ('INC8909432','Assigned to Duration','Deepika N', '12/23/2019 7:42:59 PM','12/23/2019 7:57:33 PM'),
    ('INC8909432','Incident State Duration','Pending', '12/23/2019 7:57:33 PM','12/24/2019 12:59:29 PM'),
    ('INC8909432','Assigned to Duration','Gowtham Komisettu', '12/23/2019 7:57:33 PM','12/24/2019 12:18:44 PM'),
    ('INC8909432','Assigned to Duration','Abhinav P', '12/24/2019 12:18:44 PM','1/2/2020 2:00:18 PM'),
    ('INC8909432','Incident State Duration','Resolved', '12/24/2019 12:59:29 PM','1/2/2020 2:00:18 PM'),
    ('INC8909432','Incident State Duration','Closed', '1/2/2020 2:00:18 PM', NULL)

    --SELECT * FROM #INC_Data
    --Order by IncidentNumber, Mi_Start

    • This reply was modified 4 years, 1 month ago by  nsm2909.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply