Inserting Rows Through a View by Column

My requirement is this: I have a view that returns a recordset where for each change in value of the DeviceStatus column, I set the Flag column to 1.

This way I can then calculate the total duration of that status. The problem is when the duration “overflows” into the next hour, because I would like another record with the new hour.

The query is this.

SELECT        
    EventTime, DeviceStatus, DeviceName,
    CASE 
        WHEN DeviceStatus = LAG(DeviceStatus) OVER (ORDER BY EventTime)     
            THEN 0 
            ELSE 1 
    END AS Flag
FROM            
    [EventSourcing].[Fact].[Operations]
WHERE
    (DeviceName LIKE 'Device%') 
    AND DateID in (20241129,20241130)
ORDER BY
    1

A specific case is this one, where at 18:15:07 there is a state change that continues until 04:44:02 the following day

I wish it were like this

Do I necessarily have to enter records?

I wouldn't know how to do that. Thank you to anyone who can help me.

Use event time if it exists for a given hour, otherwise use generated exact hour. For Device Status we use the value from the most recent EventTime. Updated 2024-DEC-06 07:40 to use Generate_Series instead of recursive CTE. Updated 2024-DEC-09 to include DeviceName.

--Use event time if it exists for a given hour+devicename combination,otherwise use generated exact hour
with d as (
   --Generate dates at 1 hour intervals for 48 hours
  --Crossjoin with distinct DeviceName
   SELECT DATEADD(HOUR, Hours.value -1 , '2024-11-29') dte, DeviceName
   FROM GENERATE_SERIES(1,48) Hours
   CROSS JOIN 
   (SELECT distinct DeviceName 
   FROM Operations) do
  )
select isnull(o.EventTime,d.dte) as EventTime, 
  isnull(o.DeviceName, d.devicename) as devicename,
  isnull(DeviceStatus,
  (SELECT top 1 DeviceStatus FROM Operations WHERE eventtime < dte ORDER BY eventtime Desc)
  
  )  as DeviceStatus, 
  CASE WHEN o.EventTime IS NULL THEN  0
  ELSE
     CASE WHEN DeviceStatus = LAG(DeviceStatus) OVER (ORDER BY EventTime) THEN 0 ELSE 1 END 
  END AS Flag         
from d
LEFT JOIN  Operations o ON  o.devicename=d.devicename AND d.dte=DATEADD(HH,DATEPART(HH,o.EventTime),CAST(CAST(o.EventTime AS DATE) AS DATETIME))
WHERE    isnull(o.DeviceName, d.devicename) LIKE 'Device%'  
    AND CAST( isnull(o.EventTime,d.dte) AS DATE) in ('2024-11-29','2024-11-30')  
ORDER BY devicename desc,  isnull(o.EventTime,d.dte)

fiddle