locked
Find date range based on overlapping and missing date ranges RRS feed

  • Question

  • Hi everyone,

    I have a table which defines multiple rows per event. The structure looks like this EventID IntervalStartDate IntervalEndDate , Duration. I am looking to retrieve the total event duration having in mind that there might be missing or overlapping intervals for each eventid.

    Here is the table script:

    CREATE TABLE [dbo].[tstEventTable](
    	[EventID] [int] NULL,
    	[IntervalStartDate] [datetime] NULL,
    	[IntervalEndDate] [datetime] NULL,
    	[Duration] [float] NULL
    ) ON [PRIMARY]
    

    I am looking for good algorithm which can be applied to millions of rows in table on a daily basis.

    Thanks very much in advance


    • Edited by Kaly81 Saturday, December 15, 2012 9:56 AM
    Friday, December 14, 2012 7:43 AM

Answers

  • Programm  CTE logic to store non-overlapping intervals in temp table # intvlnorm kind of

    insert #intvlnorm 
    select EventID, IntervalStartDate, IntervalEndDate     from myTable a    
    where not exists (select 1 from Mytable b
         where .EventID=b.EventID
            and  a.IntervalStartDate between b.IntervalStartDate  and b.IntervalEndDate )
    while 1=1
      update  #intvlnorm 
         set  IntervalEndDate = b.IntervalEndDate  
       from #intvlnorm a
       join myTable b on a.EventID=b.EventID 
             and b.IntervalStartDate between a.IntervalStartDate
             and a.IntervalEndDate and b.IntervalEndDate > a.IntervalEndDate
    
      if @@rowcount = 0 then   
         break
    continue
    select EventID, sum( datediff(second,IntervalStartDate,IntervalEndDate)) totaltime
    from #intvlnorm 
    group by EventID
    Sorry, no 2000 to test it



    Serg

    • Marked as answer by Iric Wen Monday, December 24, 2012 8:52 AM
    Friday, December 14, 2012 9:48 AM

All replies

  • Interval 4 and ToTal Event time looking odd. Pls explain.

    Serg

    Friday, December 14, 2012 7:58 AM
  • Try, not tested

    ;with  intvlnorm as (
      select EventID, IntervalStartDate, IntervalEndDate 
        from myTable a
        where not exists (select 1 from Mytable b 
          where a.EventID=b.EventID and
              a.IntervalStartDate between b.IntervalStartDate  and b.IntervalEndDate )
      union all 
      select a.EventID, a.IntervalStartDate, m.IntervalEndDate 
        from intvlnorm a
        cross apply ( select top(1) IntervalEndDate 
                      from myTable b 
                      where a.EventID=b.EventID and
              b.IntervalStartDate between a.IntervalStartDate  and a.IntervalEndDate and b.IntervalEndDate > a.IntervalEndDate
                      order by b.IntervalEndDate desc) m
    )
    select EventID, sum( datediff(second,IntervalStartDate,IntervalEndDate)) totaltime
    from 
    (select EventID, IntervalStartDate, max(IntervalEndDate) IntervalEndDate 
    from intvlnorm
    group by EventID, IntervalStartDate ) grp
    group by EventID
     


    Serg


    • Edited by SergNL Friday, December 14, 2012 8:33 AM
    Friday, December 14, 2012 8:32 AM
  • Hi Serg

    thanks very much for the input. As the code is executed against SQL 2000 could you advice on alternative of the CTE and cross apply functions.

    Thanks

    Friday, December 14, 2012 8:43 AM
  • Programm  CTE logic to store non-overlapping intervals in temp table # intvlnorm kind of

    insert #intvlnorm 
    select EventID, IntervalStartDate, IntervalEndDate     from myTable a    
    where not exists (select 1 from Mytable b
         where .EventID=b.EventID
            and  a.IntervalStartDate between b.IntervalStartDate  and b.IntervalEndDate )
    while 1=1
      update  #intvlnorm 
         set  IntervalEndDate = b.IntervalEndDate  
       from #intvlnorm a
       join myTable b on a.EventID=b.EventID 
             and b.IntervalStartDate between a.IntervalStartDate
             and a.IntervalEndDate and b.IntervalEndDate > a.IntervalEndDate
    
      if @@rowcount = 0 then   
         break
    continue
    select EventID, sum( datediff(second,IntervalStartDate,IntervalEndDate)) totaltime
    from #intvlnorm 
    group by EventID
    Sorry, no 2000 to test it



    Serg

    • Marked as answer by Iric Wen Monday, December 24, 2012 8:52 AM
    Friday, December 14, 2012 9:48 AM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. Does your boss hand you pretty colored pictures when he want you to program something? Now we have to guess at everything, the schema, the specs, and we have to do all the typing! Please tell me why you did this; I have to teach SQL and I need to know the reasoning that students use when they do something this wrong. 

    >> I have a table which defines multiple rows per event. The structure looks like this (event_id, event_start_date, event_end_date). I am looking to retrieve the total event interval [sic:duration] having in mind that there might be missing or overlapping intervals for each event_id. <<

    INTERVAL is a temporal data type in ANSI/ISO Standard SQL. Did you mean duration? 

    CREATE TABLE Events
    (event_id INTEGER NOT NULL PRIMARY KEY,
     event_start_date DATE NOT NULL,
     event_end_date DATE NOT NULL,
     CHECK (event_start_date <= event_end_date));

    Why was this DDL so hard that you could not do it? Do you know what a Calendar table is? 

    SELECT COUNT(DISTINCT cal_date) AS event_duration 
      FROM Events AS E, Calendar AS C
     WHERE C.cal_date BETWEEN E.event_start_date AND E.event_end_date;


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, December 14, 2012 6:26 PM