Fun with DateTimes/Durations

Question

I have a table (Events) with the following columns:

ClientID, EventID, EventName, StartDate, Duration

Consider the following data:

INSERT INTO Events(ClientID, EventID, EventName, StartDate, Duration) 
VALUES (1, 1, 'Login', '2016-11-27 1:30:00', 86400000),
       (2, 1, 'Login', '2016-11-27 0:30:00', 86400000),
       (3, 1, 'Login', '2016-11-27 0:00:00', 86400000)

As you can see, the Duration is in milliseconds and can span multiple days.

In the end, I would like to return the following for 2016-11-28:

EventID  EventName  Sum(Duration) Count(ClientID)
--------------------------------------------------
1        'Login'     7200000          2 

The question being answered here is "How many clients were logged in on 2016-11-28 and for how many milliseconds?". 86400000 ms is 1 day. So, the first row would have 1.5 hours on 2016-11-28 (1:30 after midnight), the second would have .5 hours (0:30 after midnight), the third would have 0 hours (0:00 after midnight). Add those up, you get 2 hours or 7200000 ms and 2 clients since the 3rd did not have a duration on that day.

I have spent days toying with CASE statements to try to do this, and I really feel like there has to a better approach.

DECLARE @StartDate as DateTime, @EndDate as DateTime
SET @StartDate = '2016-11-28'
SET @EndDate = '2016-11-29'

SELECT 
    EventID, EventName, COUNT(ClientID) AS CountClients,
    SUM(CAST(DATEDIFF(ms, CASE 
                            WHEN StartDate < @StartDate 
                               THEN @StartDate 
                            ELSE StartDate 
                         END, 
                         CASE 
                            WHEN DATEADD(ms, Duration, StartDate) >= @EndDate 
                               THEN @EndDate 
                               ELSE DATEADD(ms, Duration, StartDate) 
                         END) AS bigint)) AS SumDuration
FROM Events
WHERE
    ((StartDate >= @StartDate AND StartDate < @EndDate)
    OR (DATEADD(ms, Duration, StartDate) >= @StartDate AND DATEADD(ms, Duration, StartDate) < @EndDate))
GROUP BY EventID, EventName

I am really struggling with how to do this efficiently, considering that I will need to get partial durations for dates that span more than a day. Any help would really be appreciated.

Answer 1

You could do it like this instead.

select E.EventID,
       E.EventName,
       count(*) as CountClients,
       sum(datediff(millisecond, I.StartDate, I.EndDate)) as SumDuration
from dbo.Events as E
  cross apply (
              select max(T.StartDate) as StartDate,
                     min(T.EndDate) as EndDate
              from (
                   values(@StartDate, @EndDate),
                         (E.StartDate, dateadd(millisecond, E.Duration, E.StartDate))
                   ) as T(StartDate, EndDate)
              ) as I
where E.StartDate < @EndDate and
      dateadd(millisecond, E.Duration, E.StartDate) > @StartDate
group by E.EventID,
         E.EventName;

Not sure it looks any simpler than what you have but your version is missing out on intervals that starts before @StartDate and ends after @EndDate. I changed the where clause to take care of that and at the same time it will be possible for you to use an index to somewhat limit the number of rows read from the table.

I also changed the way you calculate max StartDateand min EndDate for the sum of duration. Don't think it matter much for performance but to me the code looks neater.

With an index on StartDate having Duration, EventID, EventName as include columns you will get an index seek on E.StartDate < @EndDate and dateadd(millisecond, E.Duration, E.StartDate) > @StartDate as a residual predicate.

create nonclustered index IX on dbo.Events(StartDate)  
  include(Duration, EventName, EventID);

This will scan the index from the beginning as long as StartDate is less than @EndDate and it will make wonders for performance if you are looking at early dates and not so much for later dates.

An even better option could be an index on EventID, EventName with StartDate, Duration as include columns to remove the Sort operator from the plan.

create nonclustered index IX on dbo.Events(EventID, EventName)
  include(StartDate, Duration);

Every row in the table has to be looked at but it will be way better if you need to scan almost the entire table anyway.