Re: Counting Time overlap periods

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 08/26/04 

Date: Thu, 26 Aug 2004 10:24:25 -0400

Would this work for you:

SELECT EventId, DATEDIFF(mi, StartTime, EndTime) AS Duration
FROM
    (SELECT EventId, MIN(StartTime) AS StartTime, MAX(EndTime) AS EndTime
    FROM YourTable
    GROUP BY EventId) AS x (EventId, StartTime, EndTime)

"PRADEEP T.N." <PRADEEP T.N.@discussions.microsoft.com> wrote in message
news:6B0D64DC-F1FA-43BC-8046-EF9CA8B77C70@microsoft.com...
> I have a table, which stores the events occured, like this (starttime &
> endtime are datetime columns)
> StartTime EndTime
> 08:30 08:31
> 08:32 08:35
> 08:02 08:05
> 08:31 08:32
>
> I want to take the duration of the event for a given hour. I cannot take
the
> difference of Max() - Min() because it will include the OVERLAP duration
> also. I want the duration of event without time overlap.
>
> Anybody knows how to achieve this?
>
> Thanks.
>

Relevant Pages

  • Re: Need help from Gurus: Profiler columns
    ... DATEDIFF counts boundaries, not actual interval. ... endtime is inside the same second, DATEDIFF returns 0, but if the starttime ... and endtime are in different seconds, then you will get a non-zero answer. ... Duration is in microseconds, by the way, but you can look for equivalancies ...
    (microsoft.public.sqlserver.tools)
  • Re: Need help from Gurus: Profiler columns
    ... ORDER BY Duration DESC ... So, if the starttime ... and endtime is inside the same second, DATEDIFF returns 0, but if the ... equivalancies by the following query. ...
    (microsoft.public.sqlserver.tools)
  • Re: Profiler: Duration vs Difference between StartTime and EndTime
    ... I am not certain how duration is calculated, but I regularly see numbers that are wildly inaccurate. ... "Expert SQL Server 2005 Development" ... The duration for some of the statements was just under 1 minute, however for the same statements the difference between the starttime and endtime were closer to 17 ms. ...
    (microsoft.public.sqlserver.tools)
  • Re: Need help from Gurus: Profiler columns
    ... if you want to see the variance between DATEDIFF and duration you ... DATEDIFF counts boundaries, not actual interval. ... endtime is inside the same second, DATEDIFF returns 0, but if the ... starttime and endtime are in different seconds, ...
    (microsoft.public.sqlserver.tools)
  • RE: calculating difference between date/time fields
    ... Function BusinessHours(StartTime As Date, Endtime As Date) ... Dim DiffTime As Double ... If Endtime < StartTime Then ...
    (microsoft.public.excel.programming)