How to filter out multiple downtime events in SQL Server?

Question

There is a query I need to write that will filter out multiples of the same downtime event. These records get created at the exact same time with multiple different timestealrs which I don't need. Also, in the event of multiple timestealers for a downtime event I need to make the timestealer 'NULL' instead.

Example table:

Id	TimeStealer	Start	End	Is_Downtime	Downtime_Event
1	Machine 1	2022-01-01 01:00:00	2022-01-01 01:01:00	1	Malfunction
2	Machine 2	2022-01-01 01:00:00	2022-01-01 01:01:00	1	Malfunction
3	NULL	2022-01-01 00:01:00	2022-01-01 00:59:59	0	Operating

What I need the query to return:

Id	TimeStealer	Start	End	Is_Downtime	Downtime_Event
1	NULL	2022-01-01 01:00:00	2022-01-01 01:01:00	1	Malfunction
2	NULL	2022-01-01 00:01:00	2022-01-01 00:59:59	0	Operating

Answer 1

Seems like this is a top 1 row of each group, but with the added logic of making a column NULL when there are multiple rows. You can achieve that by also using a windowed COUNT, and then a CASE expression in the outer SELECT to only return the value of TimeStealer when there was 1 event:

WITH CTE AS(
    SELECT V.Id,
           V.TimeStealer,
           V.Start,
           V.[End],
           V.Is_Downtime,
           V.Downtime_Event,
           ROW_NUMBER() OVER (PARTITION BY V.Start, V.[End], V.Is_Downtime,V.Downtime_Event ORDER BY ID) AS RN,
           COUNT(V.ID) OVER (PARTITION BY V.Start, V.[End], V.Is_Downtime,V.Downtime_Event) AS Events
    FROM(VALUES('1','Machine 1',CONVERT(datetime2(0),'2022-01-01 01:00:00'),CONVERT(datetime2(0),'2022-01-01 01:01:00'),'1','Malfunction'),
               ('2','Machine 2',CONVERT(datetime2(0),'2022-01-01 01:00:00'),CONVERT(datetime2(0),'2022-01-01 01:01:00'),'1','Malfunction'),
               ('3','NULL',CONVERT(datetime2(0),'2022-01-01 00:01:00'),CONVERT(datetime2(0),'2022-01-01 00:59:59'),'0','Operating'))V(Id,TimeStealer,[Start],[End],Is_Downtime,Downtime_Event))
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS ID,
       CASE WHEN C.Events = 1 THEN C.TimeStealer END AS TimeStealer,
       C.Start,
       C.[End],
       C.Is_Downtime,
       C.Downtime_Event
FROM CTE C
WHERE C.RN = 1;