help with script to calculate gaps and overlaps from list of times

  • So it produces the correct result.

    To get it into 2 separate column just use the CASE statement I provided earlier.

    Then just remove the other columns from the query you do not want to show.

  • well no it doesn't produce the correct result.

    -10 is not the correct result.

  • Does it insert the data into the table variable in the correct order? You can tell if you query the table and look at the Id value.

  • yes it is inserting into the table correctly

    also, where there are mulitple gaps and overlaps, the result is this (when using you case statement)

    persref  gap  overlap
    GIPAQ 10 NULL
    GIPAQ 10 NULL
    GIPAQ NULL -5
    GIPAQ 5 NULL
    GIPAQ NULL -10

    • This reply was modified 9 months, 1 week ago by  chenks.
    • This reply was modified 9 months, 1 week ago by  chenks.
  • this gives a closer match as to what the end result should be, but still with the minus figure.

    i could just remove the minus symbol.

    DECLARE @startdate DATETIME;
    DECLARE @persref VARCHAR(10);
    SET @startdate = '19/09/2021';
    SET @persref = 'gipaq';
    DECLARE @ViewTimesheet TABLE
    (
    id int identity(1,1) not null,
    persref VARCHAR(8),
    dept VARCHAR(20),
    date DATETIME,
    call_ref INT,
    Address VARCHAR(90),
    link_to_contract_header VARCHAR(30),
    engineers_report TEXT,
    travel_start DATETIME,
    on_site DATETIME,
    off_site DATETIME,
    travel_end DATETIME,
    call_status_description VARCHAR(50),
    call_type_description VARCHAR(50),
    travel_miles INT,
    ce_id INT,
    eventtype VARCHAR(10)
    );

    INSERT INTO @ViewTimesheet
    (
    persref,
    dept,
    date,
    call_ref,
    Address,
    link_to_contract_header,
    engineers_report,
    travel_start,
    on_site,
    off_site,
    travel_end,
    call_status_description,
    call_type_description,
    travel_miles,
    ce_id,
    eventtype)
    SELECT *
    FROM
    (
    SELECT allocated_to persref,
    pers_department_code,
    dbo.dateonly(on_site) AS 'startdate',
    call_ref,
    add1 + ' - ' + post_code AS 'address',
    link_to_contract_header,
    CONVERT(VARCHAR(2000), engineers_report) AS 'engineers_report',
    travel_start,
    on_site,
    off_site,
    travel_end,
    call_status_description,
    call_type_description,
    travel_miles,
    ce_id,
    'CE' AS 'eventtype'
    FROM calls WITH(NOLOCK)
    INNER JOIN clients ON client_ref = link_to_client
    INNER JOIN lu_call_types ON call_type = call_type_code
    INNER JOIN call_events ON call_ref = link_to_call
    INNER JOIN lu_call_status ON event_code = call_status_code
    INNER JOIN personnel ON pers_ref = @persref
    WHERE dbo.dateonly(on_site) = @startdate
    AND allocated_to = @persref
    AND event_code IN('PR', 'F', 'RD', 'NA', 'PO')
    UNION ALL
    SELECT NP_Engineer,
    pers_department_code,
    dbo.dateonly(np_travel_start),
    NULL,
    NonProd_Description,
    'NonProd',
    CONVERT(VARCHAR(2000), NP_Notes),
    NP_Travel_start,
    np_on_site,
    np_off_site,
    np_travel_end,
    'NonProd',
    nonprod_description,
    0,
    NP_ID,
    'NP'
    FROM non_productive_events WITH(NOLOCK)
    INNER JOIN lu_nonprod_types ON np_code = nonprod_code
    INNER JOIN personnel ON pers_ref = NP_Engineer
    WHERE NP_Engineer = @persref
    AND dbo.dateonly(np_travel_start) = @startdate
    ) timesheetdetails
    order by travel_start;


    SELECT
    t1.persref,
    sum(CASE WHEN datediff(mi,t1.travel_end,t2.travel_start) >0 THEN datediff(mi,t1.travel_end,t2.travel_start) ELSE NULL END) gap,
    sum(CASE WHEN datediff(mi,t1.travel_end,t2.travel_start) <0 THEN datediff(mi,t1.travel_end,t2.travel_start) ELSE NULL END) overlap

    FROM @ViewTimesheet AS t1
    inner join @ViewTimesheet AS t2
    on t1.persref = t2.persref
    and t2.id = t1.id + 1
    where datediff(mi,t1.travel_end,t2.travel_start) <> 0

    group by t1.persref
  • Just change the order of comparison in the datediff for overlap to change the sign:

    sum(CASE WHEN datediff(mi,t1.travel_end,t2.travel_start) <0 
    THEN datediff(mi,t2.travel_start,t1.travel_end) ELSE NULL
    END) overlap
  • Ok.  There's a whole bunch of data in a whole bunch of tables that we have absolutely zero access to.  With that in mind, the only problem I'm going to solve is the problem of accurately counting the GapMinutes and the OverlapMinutes.  To do that easily, we're going to need a working table to make up for the fact that we have no ROW_NUMBER() or LAG() function in SQL Server 2000.  Then, you can join to that working table or query it or whatever you need to do.

    First, here's some readily consumable test data that should work in SQLServer 2000. I added another "persref" so that you understand you can do the whole shebang in one "swell-foop" when we get to the solution. 😀

    --=====================================================================================================================
    -- Create the sample time sheet table and data.
    -- This is NOT a part of the solution. We're just setting up a work/test environment here.
    --=====================================================================================================================
    --===== If the test table already exists, drop it
    IF OBJECT_ID('tempdb..#TimeSheet','U') IS NOT NULL DROP TABLE #TimeSheet;
    GO
    --===== Create and populate the #TimeSheet table on the fly.
    SELECT persref = CONVERT(CHAR(5),d.persref)
    ,travel_start = CONVERT(DATETIME,d.travel_start)
    ,travel_end = CONVERT(DATETIME,d.travel_end)
    INTO #TimeSheet
    FROM (
    SELECT 'GIPAQ','2021-09-19 07:30:00.000', '2021-09-19 08:45:00.000' UNION ALL
    SELECT 'GIPAQ','2021-09-19 08:45:00.000', '2021-09-19 09:45:00.000' UNION ALL
    SELECT 'GIPAQ','2021-09-19 09:45:00.000', '2021-09-19 09:55:00.000' UNION ALL
    SELECT 'GIPAQ','2021-09-19 10:05:00.000', '2021-09-19 10:35:00.000' UNION ALL
    SELECT 'GIPAQ','2021-09-19 10:35:00.000', '2021-09-19 11:35:00.000' UNION ALL
    SELECT 'GIPAQ','2021-09-19 11:35:00.000', '2021-09-19 12:00:00.000' UNION ALL
    SELECT 'GIPAQ','2021-09-19 12:00:00.000', '2021-09-19 13:00:00.000' UNION ALL
    SELECT 'GIPAQ','2021-09-19 13:00:00.000', '2021-09-19 13:50:00.000' UNION ALL
    SELECT 'GIPAQ','2021-09-19 13:50:00.000', '2021-09-19 14:20:00.000' UNION ALL
    SELECT 'GIPAQ','2021-09-19 14:20:00.000', '2021-09-19 15:15:00.000' UNION ALL
    SELECT 'GIPAQ','2021-09-19 15:05:00.000', '2021-09-19 16:00:00.000' UNION ALL

    SELECT 'JULUP','2021-09-19 07:30:00.000', '2021-09-19 08:45:00.000' UNION ALL
    SELECT 'JULUP','2021-09-19 08:45:00.000', '2021-09-19 09:35:00.000' UNION ALL --Added 10 minute gap to what existed
    SELECT 'JULUP','2021-09-19 09:45:00.000', '2021-09-19 09:55:00.000' UNION ALL
    SELECT 'JULUP','2021-09-19 10:05:00.000', '2021-09-19 10:35:00.000' UNION ALL
    SELECT 'JULUP','2021-09-19 10:35:00.000', '2021-09-19 11:35:00.000' UNION ALL
    SELECT 'JULUP','2021-09-19 11:35:00.000', '2021-09-19 12:00:00.000' UNION ALL
    SELECT 'JULUP','2021-09-19 12:00:00.000', '2021-09-19 13:00:00.000' UNION ALL
    SELECT 'JULUP','2021-09-19 13:00:00.000', '2021-09-19 14:10:00.000' UNION ALL --Added 20 minute overlap to what existed
    SELECT 'JULUP','2021-09-19 13:50:00.000', '2021-09-19 14:20:00.000' UNION ALL
    SELECT 'JULUP','2021-09-19 14:20:00.000', '2021-09-19 15:15:00.000' UNION ALL
    SELECT 'JULUP','2021-09-19 15:05:00.000', '2021-09-19 16:00:00.000'
    )d(persref,travel_start,travel_end)
    ;
    --===== Display the content of the #TimeSheet table to confirm things worked as expected.
    SELECT *
    FROM #TimeSheet
    ORDER BY persref, travel_start
    ;

    Heh... and now a very old, tried and true solution that will work in SQLServer 2000.  Again, join to the resulting working table or do whatever you want with it.  It contains the original data and the GapMinutes and OverlapMinutes where both are returned in separate columns as positive numbers.  If you want the OverlapMinutes to be negative, just remove the ABS() function.

    --=====================================================================================================================
    -- Solve the problem using an updated self-join
    --=====================================================================================================================
    --===== If the work table already exists, drop it.
    IF OBJECT_ID('tempdb..#WorkTable','U') IS NOT NULL DROP TABLE #WorkTable;
    GO
    --===== Create the work table with the same columns as the #TimeSheet table plus some special columns.
    CREATE TABLE #WorkTable
    (
    SortOrder INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    ,persref CHAR(5)
    ,travel_start DATETIME
    ,travel_end DATETIME
    ,GapMinutes INT DEFAULT 0
    ,OverLapMinutes INT DEFAULT 0
    )
    ;
    --===== Now, populate the working table from the timesheet table in the correct order.
    -- We need to do this to create a joinable "SortOrder" column and the required
    -- GapMinutes and OverlapMinutes columns.
    INSERT INTO #WorkTable WITH (TABLOCK)
    (persref, travel_start, travel_end)
    SELECT persref, travel_start, travel_end
    FROM #TimeSheet
    ORDER BY persref, travel_start
    OPTION (MAXDOP 1)
    ;
    --===== Calculate and Update the working table with all gaps and overlaps by row.
    UPDATE lo
    SET GapMinutes = CASE
    WHEN DATEDIFF(mi,lo.travel_end,hi.travel_start) > 0
    THEN DATEDIFF(mi,lo.travel_end,hi.travel_start)
    ELSE 0
    END
    ,OverLapMinutes = CASE
    WHEN DATEDIFF(mi,lo.travel_end,hi.travel_start) < 0
    THEN ABS(DATEDIFF(mi,lo.travel_end,hi.travel_start))
    ELSE 0
    END
    FROM #WorkTable lo
    FULL JOIN #WorkTable hi ON lo.SortOrder+1 = hi.SortOrder
    AND lo.persref = hi.persref
    ;
    --===== Display the content of the #WorkTable table to confirm things worked as expected.
    SELECT *
    FROM #WorkTable
    ORDER BY persref, travel_start
    ;

    That returns the following working table...

    An example of what I'm talking about is if you only want to see rows that have a Gap or Overlap, then the following code will do it for you.

    --===== Display the content of the #WorkTable table to confirm things worked as expected
    -- displaying only those rows that have a Gap or Overlap.
    SELECT *
    FROM #WorkTable
    WHERE GapMinutes <> 0 OR OverLapMinutes <> 0
    ORDER BY persref, travel_start
    ;

    ... and that returns the following...

    Like I said, once that working table is created, knock yourself out with however you want to join to it and use it.  We don't have your other data to test with.

    --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)
    Intro to Tally Tables and Functions

Viewing 7 posts - 16 through 22 (of 22 total)

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