sql - need to find start and end time of events which is still not active

Question

I have a table with 3 coulmne timestamp eventid active

timestamp	eventid	active
2020-02-01 22:44:23	E1	true
2020-02-01 22:45:23	E1	false
2020-02-01 22:46:23	E1	true
2020-02-01 22:47:23	E1	false
2020-02-01 22:44:23	E2	true
2020-02-01 22:45:23	E2	false
2020-02-01 22:46:23	E2	true

question from:https://stackoverflow.com/questions/65545984/need-to-find-start-and-end-time-of-events-which-is-still-not-active

Answer 1

 ;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY eventid ORDER BY timestamp DESC) AS rn
   FROM yourTable
)
SELECT min(yt.timestamp) as Start_Time, max(yt.timestamp) as End_Time,yt.eventid
FROM yourTable yt
inner join cte
On cte.eventid=yt.eventid
WHERE 
cte.rn = 1 and cte.active="false"
group by yt.eventid

In this way you will not see E2, because of it is still active.