Grouping in the sequence as it appears with timestamp

Question

The table has binary value Auth and NonAuth in a a sequence - I would like to see start and end time of each Auth and NonAuth conditions:

Sample i/p

Expected o/p

Answer 1

create table test (dt datetime, status varchar(20))

insert into test (dt, status) 
values
('2023-01-11 12:41','Auth'),
('2023-01-11 12:42','Auth'),
('2023-01-11 12:43','Auth'),
('2023-01-11 12:44','Auth'),
('2023-01-11 12:45','Auth'),
('2023-01-11 12:46','NonAuth'),
('2023-01-11 12:47','NonAuth'),
('2023-01-11 12:48','NonAuth'),
('2023-01-11 12:49','Auth'),
('2023-01-11 12:50','Auth')

Select status,min(dt) as start_time, MAX(dt) as end_time
from (
Select status, dt 
,row_number() over(Order by dt)-row_number() over(partition by status Order by dt) as grp

from test) t

Group by status, grp
order by min(dt)


drop table test

Answer 2

Hi @John D

This is a typical Gaps and Islands Problem; you can refer to the following two articles for more details and learning.

Introduction to Gaps and Islands Analysis

SQL Server Window Functions Gaps and Islands Problem

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.