SQL Server Performance

Time areas with overlapping

Discussion in 'SQL Server 2005 General DBA Questions' started by francesco, Feb 1, 2010.

  1. francesco New Member

    Hello
    I'm really going mad with the following problem:
    I have a SQL table with two columns, both have the type DATETIME.
    The first column represents the start time and the second column the stop time.
    The start time of a lower row is always lower or equal than the start time of the following rows
    (The database is ordered by that statement).
    The stop time in one row is always greater than the start time in this row.
    The start time in another row can be the stop time of an earlier row.
    Start and stop times in several rows can be identical.
    Now I want to calculate the total time difference by adding a third column and eliminating the overlapping time intervalls.
    Here are 2 examples:
    Ex1: Given table:
    Start ... Stop
    -------------------
    0:20 ... 0:50
    0:30 ... 0:45
    0:35 ... 0:40
    0:35 ... 0:40
    0:36 ... 0:40
    The output has to be:

    Start ... Stopp ... Difference
    ----------------------------------------
    0:20 ... 0:50 30
    Ex2: Given table
    Start ... Stop
    -------------------
    0:20 ... 0:35
    0:30 ... 0:45
    0:35 ... 0:50
    1:00 ... 2:00
    2:30 ... 3:00
    3:00 ... 3:30
    The output has to be:

    Start ... Stopp ... Difference
    ----------------------------------------
    0:20 ... 0:50 30
    1:00 ... 2:00 60
    2:30 ... 3:30 60
    Is this problem solvable?
    Franz
  2. preethi Member

    There could be a better way of calculating this but this query delivers the results.
    create table #Process(rowid int identity, StartTime datetime, StopTime datetime)

    Insert into #process values('01/01/2010 0:20', '01/01/2010 0:50')
    Insert into #process values('01/01/2010 0:30', '01/01/2010 0:45')
    Insert into #process values('01/01/2010 0:35', '01/01/2010 0:40')
    Insert into #process values('01/01/2010 0:35', '01/01/2010 0:40')
    Insert into #process values('01/01/2010 0:36', '01/01/2010 0:40')
    Insert into #process values('01/02/2010 0:20', '01/02/2010 0:35')
    Insert into #process values('01/02/2010 0:30', '01/02/2010 0:45')
    Insert into #process values('01/02/2010 0:35', '01/02/2010 0:50')
    Insert into #process values('01/02/2010 1:00', '01/02/2010 2:00')
    Insert into #process values('01/02/2010 2:30', '01/02/2010 3:00')
    Insert into #process values('01/02/2010 3:00', '01/02/2010 3:30')

    SELECT MIN(StartTime), MAX(StopTime), DATEDIFF(mi,MIN(StartTime), MAX(StopTime)) FROM (
    select A.StartTime, A.StopTime, MIN(B.Starttime) MinTIme
    FROM #process A LEFT JOIN #process B
    ON A.StopTime < B.StartTime
    GROUP BY A.StartTime, A.StopTime) AS A
    GROUP BY MinTime
    order by MIN(StartTime)
    You need to explore all the possible solutions and try the best approach.
    Hope this helps.
  3. preethi Member

    By the way, welcome to the forums. Your question is a good choice for a puzzle competition [:D]
  4. francesco New Member

    @ preethi
    Thank you for your hint.
    But unfortunately I have one example where your query is not working:
    Start Stop
    0:00 0:40
    0:10 0:20
    0:25 0:30
    The output is:
    0:00 0:40 40
    0:10 0:20 10
    but it should be:
    0:00 0:40 40
    Franz
  5. preethi Member

    I believe this should work, unless there is another scenario we didn't cover:
    SELECT MaxTime AS StartTime, MAX(StopTime) as StopTime, DATEDIFF(mi, MaxTime, MAX(StopTime)) AS Duration
    FROM
    (
    SELECT A.StartTime, A.StopTime, MAX(B.StartTime) MaxTime
    FROM #process A INNER JOIN
    (
    SELECT A.StartTime, A.StopTime
    FROM #process A LEFT JOIN #process B
    ON A.StartTime > B.StartTime AND A.StartTime <=B.StopTime
    WHERE B.StartTime IS NULL
    ) B
    ON A.StartTime >=B.StartTime
    GROUP BY A.StartTime, A.StopTime
    ) A
    GROUP BY MaxTime
    Hope this helps.
  6. francesco New Member

    That is great, it looks like it is working - you are a real genius.
    Thank you very much
  7. preethi Member

    You are welcome. A genius would have done this on the first attempt. [;)]
  8. preethi Member

    By the way, it resolves your problem for now only. You may get intotrouble if that table is large or does not have proper indexes. Howlong it takes to execute? Do you see any issues with the execution plan?
  9. LAKSHMINARAYANA New Member

    Hi,

    I know this is post is more than 3 years old but I have a similar requirement and am not able to find a solution and hence this post. I need to find the number of rows in addition to the span as needed in the earlier post.

    The requirement for the above examples will be as below:

    Ex1: Given table:
    Start ... Stop
    -------------------
    0:20 ... 0:50
    0:30 ... 0:45
    0:35 ... 0:40
    0:35 ... 0:40
    0:36 ... 0:40
    The output has to be:

    Start ... Stopp ... Difference... No. of Entries
    ----------------------------------------
    0:20 ... 0:50... 30 ....5
    Ex2: Given table
    Start ... Stop
    -------------------
    0:20 ... 0:35
    0:30 ... 0:45
    0:35 ... 0:50
    1:00 ... 2:00
    2:30 ... 3:00
    3:00 ... 3:30
    The output has to be:

    Start ... Stopp ... Difference... No. of Entries
    ----------------------------------------
    0:20 ... 0:50... 30 ....3
    1:00 ... 2:00... 60 ....1
    2:30 ... 3:30... 60 ....2

    Any ideas?

    Thanks in advance,
    Lakshminarayana

Share This Page