Pfthb

I have a table containing a time series with following information. Each record represents the event of "changing the mode".

 Timestamp | Mode 
------------------+------
 2018-01-01 12:00 | 1 
 2018-01-01 18:00 | 2 
 2018-01-02 01:00 | 1 
 2018-01-02 02:00 | 2 
 2018-01-04 04:00 | 1 

By using the LEAD function, I can create a query with the following result. Now each record contains the information, when and how long the "mode was active".

Please check the 2nd and the 4th record. They "belong" to multiple days.

 StartDT | EndDT | Mode | Duration
------------------+------------------+------+----------
 2018-01-01 12:00 | 2018-01-01 18:00 | 1 | 6:00
 2018-01-01 18:00 | 2018-01-02 01:00 | 2 | 7:00
 2018-01-02 01:00 | 2018-01-02 02:00 | 1 | 1:00
 2018-01-02 02:00 | 2018-01-04 04:00 | 2 | 50:00
 2018-01-04 04:00 | (NULL) | 1 | (NULL)

Now I would like to have a query that groups the data by day and mode and aggregates the duration.

This result table is needed:

 Date | Mode | Total
------------+------+-------
 2018-01-01 | 1 | 6:00
 2018-01-01 | 2 | 6:00
 2018-01-02 | 1 | 1:00
 2018-01-02 | 2 | 23:00
 2018-01-03 | 2 | 24:00
 2018-01-04 | 2 | 04:00

I didn't known how to handle the records that "belongs" to multiple days. Any ideas?

create table ChangeMode ( ModeStart datetime2(7), Mode int )

insert into ChangeMode ( ModeStart, Mode ) values
( '2018-11-15T21:00:00.0000000', 1 ),
( '2018-11-16T17:18:19.1231234', 2 ),
( '2018-11-16T18:00:00.5555555', 1 ),
( '2018-11-16T18:00:01.1234567', 2 ),
( '2018-11-16T19:02:22.8888888', 1 ),
( '2018-11-16T20:00:00.9876543', 2 ),
( '2018-11-17T09:00:00.0000000', 1 ),
( '2018-11-17T23:23:23.0230450', 2 ),
( '2018-11-19T17:00:00.0172839', 1 ),
( '2018-11-20T03:07:00.7033077', 2 )

;
with 
-- Determine the earliest and latest dates.
-- Cast to date to remove the time portion.
-- Cast results back to datetime because we're going to add hours later.
MinMaxDates 
as 
(select cast(min(cast(ModeStart as date))as datetime) as MinDate, 
 cast(max(cast(ModeStart as date))as datetime) as MaxDate from ChangeMode),

-- How many days have passed during that period
Dur
as
(select datediff(day,MinDate,MaxDate) as Duration from MinMaxDates),

-- Create a list of numbers.
-- These will be added to MinDate to get a list of dates.
NumList
as
( select 0 as Num
 union all
 select Num+1 from NumList,Dur where Num<Duration ),

-- Create a list of dates by adding those numbers to MinDate
DayList 
as
( select dateadd(day,Num,MinDate)as ModeDate from NumList, MinMaxDates ),

-- Create a list of day periods
PeriodList
as
( select ModeDate as StartTime,
 dateadd(day,1,ModeDate) as EndTime
 from DayList ),

-- Use LEAD to get periods for each record
-- Final record would return NULL for ModeEnd
-- We replace that with end of last day
ModePeriodList
as
( select ModeStart, 
 coalesce( lead(ModeStart)over(order by ModeStart),
 dateadd(day,1,MaxDate) ) as ModeEnd, 
 Mode from ChangeMode, MinMaxDates ),

ModeDayList
as
( select * from ModePeriodList, PeriodList 
where ModeStart<=EndTime and ModeEnd>=StartTime
),

-- Keep the later of the mode start time, and the day start time
-- Keep the earlier of the mode end time, and the day end time
ModeDayPeriod
as
( select case when ModeStart>=StartTime then ModeStart else StartTime end as StartTime,
 case when ModeEnd<=EndTime then ModeEnd else EndTime end as EndTime,
 Mode from ModeDayList ),

SumDurations
as
( select cast(StartTime as date) as ModeDate, 
 Mode, 
 DateDiff_Big(nanosecond,StartTime,EndTime)
 /3600000000000 
 as DurationHours from ModeDayPeriod ) 

-- List the results in order
-- Use MaxRecursion option in case there are more than 100 days 
select ModeDate as [Date], Mode, sum(DurationHours) as [Total Duration Hours]
 from SumDurations 
group by ModeDate, Mode
order by ModeDate, Mode
option (maxrecursion 0)

Result is:

Date Mode Total Duration Hours
---------- ----------- ---------------------------------------
2018-11-15 1 3.00000000000000
2018-11-16 1 18.26605271947221
2018-11-16 2 5.73394728052777
2018-11-17 1 14.38972862361111
2018-11-17 2 9.61027137638888
2018-11-18 2 24.00000000000000
2018-11-19 1 6.99999519891666
2018-11-19 2 17.00000480108333
2018-11-20 1 3.11686202991666
2018-11-20 2 20.88313797008333

you could use a CTE to create a table of days then join the time slots to it

DECLARE @MAX as datetime2 = (SELECT MAX(CAST(Timestamp as date)) MX FROM process);
WITH StartEnd AS (select p1.Timestamp StartDT, 
 P2.Timestamp EndDT ,
 p1.mode
 from process p1
 outer apply 
 (SELECT TOP 1 pOP.* FROM 
 process pOP 
 where pOP.Timestamp > p1.Timestamp 
 order by pOP.Timestamp asc) P2
 ),
 CAL AS (SELECT (SELECT MIN(cast(StartDT as date)) MN FROM StartEnd) DT
 UNION ALL
 SELECT DATEADD(day,1,DT) DT FROM CAL WHERE CAL.DT < @MAX
 ),
 TMS AS 
 (SELECT CASE WHEN S.StartDT > C.DT THEN S.StartDT ELSE C.DT END AS STP,
 CASE WHEN S.EndDT < DATEADD(day,1,C.DT) THEN S.ENDDT ELSE DATEADD(day,1,C.DT) END AS STE
 FROM StartEnd S JOIN CAL C ON NOT(S.EndDT <= C.DT OR S.StartDT>= DATEADD(day,1,C.dt))
 )
 SELECT *,datediff(MI ,TMS.STP, TMS.ste) as x from TMS

The following uses recursive CTE to build a list of dates (a calendar or number table works equally well). It then intersect the dates with date times so that missing dates are populated with matching data. The important bit is that for each row, if start datetime belongs to previous day then it is clamped to 00:00. Likewise for end datetime.

DECLARE @t TABLE (timestamp DATETIME, mode INT);
INSERT INTO @t VALUES
('2018-01-01 12:00', 1),
('2018-01-01 18:00', 2),
('2018-01-02 01:00', 1),
('2018-01-02 02:00', 2),
('2018-01-04 04:00', 1);

WITH cte1 AS (
 -- the min and max dates in your data
 SELECT
 CAST(MIN(timestamp) AS DATE) AS mindate,
 CAST(MAX(timestamp) AS DATE) AS maxdate
 FROM @t
), cte2 AS (
 -- build all dates between min and max dates using recursive cte
 SELECT mindate AS day_start, DATEADD(DAY, 1, mindate) AS day_end, maxdate
 FROM cte1
 UNION ALL
 SELECT DATEADD(DAY, 1, day_start), DATEADD(DAY, 2, day_start), maxdate
 FROM cte2
 WHERE day_start < maxdate
), cte3 AS (
 -- pull end datetime from next row into current
 SELECT
 timestamp AS dt_start,
 LEAD(timestamp) OVER (ORDER BY timestamp) AS dt_end,
 mode
 FROM @t
), cte4 AS (
 -- join datetime with date using date overlap query
 -- then clamp start datetime to 00:00 of the date
 -- and clamp end datetime to 00:00 of next date
 SELECT 
 IIF(dt_start < day_start, day_start, dt_start) AS dt_start_fix, 
 IIF(dt_end > day_end, day_end, dt_end) AS dt_end_fix,
 mode
 FROM cte2
 INNER JOIN cte3 ON day_end > dt_start AND dt_end > day_start
)
SELECT dt_start_fix, dt_end_fix, mode, datediff(minute, dt_start_fix, dt_end_fix) / 60.0 AS total
FROM cte4

DB Fiddle

Thanks everybody!

The answer from Cato put me on the right track. Here my final solution:

DECLARE @Start AS datetime;
DECLARE @End AS datetime;
DECLARE @Interval AS int;


SET @Start = '2018-01-01';
SET @End = '2018-01-05';
SET @Interval = 24 * 60 * 60;



WITH 

cteDurations AS 
 (SELECT [Timestamp] AS StartDT,
 LEAD ([Timestamp]) OVER (ORDER BY [Timestamp]) AS EndDT,
 Mode
 FROM tblLog
 WHERE [Timestamp] BETWEEN @Start AND @End
 ),

cteTimeslots AS
 (SELECT @Start AS StartDT,
 DATEADD(SECOND, @Interval, @Start) AS EndDT
 UNION ALL
 SELECT EndDT,
 DATEADD(SECOND, @Interval, EndDT)
 FROM cteTimeSlots WHERE StartDT < @End
 ),

cteDurationsPerTimesplot AS 
 (SELECT CASE WHEN S.StartDT > C.StartDT THEN S.StartDT ELSE C.StartDT END AS StartDT,
 CASE WHEN S.EndDT < C.EndDT THEN S.EndDT ELSE C.EndDT END AS EndDT,
 C.StartDT AS Slot,
 S.Mode
 FROM cteDurations S 
 JOIN cteTimeslots C ON NOT(S.EndDT <= C.StartDT OR S.StartDT >= C.EndDT)
 )


SELECT Slot,
 Mode,
 SUM(DATEDIFF(SECOND, StartDT, EndDT)) AS Duration

FROM cteDurationsPerTimesplot
GROUP BY Slot, Mode
ORDER BY Slot, Mode;

With the variable @Interval you are able to define the size of the timeslots.

The CTE cteDurations creates a subresult with the durations of all necessary entries by using the TSQL function LEAD (available in MSSQL >= 2012). This will be a lot faster than an OUTER APPLY.

The CTE cteTimeslots generates a list of timeslots with start time and end time.

The CTE cteDurationsPerTimesplot is a subresult with a JOIN between cteDurations and cteTimeslots. This this the magic JOIN statement from Cato!

And finally the SELECT statement will do the grouping and sum calculation per Slot and Mode.

Once again: Thanks a lot to everybody! Especially to Cato! You saved my weekend!

Regards
Oliver