How can I return an event duration divided over multiple days in SQL?

user_8527998 0
Viewed 61

I have a database that contains Event data. These Events represent a warning state in a system. They have a StartTime, EndTime, Duration (in seconds) and VarName (and some other attributes that are less relevant for the question).

I am trying to write a query that will allow me to represent the amount of time that a certain warning was active per day. This way, service engineers can easily see if certain changes/fixes caused a warning to decrease or disappear over time.

A quick-and-dirty first attempt is shown below.

SELECT
    [VarName] AS metric,
    SUM([Duration]) AS value,
    Convert(date, [StartTime]) AS date
FROM [dbo].[Events]
WHERE VarName LIKE 'WARN%'
GROUP BY Convert(date, [StartTime]), VarName
ORDER BY date

This works well enough when Events last only a short time and handles multiple Events of the same type in a day. But it breaks down when Events span multiple days (or even weeks).

Example:

VarName StartTime EndTime Duration
WARN_1 2021-06-28 23:00:00.000 2021-06-29 02:00:00.00 10800

What I get:

metric date value
WARN_1 2021-06-28 10800

What I want:

metric date value
WARN_1 2021-06-28 3600
WARN_1 2021-06-29 7200

Taking into account that:

  • An event can occur multiple times in the same day
  • An event can span multiple days

I'll be fiddling with this today and if I come up with a working solution I'll append it to this post. But I don't work with SQL all that often, and it feels like this may require some more advanced trickery. Any help is appreciated!

tsql sql-server sql
2 Answers

You can use a recursive CTE to break the periods into days:

with cte as (
      select varname, starttime,
             (case when datediff(day, starttime, endtime) = 0
                   then endtime
                   else dateadd(day, 1, convert(date, starttime))
              end) as day_endtime,
             endtime
      from t
      union all
      select varname, day_endtime,
             (case when datediff(day, starttime, endtime) = 1
                   then endtime
                   else dateadd(day, 1, convert(date, day_endtime))
              end) as day_endtime,
             endtime
      from cte
      where datediff(day, starttime, endtime) > 0
     )
select *
from cte;

To aggregate, change the last part to:

select varname, convert(date, starttime),
       sum(datediff(second, starttime, day_endtime))
from cte
group by varname, convert(date, starttime);

Here is a db<>fiddle.

If you need timespans longer than 100 days, add option (maxrecursion 0) or any number larger than about 732.

user_11440350

If you have a numbers table, you can join to that based on the difference in days which will give you n number of rows for each event based on the days. You then just need to calculate how much of each event falls in that day, which I've done with a case expression:

SELECT  t.VarName,
        StartDate = CONVERT(DATE, DATEADD(DAY, n.Number, t.StartTime)),
        Duration = CASE -- starts and ends on same date
                        WHEN CONVERT(DATE, t.StartTime) = CONVERT(DATE, t.EndTime) THEN t.Duration 
                        -- First Day
                        WHEN n.Number = 0 THEN DATEDIFF(SECOND, t.StartTime, CONVERT(DATE, DATEADD(DAY, 1, t.StartTime)))
                        --Last Day
                        WHEN CONVERT(DATE, DATEADD(DAY, n.Number, t.StartTime)) = CONVERT(DATE, t.EndTime) 
                            THEN DATEDIFF(SECOND, CONVERT(DATE, DATEADD(DAY, n.Number, t.StartTime)), t.EndTime)
                        -- Middle Day
                        ELSE 86400
                    END
FROM    @t AS t
        INNER JOIN Numbers AS n
            ON n.Number <= DATEDIFF(DAY, t.StartTime, t.EndTime);

If you don't have a numbers table, you can very easily create this on the fly:

DECLARE @T TABLE (VarName VARCHAR(10), StartTime DATETIME, EndTime DATETIME, Duration AS DATEDIFF(SECOND, StartTime, EndTime));
INSERT @T (VarName, StartTime, EndTime)
VALUES 
    ('WARN_1', '20210628 23:00:00.000', '20210629 02:00:00.00'),
    ('WARN_2', '20210629 11:00:00.000', '20210629 14:00:00.00'),
    ('WARN_3', '20210630 23:00:00.000', '20210704 02:00:00.00');

-- This will do numbers 0-99, add more cross joins if necessary
WITH Numbers (Number) AS
(   SELECT  ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
    FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n1 (N)
    CROSS JOIN (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n2 (N)
)
SELECT  t.VarName,
        StartDate = CONVERT(DATE, DATEADD(DAY, n.Number, t.StartTime)),
        Duration = CASE -- starts and ends on same date
                        WHEN CONVERT(DATE, t.StartTime) = CONVERT(DATE, t.EndTime) THEN t.Duration 
                        -- First Day
                        WHEN n.Number = 0 THEN DATEDIFF(SECOND, t.StartTime, CONVERT(DATE, DATEADD(DAY, 1, t.StartTime)))
                        --Last Day
                        WHEN CONVERT(DATE, DATEADD(DAY, n.Number, t.StartTime)) = CONVERT(DATE, t.EndTime) 
                            THEN DATEDIFF(SECOND, CONVERT(DATE, DATEADD(DAY, n.Number, t.StartTime)), t.EndTime)
                        -- Middle Day
                        ELSE 86400
                    END
FROM    @t AS t
        INNER JOIN Numbers AS n
            ON n.Number <= DATEDIFF(DAY, t.StartTime, t.EndTime);
user_10484250
Related