0

Consider the following table:

CREATE TABLE dbo.Events (
    [ClientID] int,
    [EventID] int,
    [EventName] varchar(50),
    [StartDate] datetime,
    [Duration] bigint
)

INSERT INTO dbo.Events(ClientID, EventID, EventName, StartDate, Duration) 
VALUES (1, 1, 'Login', '2016-11-27 01:30:00.000', 86400000),
       (2, 1, 'Login', '2016-11-27 00:30:00.000', 86400000),
       (3, 1, 'Login', '2016-11-27 00:00:00.000', 86400000),
       (4, 1, 'Login', '2016-11-28 23:30:00.000', 172800000),
       (1, 2, 'Lock', '2016-11-27 23:30:00.000', 3600000),
       (4, 2, 'Lock', '2016-11-28 23:30:00.000', 1800000)

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

I need to see how many clients were Logged In, and how many clients were Locked, and for how long during a given date range. The date range can span a day/week/month/year, and when it does span more than a day, it should still bucket by half hour.

In the end, the results for a query on the date range 2016-11-28 00:00:00.000 - 2016-11-30 00:00:00.000 would look like so (apologies if I miscalculated the SumDuration or CountClients somewhere, I did this manually but I think it is correct):

EventID  EventName  HalfHour  SumDuration  CountClients
--------------------------------------------------
1        'Login'    0:00      5400000      3 
1        'Login'    0:30      3600000      2 
1        'Login'    1:00      3600000      2
1        'Login'    1:30      1800000      1 
...      ...        ...       ...          ...
1        'Login'    23:00     1800000      1 
1        'Login'    23:30     3600000      1
2        'Lock'     0:00      3600000      2 
2        'Lock'     0:30      1800000      1 
...      ...        ...       ...          ...
2        'Lock'     23:00     1800000      1 
2        'Lock'     23:30     3600000      2 

The ellipses are for the sake of brevity in repeated results, but there should be 48 results per Event (1 for every half hour in a day). You will also notice that the Duration needs to be calculated during that half hour. It is not in the example data, but it is possible to have a Duration end at any time, not just a half hour mark. Last, I should note the result for 'Login' at 23:30. ClientID 4 had a Login event for both 2016-11-28 and 2016-11-29 during the 23:30-0:00 half hour. So, the Duration sums both, but only counts the Client once.

I have the following, which gives me the CountClients and SumDuration, but doesn't break it up into half-hour increments:

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;

How would I go about breaking this up into half hour increments?

3

First, let's look at how to get all the time slices.

DECLARE @FirstDay smalldatetime = '20161128', 
        @LastDay  smalldatetime = '20161129';

SELECT TOP (24*2*DATEDIFF(DAY, @FirstDay, DATEADD(DAY,1,@LastDay)))
  DATEADD(MINUTE, 30*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1), @FirstDay)
FROM master..spt_values;

This will give you your set of 96 time slots, in half hour increments, from the 28th at midnight through the 29th at 23:30. A Numbers table is a better option than master.dbo.spt_values, but you can also use sys.all_objects - or any table really that has enough rows to cover your desired date range.

Next, let's see how to find the rows in the Events table that you care about - basically anything that passes through your window.

DECLARE @FirstDay smalldatetime = '20161128', 
        @LastDay  smalldatetime = '20161129';

SELECT EventID, EventName, StartDate, 
  EndDate = DATEADD(MILLISECOND, Duration, StartDate)
FROM dbo.Events
WHERE StartDate >= DATEADD(MILLISECOND, -Duration, @FirstDay) 
  AND StartDate <  DATEADD(DAY, 1, @LastDay);

Now, how to use that to get the results you're after (edited to accommodate durations that do not end on the half hour mark):

DECLARE @FirstDay smalldatetime = '20161128', 
        @LastDay  smalldatetime = '20161129';

;WITH TimeSlots(HalfHour) AS 
(
  SELECT TOP (24*2*DATEDIFF(DAY, @FirstDay, DATEADD(DAY,1,@LastDay)))
    DATEADD(MINUTE, 30*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1), @FirstDay)
  FROM master..spt_values
),
EventRange AS
(
  SELECT EventID, EventName, StartDate, 
    EndDate = DATEADD(MILLISECOND, Duration, StartDate)
  FROM dbo.Events
  WHERE StartDate >= DATEADD(MILLISECOND, -Duration, @FirstDay)
    AND StartDate <  DATEADD(DAY, 1, @LastDay)
),
Combo AS
(
  SELECT e.EventID, e.EventName, 
    HalfHourSlot = CONVERT(CHAR(5), CONVERT(datetime, CONVERT(time, t.HalfHour)), 108), 
    CASE
        WHEN e.StartDate > DATEADD(minute, 30, t.HalfHour) OR e.EndDate <= t.HalfHour
            THEN 0
        ELSE
            DATEDIFF(MINUTE, CASE WHEN e.StartDate >= t.HalfHour THEN e.StartDate ELSE t.HalfHour END, CASE WHEN e.EndDate <= DATEADD(minute, 30, t.HalfHour) THEN e.EndDate ELSE DATEADD(minute, 30, t.HalfHour) END)
    END AS Duration
  FROM TimeSlots AS t
  CROSS JOIN EventRange AS e
)
SELECT EventID, EventName, HalfHour = HalfHourSlot,
  SumDuration  = SUM(Duration), 
  CountClients = SUM(CASE WHEN Duration>0 THEN 1 ELSE 0 END) 
FROM Combo
GROUP BY EventID, EventName, HalfHourSlot
ORDER BY EventName DESC, HalfHour;
  • There is only 1 issue with this: SUM(Incl*1800000) assumes that it will always span the entire half hour slot. I made all of the Durations end at the half hour for simplicity in manual calculations, but this is not the case with the real data. "It is not in the example data, but it is possible to have a Duration end at any time, not just a half hour mark." – bbailes Jan 16 '17 at 22:31
  • This got me there, with some slight changes to accommodate for my comment above. – bbailes Jan 16 '17 at 22:59
  • @bbailes Well your sample data only included data that spawned entire half hour ranges, and you didn't specify otherwise, so it was a valid assumption that either (a) data was recorded only in half hour increments or (b) a half hour always counts as a full half hour regardless of how much it actually consumed (e.g. you stated it could end at any time, but not that it should be summed as a portion instead of a full half hour). – Aaron Bertrand Jan 16 '17 at 23:03
  • It was specified otherwise in the question: "It is not in the example data, but it is possible to have a Duration end at any time, not just a half hour mark." Either way, thank you so much for your help. I would not have figured this out without you. :) – bbailes Jan 16 '17 at 23:07
  • @bbailes It's good to include sample data that represent edge cases simply because we take the sample data and desired results and solve the problem elsewhere, not on this web page. We're here to help solve actual technical problems, not parse word problems. I'm glad my solution helped but it would be easier to solve those problems if all edge cases and assumptions are obvious up front without having to read a thesis. – Aaron Bertrand Jan 16 '17 at 23:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.