SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE #pretemp
(
  hospital_code varchar(255),
  s_visit_ident varchar(255),
  orderdt datetime
); 

INSERT INTO #pretemp (hospital_code, s_visit_ident, orderdt) VALUES
('ClevelandClinic', '1', '01-02-2020 12:30'),
('ClevelandClinic', '1','01-02-2020 12:35'),
('ClevelandClinic', '1', '01-02-2020 12:42'),
('ClevelandClinic', '1', '01-02-2020 12:55'),
('ClevelandClinic', '1','01-05-2020 18:44'),
('ClevelandClinic', '2','01-05-2020 07:09'),
('ClevelandClinic', '1','01-05-2020 07:12'),
('ClevelandClinic', '1','01-05-2020 07:18'),
('ClevelandClinic', '1', '01-07-2020 00:00'),
('ClevelandClinic', '3','01-07-2020 00:03'),
('ClevelandClinic', '3','01-07-2020 00:10');
 
select * from #pretemp;


hospital_code
s_visit_ident
orderdt


ClevelandClinic
1
2020-01-02 12:30:00.000

ClevelandClinic
1
2020-01-02 12:35:00.000

ClevelandClinic
1
2020-01-02 12:42:00.000

ClevelandClinic
1
2020-01-02 12:55:00.000

ClevelandClinic
1
2020-01-05 18:44:00.000

ClevelandClinic
2
2020-01-05 07:09:00.000

ClevelandClinic
1
2020-01-05 07:12:00.000

ClevelandClinic
1
2020-01-05 07:18:00.000

ClevelandClinic
1
2020-01-07 00:00:00.000

ClevelandClinic
3
2020-01-07 00:03:00.000

ClevelandClinic
3
2020-01-07 00:10:00.000


select p.hospital_code,
       p.s_visit_ident as patient,
       p.orderdt,
       datediff(
         minute,
         lag(p.orderdt) over(partition by p.s_visit_ident order by p.orderdt),
         p.orderdt) as diff_min,
       case when coalesce(datediff(
         minute,
         lag(p.orderdt) over(partition by p.s_visit_ident order by p.orderdt),
         p.orderdt), 15) < 15
         then 0 else 1 end as increment_group
from #pretemp p
order by p.s_visit_ident, p.orderdt;


hospital_code
patient
orderdt
diff_min
increment_group


ClevelandClinic
1
2020-01-02 12:30:00.000
null
1

ClevelandClinic
1
2020-01-02 12:35:00.000
5
0

ClevelandClinic
1
2020-01-02 12:42:00.000
7
0

ClevelandClinic
1
2020-01-02 12:55:00.000
13
0

ClevelandClinic
1
2020-01-05 07:12:00.000
3977
1

ClevelandClinic
1
2020-01-05 07:18:00.000
6
0

ClevelandClinic
1
2020-01-05 18:44:00.000
686
1

ClevelandClinic
1
2020-01-07 00:00:00.000
1756
1

ClevelandClinic
2
2020-01-05 07:09:00.000
null
1

ClevelandClinic
3
2020-01-07 00:03:00.000
null
1

ClevelandClinic
3
2020-01-07 00:10:00.000
7
0


with cte as
(
  select p.hospital_code,
         p.s_visit_ident,
         p.orderdt,
         case when coalesce(datediff(
           minute,
           lag(p.orderdt) over(partition by p.s_visit_ident order by p.orderdt),
           p.orderdt), 15) < 15
           then 0 else 1 end as increment_group
  from #pretemp p
)
select cte.hospital_code,
       cte.s_visit_ident as patient,
       cte.orderdt,
       sum(cte.increment_group)
         over(partition by cte.s_visit_ident
              order by cte.orderdt
              rows between unbounded preceding and current row) - 1 as [group]
from cte
order by cte.s_visit_ident, cte.orderdt;


hospital_code
patient
orderdt
group


ClevelandClinic
1
2020-01-02 12:30:00.000
0

ClevelandClinic
1
2020-01-02 12:35:00.000
0

ClevelandClinic
1
2020-01-02 12:42:00.000
0

ClevelandClinic
1
2020-01-02 12:55:00.000
0

ClevelandClinic
1
2020-01-05 07:12:00.000
1

ClevelandClinic
1
2020-01-05 07:18:00.000
1

ClevelandClinic
1
2020-01-05 18:44:00.000
2

ClevelandClinic
1
2020-01-07 00:00:00.000
3

ClevelandClinic
2
2020-01-05 07:09:00.000
0

ClevelandClinic
3
2020-01-07 00:03:00.000
0

ClevelandClinic
3
2020-01-07 00:10:00.000
0

hospital_code	s_visit_ident	orderdt
ClevelandClinic	1	2020-01-02 12:30:00.000
ClevelandClinic	1	2020-01-02 12:35:00.000
ClevelandClinic	1	2020-01-02 12:42:00.000
ClevelandClinic	1	2020-01-02 12:55:00.000
ClevelandClinic	1	2020-01-05 18:44:00.000
ClevelandClinic	2	2020-01-05 07:09:00.000
ClevelandClinic	1	2020-01-05 07:12:00.000
ClevelandClinic	1	2020-01-05 07:18:00.000
ClevelandClinic	1	2020-01-07 00:00:00.000
ClevelandClinic	3	2020-01-07 00:03:00.000
ClevelandClinic	3	2020-01-07 00:10:00.000

hospital_code	patient	orderdt	diff_min	increment_group
ClevelandClinic	1	2020-01-02 12:30:00.000	null	1
ClevelandClinic	1	2020-01-02 12:35:00.000	5	0
ClevelandClinic	1	2020-01-02 12:42:00.000	7	0
ClevelandClinic	1	2020-01-02 12:55:00.000	13	0
ClevelandClinic	1	2020-01-05 07:12:00.000	3977	1
ClevelandClinic	1	2020-01-05 07:18:00.000	6	0
ClevelandClinic	1	2020-01-05 18:44:00.000	686	1
ClevelandClinic	1	2020-01-07 00:00:00.000	1756	1
ClevelandClinic	2	2020-01-05 07:09:00.000	null	1
ClevelandClinic	3	2020-01-07 00:03:00.000	null	1
ClevelandClinic	3	2020-01-07 00:10:00.000	7	0