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

  • hi

    got a question here that i hope someone can help with.

    i have script that pulls a persons timesheet for a day (from 2 different tables) - start date/time and end date/time for each event in the day. it's an SQL 2000 server (yes i know!)

    what i need to do is be able to calculate (in minutes) is 2 things.

    the total number of gaps (in minutes) between each event

    the total number of overlaps (in mionutes) between each event.

    basically there should no no gaps or overlaps from the first start time to the last end time.

    i hope i explained this fully.

    here is my example,

    as you can see, there is a 10 minute gap between the end time on event 3 and the start time on event 4

    and there is a 10 minute overlap between the end time on event 10 and the start time on event 11

    persreftravel_start          travel_end
    GIPAQ2021-09-19 07:30:00.000 2021-09-19 08:45:00.000
    GIPAQ2021-09-19 08:45:00.000 2021-09-19 09:45:00.000
    GIPAQ2021-09-19 09:45:00.000 2021-09-19 09:55:00.000
    GIPAQ2021-09-19 10:05:00.000 2021-09-19 10:35:00.000
    GIPAQ2021-09-19 10:35:00.000 2021-09-19 11:35:00.000
    GIPAQ2021-09-19 11:35:00.000 2021-09-19 12:00:00.000
    GIPAQ2021-09-19 12:00:00.000 2021-09-19 13:00:00.000
    GIPAQ2021-09-19 13:00:00.000 2021-09-19 13:50:00.000
    GIPAQ2021-09-19 13:50:00.000 2021-09-19 14:20:00.000
    GIPAQ2021-09-19 14:20:00.000 2021-09-19 15:15:00.000
    GIPAQ2021-09-19 15:05:00.000 2021-09-19 16:00:00.000

    this is the query that i am using to pull this data

    declare @startdate datetime
    declare @persref varchar(10)

    set @startdate= '19/09/2021'
    set @persref = 'gipaq'

    select

    pers.pers_ref as 'persref',
    travel_start,
    travel_end

    from

    -- get personnel details --

    (select pers_ref, pers_department_code, case when left(pers_name,2) in ('A1','A2','A3','A4','A5','E1','E2','E3') then STUFF(pers_name, 1, 3, '') when left(pers_name,4) in ('SER ','COM ') then RIGHT(pers_name, LEN(pers_name) - 4) else pers_name end as 'pers_name'
    from personnel )
    where pers_ref = @persref) pers

    -- get event details --

    left join (
    select allocated_to 'engineer', pers_name as 'engineername', pers_department_code 'dept', dbo.dateonly(on_site) 'Date', call_ref, add1+' - '+post_code '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 )
    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_name, 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 )
    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) event on event.engineer=pers.pers_ref

    order by travel_start
  • You should provide consumable date like this:

    IF OBJECT_ID('tempdb..#temp','U') is not null
    drop table #temp

    select *
    into #temp
    from (values
    ('GIPAQ','2021-09-19 07:30:00.000', '2021-09-19 08:45:00.000'),
    ('GIPAQ','2021-09-19 08:45:00.000', '2021-09-19 09:45:00.000'),
    ('GIPAQ','2021-09-19 09:45:00.000', '2021-09-19 09:55:00.000'),
    ('GIPAQ','2021-09-19 10:05:00.000', '2021-09-19 10:35:00.000'),
    ('GIPAQ','2021-09-19 10:35:00.000', '2021-09-19 11:35:00.000'),
    ('GIPAQ','2021-09-19 11:35:00.000', '2021-09-19 12:00:00.000'),
    ('GIPAQ','2021-09-19 12:00:00.000', '2021-09-19 13:00:00.000'),
    ('GIPAQ','2021-09-19 13:00:00.000', '2021-09-19 13:50:00.000'),
    ('GIPAQ','2021-09-19 13:50:00.000', '2021-09-19 14:20:00.000'),
    ('GIPAQ','2021-09-19 14:20:00.000', '2021-09-19 15:15:00.000'),
    ('GIPAQ','2021-09-19 15:05:00.000', '2021-09-19 16:00:00.000')) T(persref,travel_start,travel_end)

    I can think of two ways of getting results.

    Using LAG:

    ;with cte as
    (
    select *,
    LAG(t1.travel_end) OVER (PARTITION BY t1.persref ORDER BY t1.travel_start) travel_end_lag
    from #temp t1
    )
    select datediff(mi,travel_end_lag,travel_start) gap,
    *
    from cte
    where travel_end_lag <> travel_start

    Using a self join:

    ;with cte as
    (
    select *, ROW_NUMBER() OVER (PARTITION BY t1.persref ORDER BY t1.travel_start) rn
    from #temp t1
    )
    select datediff(mi,t1.travel_end,t2.travel_start) gap,
    *
    from cte t1
    left join cte t2
    on t2.rn = t1.rn + 1
    where datediff(mi,t1.travel_end,t2.travel_start) <> 0

    A negative gap is an overlap and a positive gap is a gap.

  • I've always considered the following article to be a must read on this subject.

    https://www.itprotoday.com/sql-server/new-solution-packing-intervals-problem

    --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

  • i'm not sure either of those will work with SQL 2000?

    i know LAG isn't supported. and i don't believe PARTITION is supported in SQL 2000 either?

  • A negative gap is an overlap and a positive gap is a gap.

    also, i need those to be 2 distinct seperate values.

  • chenks wrote:

    i'm not sure either of those will work with SQL 2000? i know LAG isn't supported. and i don't believe PARTITION is supported in SQL 2000 either?

    Have you thought about upgrading your server?

    It is easy to separate into columns:

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

     

  • Jonathan AC Roberts wrote:

    Have you thought about upgrading your server?

    not an option i'm afraid.

    this is what i have to work with.

    i have done what you suggest though and used a consumable data

    the end result is that i have  2 columns, 1 showing the total gaps (in minues) and 1 the total overlaps (in minutes).

    there may be one of either, or multiples of each within a timesheet.

    declare @startdate datetime
    declare @persref varchar(10)

    set @startdate= '19/09/2021'
    set @persref = 'gipaq'

    declare @ViewTimesheet table
    (
    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
    select *
    from (

    select allocated_to, 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 )
    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 )
    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


    select
    persref, travel_start, travel_end
    from @ViewTimesheet
    order by travel_start

    • This reply was modified 3 weeks, 5 days ago by  chenks.
  • I can't see how your SQL will work with all those right brackets?

  • Jonathan AC Roberts wrote:

    I can't see how your SQL will work with all those right brackets?

    looks like the copy/paste into this forum has screwed something up, the script does work though

    declare @startdate datetime
    declare @persref varchar(10)

    set @startdate = '19/09/2021'
    set @persref = 'gipaq'

    declare @ViewTimesheet table
    (
    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
    select *
    from (

    select allocated_to, 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


    select
    t1.*

    from @ViewTimesheet as t1

    order by travel_start

    • This reply was modified 3 weeks, 5 days ago by  chenks.
  • chenks wrote:

    Jonathan AC Roberts wrote:

    Have you thought about upgrading your server?

    not an option i'm afraid. this is what i have to work with.

    i have done what you suggest though and used a consumable data

    the end result is that i have  2 columns, 1 showing the total gaps (in minues) and 1 the total overlaps (in minutes). there may be one of either, or multiples of each within a timesheet.

    Does that mean you have a solution?

  • no, i was expaining what the end result needs to be.

    you said i should provide consumable data, so that's all i have done

  • That is not really consumable data as I don't have all your tables that make up the query.

    You could add an identity column to the temporary table then insert values into the table ordered by allocated_to, travel_start

    Then use a method similar to the one I pasted in.

    I'm not sure if SQL 2000 has TOP?

    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 TOP(1000000000) *
    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 allocated_to ,travel_start;


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

     

  • that doesn't seem to give the total minutes of gaps and/or overlaps though?

    It just shows the 2 rows of data that may have an issue.

    sql2000 does support TOP, but in your script it produces an error. removing the TOP and leaving the * brings back the 2 rows mentioned above

  • chenks wrote:

    that doesn't seem to give the total minutes of gaps and/or overlaps though? It just shows the 2 rows of data that may have an issue.

    sql2000 does support TOP, but in your script it produces an error. removing the TOP and leaving the * brings back the 2 rows mentioned above

    Do you want it to just show the 2 rows or do you want it to show all rows?

    What does the first column show (labelled gap)?

    For the identity column to work the rows must be inserted into the table in the correct order. Maybe the ORDER BY works without the TOP. I'm not sure as I've never used SQL Server 2000.

     

  • i don't want it to show any rows.

    the result i want is this

    persref  Gaps   Overlaps
    gipaq 10 10

    you script doesn't run at all, but produces an error, i have to remove the TOP(10000) line to get it to produce anything, and remove "allocated_to" from some of the lines as well

    Msg 170, Level 15, State 1, Line 45
    Line 45: Incorrect syntax near '('.
    Msg 170, Level 15, State 1, Line 95
    Line 95: Incorrect syntax near 'timesheetdetails'.

    once i remove those it produces 2 rows, with a 10 and -10 in the "gap" column.

    this is the amended version of your script that allowed it to run without errors

    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 datediff(mi,t1.travel_end,t2.travel_start) gap,
    t1.*
    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

    • This reply was modified 3 weeks, 5 days ago by  chenks.

Viewing 15 posts - 1 through 15 (of 22 total)

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