Post reply

Time difference

  • July 20, 2017 at 6:43 pm

    #339039

    Hi,

    I want to calculate the difference time(minutes) between each I and O flag.

    Please refer below table

    EmpIdFlagPunchedTimeAttendanceDate
    AI9:16:00 AM7/13/2017
    AO9:48:00 AM7/13/2017
    AI11:06:00 AM7/13/2017
    AO11:38:00 AM7/13/2017
    AI12:38:00 PM7/13/2017
    AO1:00:00 PM7/13/2017
    AI2:07:00 PM7/13/2017
    AO4:42:00 PM7/13/2017
    AI5:27:00 PM7/13/2017
    AO5:42:00 PM7/13/2017
    AI6:25:00 PM7/13/2017
    AO6:26:00 PM7/13/2017
    AI6:31:00 PM7/13/2017
    AO6:32:00 PM7/13/2017
    BI9:16:00 AM7/12/2017
    BO9:48:00 AM7/12/2017
    BI11:06:00 AM7/12/2017
    BO11:38:00 AM7/12/2017
    BI12:38:00 PM7/12/2017
    BO1:00:00 PM7/12/2017
    BI2:07:00 PM7/12/2017
    BO4:42:00 PM7/12/2017
    BI5:27:00 PM7/12/2017
    BO5:42:00 PM7/12/2017
    BI6:25:00 PM7/12/2017
    BO6:26:00 PM7/12/2017
    BI6:31:00 PM7/12/2017
    BO6:32:00 PM7/12/2017

  • July 20, 2017 at 7:48 pm

    #1951992

    Thanks for the data sample... however, that's not super helpful. (Imagine copying and pasting that into SSMS and trying to run it.)  Here's how to do it:

    CREATE TABLE #TimeClock(
        EmpID CHAR,
        ClockType CHAR,
        StampTime TIME,
        StampDate DATE
    );
    GO

    INSERT INTO #TimeClock VALUES
    ('A',    'I',     '6:31:00 PM',    '7/13/2017'),
    ('A',    'O',     '6:32:00 PM',    '7/13/2017'),
    ('B',    'I',     '9:16:00 AM',    '7/12/2017'),
    ('B',     'O',     '9:48:00 AM',    '7/12/2017'),
    ('B',     'I',     '11:06:00 AM',    '7/12/2017'),
    ('B',     'O',     '11:38:00 AM',    '7/12/2017'),
    ('B',     'I',     '12:38:00 PM',    '7/12/2017'),
    ('B',     'O',     '1:00:00 PM',    '7/12/2017'),
    ('B',     'I',     '2:07:00 PM',    '7/12/2017'),
    ('B',     'O',     '4:42:00 PM',    '7/12/2017');

    Then folks here can just run the code and start from where you are.
    Since you're new, please read Jeff Moden's article explaining Best Practices... it's a great read on how to post questions so that they get answered.
    Here's a hack answer... (requires 2012 or later):
    SELECT tc.EmpID
        , tc.StampDate
        , tc.StampTime
        , DATEDIFF(n,tc.PrevEvent,tc.StampTime) AS elapsed
        , tc.EventDate
        , tc.ClockType
        , tc.PrevEvent
    FROM
    (SELECT EmpID
        , StampDate
        , StampTime
        , CAST(CAST(StampDate AS VARCHAR(10)) + ' ' + CAST(StampTime AS VARCHAR(10)) AS DATETIME) EventDate
        , ClockType
        , ROW_NUMBER() OVER (PARTITION BY EmpID, StampDate ORDER BY StampTime) AS ClockID
        , CASE WHEN ClockType = 'O' THEN LAG(StampTime) OVER (PARTITION BY EmpID, StampDate ORDER BY StampTime) END PrevEvent
    FROM #TimeClock) tc
    WHERE tc.ClockType = 'O';

  • July 21, 2017 at 5:35 am

    #1952041

    I'll also ask the question of, is there any guarantee that for every "I", there will be a related "O" and vice versa?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 21, 2017 at 7:29 am

    #1952081

    Yes it will have confirmed each I and O

  • July 21, 2017 at 9:12 am

    #1952123

    pietlinden - Thursday, July 20, 2017 7:48 PM

    Thanks for the data sample... however, that's not super helpful. (Imagine copying and pasting that into SSMS and trying to run it.)  Here's how to do it:

    CREATE TABLE #TimeClock(
        EmpID CHAR,
        ClockType CHAR,
        StampTime TIME,
        StampDate DATE
    );
    GO

    INSERT INTO #TimeClock VALUES
    ('A',    'I',     '6:31:00 PM',    '7/13/2017'),
    ('A',    'O',     '6:32:00 PM',    '7/13/2017'),
    ('B',    'I',     '9:16:00 AM',    '7/12/2017'),
    ('B',     'O',     '9:48:00 AM',    '7/12/2017'),
    ('B',     'I',     '11:06:00 AM',    '7/12/2017'),
    ('B',     'O',     '11:38:00 AM',    '7/12/2017'),
    ('B',     'I',     '12:38:00 PM',    '7/12/2017'),
    ('B',     'O',     '1:00:00 PM',    '7/12/2017'),
    ('B',     'I',     '2:07:00 PM',    '7/12/2017'),
    ('B',     'O',     '4:42:00 PM',    '7/12/2017');

    Then folks here can just run the code and start from where you are.
    Since you're new, please read Jeff Moden's article explaining Best Practices... it's a great read on how to post questions so that they get answered.
    Here's a hack answer... (requires 2012 or later):
    SELECT tc.EmpID
        , tc.StampDate
        , tc.StampTime
        , DATEDIFF(n,tc.PrevEvent,tc.StampTime) AS elapsed
        , tc.EventDate
        , tc.ClockType
        , tc.PrevEvent
    FROM
    (SELECT EmpID
        , StampDate
        , StampTime
        , CAST(CAST(StampDate AS VARCHAR(10)) + ' ' + CAST(StampTime AS VARCHAR(10)) AS DATETIME) EventDate
        , ClockType
        , ROW_NUMBER() OVER (PARTITION BY EmpID, StampDate ORDER BY StampTime) AS ClockID
        , CASE WHEN ClockType = 'O' THEN LAG(StampTime) OVER (PARTITION BY EmpID, StampDate ORDER BY StampTime) END PrevEvent
    FROM #TimeClock) tc
    WHERE tc.ClockType = 'O';

    I don't know why you call it a hack.  It's supported by MS and is more efficient than the previous standard method of getting data from a "previous" row.

    Also, converting from date/time data to (n)(var)char data and back is expensive.  I much prefer the following approach.
    SELECT StampDate, StampTime, DATEADD(DAY, DATEDIFF(DAY, 0, StampDate), CAST(StampTime AS DATETIME))

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

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

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