SQL Server 2014

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

CREATE TABLE [SESSION] (
    START_DATE DATE,
    END_DATE DATE,
    PAID DECIMAL(18,2),
    SESSION_ID INT,
    SESSION_TYPE INT,
    SESSION_ENTER_DATE DATE
) 
INSERT INTO [SESSION] (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES 
('4/13/2016', '4/26/2016', '32.53', '7415', '3256', '4/20/2016') 
,('4/27/2016', '5/10/2016', '32.53', '7415', '3256', '5/3/2016') 
,('5/11/2016', '5/24/2016', '32.53', '7415', '3256', '5/13/2016') 
,('11/11/2017', '12/8/2017', '43.59', '7415', '4963', '12/5/2017') 
,('12/9/2017', '1/5/2018', '43.59', '7415', '4963', '1/3/2018') 
,('1/6/2018', '2/2/2018', '43.59', '7415', '4963', '1/12/2018') 
,('2/3/2018', '3/2/2018', '43.59', '7415', '4963', '2/16/2018') 
,('3/31/2018', '4/27/2018', '43.59', '7415', '4963', '4/17/2018') 
,('6/23/2018', '7/20/2018', '43.59', '7415', '4963', '7/5/2018') 
,('7/21/2018', '8/17/2018', '43.59', '7415', '4963', '8/7/2018') 
,('8/18/2018', '9/14/2018', '43.59', '7415', '4963', '9/10/2018') 
,('9/15/2018', '10/12/2018', '43.59', '7415', '4963', '9/27/2018') 
,('10/13/2018', '11/9/2018', '43.59', '7415', '4963', '10/26/2018') 
,('12/9/2018', '12/28/2018', '37.02', '7415', '4963', '11/29/2018') 
,('12/29/2018', '12/29/2018', '63.6', '7415', '4963', '1/2/2019') 
,('2/17/2022', '3/2/2022', '48.58', '8642', '3256', '2/25/2022') 
,('2/17/2022', '5/12/2022', '29.27', '8642', '3256', '7/27/2022') 
,('3/3/2022', '3/9/2022', '46.6', '8642', '3256', '3/7/2022') 
,('3/10/2022', '3/16/2022', '46.6', '8642', '3256', '3/14/2022') 
,('3/17/2022', '3/23/2022', '46.6', '8642', '3256', '3/21/2022') 
,('3/24/2022', '3/30/2022', '46.6', '8642', '3256', '3/28/2022') 
,('3/31/2022', '4/6/2022', '46.6', '8642', '3256', '4/4/2022') 
,('4/7/2022', '4/13/2022', '46.6', '8642', '3256', '4/11/2022') 
,('4/14/2022', '4/20/2022', '46.6', '8642', '3256', '4/18/2022') 
,('4/21/2022', '4/27/2022', '46.6', '8642', '3256', '4/25/2022') 
,('4/28/2022', '5/4/2022', '46.6', '8642', '3256', '5/10/2022') 
,('5/5/2022', '5/11/2022', '46.6', '8642', '3256', '5/10/2022') 
,('5/12/2022', '5/12/2022', '109.8', '8642', '3256', '5/13/2022') 
,('6/2/2022', '6/15/2022', '85.2', '8642', '3256', '6/17/2022') 
,('6/2/2022', '7/27/2022', '48.72', '8642', '3256', '7/27/2022') 
,('6/16/2022', '6/22/2022', '46.6', '8642', '3256', '6/17/2022') 
,('6/23/2022', '6/29/2022', '46.6', '8642', '3256', '6/27/2022') 
,('6/30/2022', '7/6/2022', '46.6', '8642', '3256', '7/4/2022') 
,('7/7/2022', '7/13/2022', '46.6', '8642', '3256', '7/11/2022') 
,('7/14/2022', '7/20/2022', '46.6', '8642', '3256', '7/18/2022') 
,('7/21/2022', '7/27/2022', '46.6', '8642', '3256', '7/25/2022') 
,('7/28/2022', '8/3/2022', '61.19', '8642', '3256', '8/1/2022') 
,('8/4/2022', '8/10/2022', '61.19', '8642', '3256', '8/8/2022') 
,('8/11/2022', '8/17/2022', '61.19', '8642', '3256', '8/15/2022') 
,('8/18/2022', '8/24/2022', '61.19', '8642', '3256', '8/22/2022') 
,('8/25/2022', '8/31/2022', '61.19', '8642', '3256', '8/29/2022') 
,('9/1/2022', '9/7/2022', '61.19', '8642', '3256', '9/5/2022') 
,('4/19/2023', '4/25/2023', '61.19', '8642', '3256', '4/19/2023') 
,('4/26/2023', '5/2/2023', '61.19', '8642', '3256', '4/24/2023') 
,('5/3/2023', '5/9/2023', '61.19', '8642', '3256', '5/1/2023') 
,('5/10/2023', '5/16/2023', '61.19', '8642', '3256', '5/8/2023') 
,('5/17/2023', '5/23/2023', '61.19', '8642', '3256', '5/15/2023') 
,('5/24/2023', '5/30/2023', '61.19', '8642', '3256', '5/22/2023') 
,('5/31/2023', '6/6/2023', '61.19', '8642', '3256', '5/23/2023') 
,('6/7/2023', '6/13/2023', '61.19', '8642', '3256', '6/2/2023') 
,('1/2/2024', '1/8/2024', '17.48', '8642', '3256', '1/3/2024') 
,('1/2/2024', '1/7/2024', '34.97', '8642', '3256', '1/19/2024') 
,('2/22/2024', '4/11/2024', '18', '8642', '4963', '3/29/2024') 
,('4/12/2024', '4/18/2024', '71', '8642', '4963', '4/13/2024') 
,('4/19/2024', '4/25/2024', '71', '8642', '4963', '4/19/2024') 
,('4/26/2024', '5/2/2024', '71', '8642', '4963', '4/27/2024') 
,('5/3/2024', '5/9/2024', '71', '8642', '4963', '5/6/2024') 
,('5/10/2024', '5/16/2024', '71', '8642', '4963', '5/10/2024') 
,('5/17/2024', '5/23/2024', '71', '8642', '4963', '5/20/2024') 
,('5/24/2024', '5/30/2024', '71', '8642', '4963', '5/24/2024') 
,('5/31/2024', '6/6/2024', '71', '8642', '4963', '6/3/2024') 
,('6/7/2024', '6/13/2024', '71', '8642', '4963', '6/7/2024') 
,('6/14/2024', '6/20/2024', '71', '8642', '4963', '6/14/2024') 
,('6/21/2024', '6/27/2024', '71', '8642', '4963', '6/21/2024') 
,('6/28/2024', '7/4/2024', '71', '8642', '4963', '6/28/2024') 
,('7/5/2024', '7/11/2024', '71', '8642', '4963', '7/5/2024') 
,('7/12/2024', '7/18/2024', '71', '8642', '4963', '7/12/2024') 
,('7/19/2024', '7/25/2024', '71', '8642', '4963', '7/19/2024') 
,('7/26/2024', '8/1/2024', '71', '8642', '4963', '7/26/2024') 
,('8/2/2024', '8/8/2024', '71', '8642', '4963', '8/2/2024') 
,('8/9/2024', '8/15/2024', '71', '8642', '4963', '8/9/2024') 
,('8/16/2024', '8/22/2024', '71', '8642', '4963', '8/16/2024') 
,('8/23/2024', '8/29/2024', '71', '8642', '4963', '8/23/2024') 
,('8/30/2024', '9/5/2024', '71', '8642', '4963', '8/30/2024') 
,('9/6/2024', '9/12/2024', '71', '8642', '4963', '9/6/2024') 
,('9/13/2024', '9/19/2024', '71', '8642', '4963', '9/13/2024') 
,('9/20/2024', '9/26/2024', '71', '8642', '4963', '9/20/2024') 
,('9/27/2024', '10/3/2024', '71', '8642', '4963', '9/27/2024') 
,('10/4/2024', '10/10/2024', '71', '8642', '4963', '10/4/2024') 
,('10/11/2024', '10/17/2024', '71', '8642', '4963', '10/11/2024') 
,('10/18/2024', '10/31/2024', '48', '8642', '4963', '10/31/2024') 
,('11/1/2024', '11/7/2024', '71', '8642', '4963', '10/30/2024') 
,('11/8/2024', '11/14/2024', '71', '8642', '4963', '11/9/2024') 
,('11/15/2024', '11/21/2024', '71', '8642', '4963', '11/16/2024') 
,('11/22/2024', '11/28/2024', '71', '8642', '4963', '11/21/2024') 
,('11/29/2024', '12/5/2024', '71', '8642', '4963', '11/26/2024') 
,('12/6/2024', '12/12/2024', '71', '8642', '4963', '12/7/2024') 
,('12/13/2024', '12/19/2024', '71', '8642', '4963', '12/14/2024') 
,('12/20/2024', '12/26/2024', '71', '8642', '4963', NULL) 
,('12/20/2024', '12/26/2024', '71', '8642', '4963', NULL) 
,('12/20/2024', '12/26/2024', '71', '8642', '3256', '12/25/2024') 
,('12/20/2024', '12/26/2024', '71', '8642', '4963', '12/25/2024') 
,('12/27/2024', '1/2/2025', '71', '8642', '4963', '12/25/2024') 
;
select  * from session order by session_id,session_type,start_date


START_DATE
END_DATE
PAID
SESSION_ID
SESSION_TYPE
SESSION_ENTER_DATE


2016-04-13
2016-04-26
32.53
7415
3256
2016-04-20

2016-04-27
2016-05-10
32.53
7415
3256
2016-05-03

2016-05-11
2016-05-24
32.53
7415
3256
2016-05-13

2017-11-11
2017-12-08
43.59
7415
4963
2017-12-05

2017-12-09
2018-01-05
43.59
7415
4963
2018-01-03

2018-01-06
2018-02-02
43.59
7415
4963
2018-01-12

2018-02-03
2018-03-02
43.59
7415
4963
2018-02-16

2018-03-31
2018-04-27
43.59
7415
4963
2018-04-17

2018-06-23
2018-07-20
43.59
7415
4963
2018-07-05

2018-07-21
2018-08-17
43.59
7415
4963
2018-08-07

2018-08-18
2018-09-14
43.59
7415
4963
2018-09-10

2018-09-15
2018-10-12
43.59
7415
4963
2018-09-27

2018-10-13
2018-11-09
43.59
7415
4963
2018-10-26

2018-12-09
2018-12-28
37.02
7415
4963
2018-11-29

2018-12-29
2018-12-29
63.60
7415
4963
2019-01-02

2022-02-17
2022-03-02
48.58
8642
3256
2022-02-25

2022-02-17
2022-05-12
29.27
8642
3256
2022-07-27

2022-03-03
2022-03-09
46.60
8642
3256
2022-03-07

2022-03-10
2022-03-16
46.60
8642
3256
2022-03-14

2022-03-17
2022-03-23
46.60
8642
3256
2022-03-21

2022-03-24
2022-03-30
46.60
8642
3256
2022-03-28

2022-03-31
2022-04-06
46.60
8642
3256
2022-04-04

2022-04-07
2022-04-13
46.60
8642
3256
2022-04-11

2022-04-14
2022-04-20
46.60
8642
3256
2022-04-18

2022-04-21
2022-04-27
46.60
8642
3256
2022-04-25

2022-04-28
2022-05-04
46.60
8642
3256
2022-05-10

2022-05-05
2022-05-11
46.60
8642
3256
2022-05-10

2022-05-12
2022-05-12
109.80
8642
3256
2022-05-13

2022-06-02
2022-06-15
85.20
8642
3256
2022-06-17

2022-06-02
2022-07-27
48.72
8642
3256
2022-07-27

2022-06-16
2022-06-22
46.60
8642
3256
2022-06-17

2022-06-23
2022-06-29
46.60
8642
3256
2022-06-27

2022-06-30
2022-07-06
46.60
8642
3256
2022-07-04

2022-07-07
2022-07-13
46.60
8642
3256
2022-07-11

2022-07-14
2022-07-20
46.60
8642
3256
2022-07-18

2022-07-21
2022-07-27
46.60
8642
3256
2022-07-25

2022-07-28
2022-08-03
61.19
8642
3256
2022-08-01

2022-08-04
2022-08-10
61.19
8642
3256
2022-08-08

2022-08-11
2022-08-17
61.19
8642
3256
2022-08-15

2022-08-18
2022-08-24
61.19
8642
3256
2022-08-22

2022-08-25
2022-08-31
61.19
8642
3256
2022-08-29

2022-09-01
2022-09-07
61.19
8642
3256
2022-09-05

2023-04-19
2023-04-25
61.19
8642
3256
2023-04-19

2023-04-26
2023-05-02
61.19
8642
3256
2023-04-24

2023-05-03
2023-05-09
61.19
8642
3256
2023-05-01

2023-05-10
2023-05-16
61.19
8642
3256
2023-05-08

2023-05-17
2023-05-23
61.19
8642
3256
2023-05-15

2023-05-24
2023-05-30
61.19
8642
3256
2023-05-22

2023-05-31
2023-06-06
61.19
8642
3256
2023-05-23

2023-06-07
2023-06-13
61.19
8642
3256
2023-06-02

2024-01-02
2024-01-08
17.48
8642
3256
2024-01-03

2024-01-02
2024-01-07
34.97
8642
3256
2024-01-19

2024-12-20
2024-12-26
71.00
8642
3256
2024-12-25

2024-02-22
2024-04-11
18.00
8642
4963
2024-03-29

2024-04-12
2024-04-18
71.00
8642
4963
2024-04-13

2024-04-19
2024-04-25
71.00
8642
4963
2024-04-19

2024-04-26
2024-05-02
71.00
8642
4963
2024-04-27

2024-05-03
2024-05-09
71.00
8642
4963
2024-05-06

2024-05-10
2024-05-16
71.00
8642
4963
2024-05-10

2024-05-17
2024-05-23
71.00
8642
4963
2024-05-20

2024-05-24
2024-05-30
71.00
8642
4963
2024-05-24

2024-05-31
2024-06-06
71.00
8642
4963
2024-06-03

2024-06-07
2024-06-13
71.00
8642
4963
2024-06-07

2024-06-14
2024-06-20
71.00
8642
4963
2024-06-14

2024-06-21
2024-06-27
71.00
8642
4963
2024-06-21

2024-06-28
2024-07-04
71.00
8642
4963
2024-06-28

2024-07-05
2024-07-11
71.00
8642
4963
2024-07-05

2024-07-12
2024-07-18
71.00
8642
4963
2024-07-12

2024-07-19
2024-07-25
71.00
8642
4963
2024-07-19

2024-07-26
2024-08-01
71.00
8642
4963
2024-07-26

2024-08-02
2024-08-08
71.00
8642
4963
2024-08-02

2024-08-09
2024-08-15
71.00
8642
4963
2024-08-09

2024-08-16
2024-08-22
71.00
8642
4963
2024-08-16

2024-08-23
2024-08-29
71.00
8642
4963
2024-08-23

2024-08-30
2024-09-05
71.00
8642
4963
2024-08-30

2024-09-06
2024-09-12
71.00
8642
4963
2024-09-06

2024-09-13
2024-09-19
71.00
8642
4963
2024-09-13

2024-09-20
2024-09-26
71.00
8642
4963
2024-09-20

2024-09-27
2024-10-03
71.00
8642
4963
2024-09-27

2024-10-04
2024-10-10
71.00
8642
4963
2024-10-04

2024-10-11
2024-10-17
71.00
8642
4963
2024-10-11

2024-10-18
2024-10-31
48.00
8642
4963
2024-10-31

2024-11-01
2024-11-07
71.00
8642
4963
2024-10-30

2024-11-08
2024-11-14
71.00
8642
4963
2024-11-09

2024-11-15
2024-11-21
71.00
8642
4963
2024-11-16

2024-11-22
2024-11-28
71.00
8642
4963
2024-11-21

2024-11-29
2024-12-05
71.00
8642
4963
2024-11-26

2024-12-06
2024-12-12
71.00
8642
4963
2024-12-07

2024-12-13
2024-12-19
71.00
8642
4963
2024-12-14

2024-12-20
2024-12-26
71.00
8642
4963
null

2024-12-20
2024-12-26
71.00
8642
4963
null

2024-12-20
2024-12-26
71.00
8642
4963
2024-12-25

2024-12-27
2025-01-02
71.00
8642
4963
2024-12-25


with gaps as (
select * 
  ,case when max_enter_date<next_start_date 
       and datediff(day,end_date
                         ,next_start_date
             )>1
   then 1 else 0 end isGap
from(
  select *
    ,lead(start_date,1,end_date)
         over(partition by session_id,session_type order by start_date) next_start_date
    ,max(case when session_enter_date is null then getdate() else session_enter_date end)
        over(partition by session_id,session_type order by start_date) max_enter_date
  from session
)a
)
,t as(
select *
  ,row_number()over(partition by session_id,session_type,grn order by start_date desc)rn
  from(
    select *
      ,coalesce(sum(isGap)over(partition by session_id,session_type order by start_date
               rows between unbounded preceding and 1 preceding) 
         ,0) grn
    from gaps
 )grn
)
select * from t order by session_id,session_type,start_date


START_DATE
END_DATE
PAID
SESSION_ID
SESSION_TYPE
SESSION_ENTER_DATE
next_start_date
max_enter_date
isGap
grn
rn


2016-04-13
2016-04-26
32.53
7415
3256
2016-04-20
2016-04-27
2016-04-20 00:00:00.000
0
0
3

2016-04-27
2016-05-10
32.53
7415
3256
2016-05-03
2016-05-11
2016-05-03 00:00:00.000
0
0
2

2016-05-11
2016-05-24
32.53
7415
3256
2016-05-13
2016-05-24
2016-05-13 00:00:00.000
0
0
1

2017-11-11
2017-12-08
43.59
7415
4963
2017-12-05
2017-12-09
2017-12-05 00:00:00.000
0
0
4

2017-12-09
2018-01-05
43.59
7415
4963
2018-01-03
2018-01-06
2018-01-03 00:00:00.000
0
0
3

2018-01-06
2018-02-02
43.59
7415
4963
2018-01-12
2018-02-03
2018-01-12 00:00:00.000
0
0
2

2018-02-03
2018-03-02
43.59
7415
4963
2018-02-16
2018-03-31
2018-02-16 00:00:00.000
1
0
1

2018-03-31
2018-04-27
43.59
7415
4963
2018-04-17
2018-06-23
2018-04-17 00:00:00.000
1
1
1

2018-06-23
2018-07-20
43.59
7415
4963
2018-07-05
2018-07-21
2018-07-05 00:00:00.000
0
2
5

2018-07-21
2018-08-17
43.59
7415
4963
2018-08-07
2018-08-18
2018-08-07 00:00:00.000
0
2
4

2018-08-18
2018-09-14
43.59
7415
4963
2018-09-10
2018-09-15
2018-09-10 00:00:00.000
0
2
3

2018-09-15
2018-10-12
43.59
7415
4963
2018-09-27
2018-10-13
2018-09-27 00:00:00.000
0
2
2

2018-10-13
2018-11-09
43.59
7415
4963
2018-10-26
2018-12-09
2018-10-26 00:00:00.000
1
2
1

2018-12-09
2018-12-28
37.02
7415
4963
2018-11-29
2018-12-29
2018-11-29 00:00:00.000
0
3
2

2018-12-29
2018-12-29
63.60
7415
4963
2019-01-02
2018-12-29
2019-01-02 00:00:00.000
0
3
1

2022-02-17
2022-03-02
48.58
8642
3256
2022-02-25
2022-02-17
2022-07-27 00:00:00.000
0
0
26

2022-02-17
2022-05-12
29.27
8642
3256
2022-07-27
2022-03-03
2022-07-27 00:00:00.000
0
0
27

2022-03-03
2022-03-09
46.60
8642
3256
2022-03-07
2022-03-10
2022-07-27 00:00:00.000
0
0
25

2022-03-10
2022-03-16
46.60
8642
3256
2022-03-14
2022-03-17
2022-07-27 00:00:00.000
0
0
24

2022-03-17
2022-03-23
46.60
8642
3256
2022-03-21
2022-03-24
2022-07-27 00:00:00.000
0
0
23

2022-03-24
2022-03-30
46.60
8642
3256
2022-03-28
2022-03-31
2022-07-27 00:00:00.000
0
0
22

2022-03-31
2022-04-06
46.60
8642
3256
2022-04-04
2022-04-07
2022-07-27 00:00:00.000
0
0
21

2022-04-07
2022-04-13
46.60
8642
3256
2022-04-11
2022-04-14
2022-07-27 00:00:00.000
0
0
20

2022-04-14
2022-04-20
46.60
8642
3256
2022-04-18
2022-04-21
2022-07-27 00:00:00.000
0
0
19

2022-04-21
2022-04-27
46.60
8642
3256
2022-04-25
2022-04-28
2022-07-27 00:00:00.000
0
0
18

2022-04-28
2022-05-04
46.60
8642
3256
2022-05-10
2022-05-05
2022-07-27 00:00:00.000
0
0
17

2022-05-05
2022-05-11
46.60
8642
3256
2022-05-10
2022-05-12
2022-07-27 00:00:00.000
0
0
16

2022-05-12
2022-05-12
109.80
8642
3256
2022-05-13
2022-06-02
2022-07-27 00:00:00.000
0
0
15

2022-06-02
2022-06-15
85.20
8642
3256
2022-06-17
2022-06-02
2022-07-27 00:00:00.000
0
0
13

2022-06-02
2022-07-27
48.72
8642
3256
2022-07-27
2022-06-16
2022-07-27 00:00:00.000
0
0
14

2022-06-16
2022-06-22
46.60
8642
3256
2022-06-17
2022-06-23
2022-07-27 00:00:00.000
0
0
12

2022-06-23
2022-06-29
46.60
8642
3256
2022-06-27
2022-06-30
2022-07-27 00:00:00.000
0
0
11

2022-06-30
2022-07-06
46.60
8642
3256
2022-07-04
2022-07-07
2022-07-27 00:00:00.000
0
0
10

2022-07-07
2022-07-13
46.60
8642
3256
2022-07-11
2022-07-14
2022-07-27 00:00:00.000
0
0
9

2022-07-14
2022-07-20
46.60
8642
3256
2022-07-18
2022-07-21
2022-07-27 00:00:00.000
0
0
8

2022-07-21
2022-07-27
46.60
8642
3256
2022-07-25
2022-07-28
2022-07-27 00:00:00.000
0
0
7

2022-07-28
2022-08-03
61.19
8642
3256
2022-08-01
2022-08-04
2022-08-01 00:00:00.000
0
0
6

2022-08-04
2022-08-10
61.19
8642
3256
2022-08-08
2022-08-11
2022-08-08 00:00:00.000
0
0
5

2022-08-11
2022-08-17
61.19
8642
3256
2022-08-15
2022-08-18
2022-08-15 00:00:00.000
0
0
4

2022-08-18
2022-08-24
61.19
8642
3256
2022-08-22
2022-08-25
2022-08-22 00:00:00.000
0
0
3

2022-08-25
2022-08-31
61.19
8642
3256
2022-08-29
2022-09-01
2022-08-29 00:00:00.000
0
0
2

2022-09-01
2022-09-07
61.19
8642
3256
2022-09-05
2023-04-19
2022-09-05 00:00:00.000
1
0
1

2023-04-19
2023-04-25
61.19
8642
3256
2023-04-19
2023-04-26
2023-04-19 00:00:00.000
0
1
8

2023-04-26
2023-05-02
61.19
8642
3256
2023-04-24
2023-05-03
2023-04-24 00:00:00.000
0
1
7

2023-05-03
2023-05-09
61.19
8642
3256
2023-05-01
2023-05-10
2023-05-01 00:00:00.000
0
1
6

2023-05-10
2023-05-16
61.19
8642
3256
2023-05-08
2023-05-17
2023-05-08 00:00:00.000
0
1
5

2023-05-17
2023-05-23
61.19
8642
3256
2023-05-15
2023-05-24
2023-05-15 00:00:00.000
0
1
4

2023-05-24
2023-05-30
61.19
8642
3256
2023-05-22
2023-05-31
2023-05-22 00:00:00.000
0
1
3

2023-05-31
2023-06-06
61.19
8642
3256
2023-05-23
2023-06-07
2023-05-23 00:00:00.000
0
1
2

2023-06-07
2023-06-13
61.19
8642
3256
2023-06-02
2024-01-02
2023-06-02 00:00:00.000
1
1
1

2024-01-02
2024-01-08
17.48
8642
3256
2024-01-03
2024-01-02
2024-01-19 00:00:00.000
0
2
1

2024-01-02
2024-01-07
34.97
8642
3256
2024-01-19
2024-12-20
2024-01-19 00:00:00.000
1
2
2

2024-12-20
2024-12-26
71.00
8642
3256
2024-12-25
2024-12-26
2024-12-25 00:00:00.000
0
3
1

2024-02-22
2024-04-11
18.00
8642
4963
2024-03-29
2024-04-12
2024-03-29 00:00:00.000
0
0
40

2024-04-12
2024-04-18
71.00
8642
4963
2024-04-13
2024-04-19
2024-04-13 00:00:00.000
0
0
39

2024-04-19
2024-04-25
71.00
8642
4963
2024-04-19
2024-04-26
2024-04-19 00:00:00.000
0
0
38

2024-04-26
2024-05-02
71.00
8642
4963
2024-04-27
2024-05-03
2024-04-27 00:00:00.000
0
0
37

2024-05-03
2024-05-09
71.00
8642
4963
2024-05-06
2024-05-10
2024-05-06 00:00:00.000
0
0
36

2024-05-10
2024-05-16
71.00
8642
4963
2024-05-10
2024-05-17
2024-05-10 00:00:00.000
0
0
35

2024-05-17
2024-05-23
71.00
8642
4963
2024-05-20
2024-05-24
2024-05-20 00:00:00.000
0
0
34

2024-05-24
2024-05-30
71.00
8642
4963
2024-05-24
2024-05-31
2024-05-24 00:00:00.000
0
0
33

2024-05-31
2024-06-06
71.00
8642
4963
2024-06-03
2024-06-07
2024-06-03 00:00:00.000
0
0
32

2024-06-07
2024-06-13
71.00
8642
4963
2024-06-07
2024-06-14
2024-06-07 00:00:00.000
0
0
31

2024-06-14
2024-06-20
71.00
8642
4963
2024-06-14
2024-06-21
2024-06-14 00:00:00.000
0
0
30

2024-06-21
2024-06-27
71.00
8642
4963
2024-06-21
2024-06-28
2024-06-21 00:00:00.000
0
0
29

2024-06-28
2024-07-04
71.00
8642
4963
2024-06-28
2024-07-05
2024-06-28 00:00:00.000
0
0
28

2024-07-05
2024-07-11
71.00
8642
4963
2024-07-05
2024-07-12
2024-07-05 00:00:00.000
0
0
27

2024-07-12
2024-07-18
71.00
8642
4963
2024-07-12
2024-07-19
2024-07-12 00:00:00.000
0
0
26

2024-07-19
2024-07-25
71.00
8642
4963
2024-07-19
2024-07-26
2024-07-19 00:00:00.000
0
0
25

2024-07-26
2024-08-01
71.00
8642
4963
2024-07-26
2024-08-02
2024-07-26 00:00:00.000
0
0
24

2024-08-02
2024-08-08
71.00
8642
4963
2024-08-02
2024-08-09
2024-08-02 00:00:00.000
0
0
23

2024-08-09
2024-08-15
71.00
8642
4963
2024-08-09
2024-08-16
2024-08-09 00:00:00.000
0
0
22

2024-08-16
2024-08-22
71.00
8642
4963
2024-08-16
2024-08-23
2024-08-16 00:00:00.000
0
0
21

2024-08-23
2024-08-29
71.00
8642
4963
2024-08-23
2024-08-30
2024-08-23 00:00:00.000
0
0
20

2024-08-30
2024-09-05
71.00
8642
4963
2024-08-30
2024-09-06
2024-08-30 00:00:00.000
0
0
19

2024-09-06
2024-09-12
71.00
8642
4963
2024-09-06
2024-09-13
2024-09-06 00:00:00.000
0
0
18

2024-09-13
2024-09-19
71.00
8642
4963
2024-09-13
2024-09-20
2024-09-13 00:00:00.000
0
0
17

2024-09-20
2024-09-26
71.00
8642
4963
2024-09-20
2024-09-27
2024-09-20 00:00:00.000
0
0
16

2024-09-27
2024-10-03
71.00
8642
4963
2024-09-27
2024-10-04
2024-09-27 00:00:00.000
0
0
15

2024-10-04
2024-10-10
71.00
8642
4963
2024-10-04
2024-10-11
2024-10-04 00:00:00.000
0
0
14

2024-10-11
2024-10-17
71.00
8642
4963
2024-10-11
2024-10-18
2024-10-11 00:00:00.000
0
0
13

2024-10-18
2024-10-31
48.00
8642
4963
2024-10-31
2024-11-01
2024-10-31 00:00:00.000
0
0
12

2024-11-01
2024-11-07
71.00
8642
4963
2024-10-30
2024-11-08
2024-10-31 00:00:00.000
0
0
11

2024-11-08
2024-11-14
71.00
8642
4963
2024-11-09
2024-11-15
2024-11-09 00:00:00.000
0
0
10

2024-11-15
2024-11-21
71.00
8642
4963
2024-11-16
2024-11-22
2024-11-16 00:00:00.000
0
0
9

2024-11-22
2024-11-28
71.00
8642
4963
2024-11-21
2024-11-29
2024-11-21 00:00:00.000
0
0
8

2024-11-29
2024-12-05
71.00
8642
4963
2024-11-26
2024-12-06
2024-11-26 00:00:00.000
0
0
7

2024-12-06
2024-12-12
71.00
8642
4963
2024-12-07
2024-12-13
2024-12-07 00:00:00.000
0
0
6

2024-12-13
2024-12-19
71.00
8642
4963
2024-12-14
2024-12-20
2024-12-14 00:00:00.000
0
0
5

2024-12-20
2024-12-26
71.00
8642
4963
null
2024-12-20
2025-01-21 06:22:30.427
0
0
2

2024-12-20
2024-12-26
71.00
8642
4963
null
2024-12-20
2025-01-21 06:22:30.427
0
0
3

2024-12-20
2024-12-26
71.00
8642
4963
2024-12-25
2024-12-27
2025-01-21 06:22:30.427
0
0
4

2024-12-27
2025-01-02
71.00
8642
4963
2024-12-25
2025-01-02
2025-01-21 06:22:30.427
0
0
1


with gaps as (
select * 
  ,case when max_enter_date<next_start_date 
       and datediff(day,end_date
                         ,next_start_date
             )>1
   then 1 else 0 end isGap
from(
  select *
    ,lead(start_date,1,end_date)
         over(partition by session_id,session_type order by start_date) next_start_date
    ,max(case when session_enter_date is null then getdate() else session_enter_date end)
        over(partition by session_id,session_type order by start_date) max_enter_date
  from session
)a
)
,t as(
select *
  ,row_number()over(partition by session_id,session_type,grn order by start_date desc)rn
  from(
    select *
      ,coalesce(sum(isGap)over(partition by session_id,session_type order by start_date
               rows between unbounded preceding and 1 preceding) 
         ,0) grn
    from gaps
 )grn
)
select session_id,session_type,grn
  ,min(start_date) start_date
  ,max(end_date) end_date
  ,max(case when rn=1 then paid end)paid
from t
group by session_id,session_type,grn
;


session_id
session_type
grn
start_date
end_date
paid


7415
3256
0
2016-04-13
2016-05-24
32.53

7415
4963
0
2017-11-11
2018-03-02
43.59

7415
4963
1
2018-03-31
2018-04-27
43.59

7415
4963
2
2018-06-23
2018-11-09
43.59

7415
4963
3
2018-12-09
2018-12-29
63.60

8642
3256
0
2022-02-17
2022-09-07
61.19

8642
3256
1
2023-04-19
2023-06-13
61.19

8642
3256
2
2024-01-02
2024-01-08
17.48

8642
3256
3
2024-12-20
2024-12-26
71.00

8642
4963
0
2024-02-22
2025-01-02
71.00


Warning: Null value is eliminated by an aggregate or other SET operation.

START_DATE	END_DATE	PAID	SESSION_ID	SESSION_TYPE	SESSION_ENTER_DATE
2016-04-13	2016-04-26	32.53	7415	3256	2016-04-20
2016-04-27	2016-05-10	32.53	7415	3256	2016-05-03
2016-05-11	2016-05-24	32.53	7415	3256	2016-05-13
2017-11-11	2017-12-08	43.59	7415	4963	2017-12-05
2017-12-09	2018-01-05	43.59	7415	4963	2018-01-03
2018-01-06	2018-02-02	43.59	7415	4963	2018-01-12
2018-02-03	2018-03-02	43.59	7415	4963	2018-02-16
2018-03-31	2018-04-27	43.59	7415	4963	2018-04-17
2018-06-23	2018-07-20	43.59	7415	4963	2018-07-05
2018-07-21	2018-08-17	43.59	7415	4963	2018-08-07
2018-08-18	2018-09-14	43.59	7415	4963	2018-09-10
2018-09-15	2018-10-12	43.59	7415	4963	2018-09-27
2018-10-13	2018-11-09	43.59	7415	4963	2018-10-26
2018-12-09	2018-12-28	37.02	7415	4963	2018-11-29
2018-12-29	2018-12-29	63.60	7415	4963	2019-01-02
2022-02-17	2022-03-02	48.58	8642	3256	2022-02-25
2022-02-17	2022-05-12	29.27	8642	3256	2022-07-27
2022-03-03	2022-03-09	46.60	8642	3256	2022-03-07
2022-03-10	2022-03-16	46.60	8642	3256	2022-03-14
2022-03-17	2022-03-23	46.60	8642	3256	2022-03-21
2022-03-24	2022-03-30	46.60	8642	3256	2022-03-28
2022-03-31	2022-04-06	46.60	8642	3256	2022-04-04
2022-04-07	2022-04-13	46.60	8642	3256	2022-04-11
2022-04-14	2022-04-20	46.60	8642	3256	2022-04-18
2022-04-21	2022-04-27	46.60	8642	3256	2022-04-25
2022-04-28	2022-05-04	46.60	8642	3256	2022-05-10
2022-05-05	2022-05-11	46.60	8642	3256	2022-05-10
2022-05-12	2022-05-12	109.80	8642	3256	2022-05-13
2022-06-02	2022-06-15	85.20	8642	3256	2022-06-17
2022-06-02	2022-07-27	48.72	8642	3256	2022-07-27
2022-06-16	2022-06-22	46.60	8642	3256	2022-06-17
2022-06-23	2022-06-29	46.60	8642	3256	2022-06-27
2022-06-30	2022-07-06	46.60	8642	3256	2022-07-04
2022-07-07	2022-07-13	46.60	8642	3256	2022-07-11
2022-07-14	2022-07-20	46.60	8642	3256	2022-07-18
2022-07-21	2022-07-27	46.60	8642	3256	2022-07-25
2022-07-28	2022-08-03	61.19	8642	3256	2022-08-01
2022-08-04	2022-08-10	61.19	8642	3256	2022-08-08
2022-08-11	2022-08-17	61.19	8642	3256	2022-08-15
2022-08-18	2022-08-24	61.19	8642	3256	2022-08-22
2022-08-25	2022-08-31	61.19	8642	3256	2022-08-29
2022-09-01	2022-09-07	61.19	8642	3256	2022-09-05
2023-04-19	2023-04-25	61.19	8642	3256	2023-04-19
2023-04-26	2023-05-02	61.19	8642	3256	2023-04-24
2023-05-03	2023-05-09	61.19	8642	3256	2023-05-01
2023-05-10	2023-05-16	61.19	8642	3256	2023-05-08
2023-05-17	2023-05-23	61.19	8642	3256	2023-05-15
2023-05-24	2023-05-30	61.19	8642	3256	2023-05-22
2023-05-31	2023-06-06	61.19	8642	3256	2023-05-23
2023-06-07	2023-06-13	61.19	8642	3256	2023-06-02
2024-01-02	2024-01-08	17.48	8642	3256	2024-01-03
2024-01-02	2024-01-07	34.97	8642	3256	2024-01-19
2024-12-20	2024-12-26	71.00	8642	3256	2024-12-25
2024-02-22	2024-04-11	18.00	8642	4963	2024-03-29
2024-04-12	2024-04-18	71.00	8642	4963	2024-04-13
2024-04-19	2024-04-25	71.00	8642	4963	2024-04-19
2024-04-26	2024-05-02	71.00	8642	4963	2024-04-27
2024-05-03	2024-05-09	71.00	8642	4963	2024-05-06
2024-05-10	2024-05-16	71.00	8642	4963	2024-05-10
2024-05-17	2024-05-23	71.00	8642	4963	2024-05-20
2024-05-24	2024-05-30	71.00	8642	4963	2024-05-24
2024-05-31	2024-06-06	71.00	8642	4963	2024-06-03
2024-06-07	2024-06-13	71.00	8642	4963	2024-06-07
2024-06-14	2024-06-20	71.00	8642	4963	2024-06-14
2024-06-21	2024-06-27	71.00	8642	4963	2024-06-21
2024-06-28	2024-07-04	71.00	8642	4963	2024-06-28
2024-07-05	2024-07-11	71.00	8642	4963	2024-07-05
2024-07-12	2024-07-18	71.00	8642	4963	2024-07-12
2024-07-19	2024-07-25	71.00	8642	4963	2024-07-19
2024-07-26	2024-08-01	71.00	8642	4963	2024-07-26
2024-08-02	2024-08-08	71.00	8642	4963	2024-08-02
2024-08-09	2024-08-15	71.00	8642	4963	2024-08-09
2024-08-16	2024-08-22	71.00	8642	4963	2024-08-16
2024-08-23	2024-08-29	71.00	8642	4963	2024-08-23
2024-08-30	2024-09-05	71.00	8642	4963	2024-08-30
2024-09-06	2024-09-12	71.00	8642	4963	2024-09-06
2024-09-13	2024-09-19	71.00	8642	4963	2024-09-13
2024-09-20	2024-09-26	71.00	8642	4963	2024-09-20
2024-09-27	2024-10-03	71.00	8642	4963	2024-09-27
2024-10-04	2024-10-10	71.00	8642	4963	2024-10-04
2024-10-11	2024-10-17	71.00	8642	4963	2024-10-11
2024-10-18	2024-10-31	48.00	8642	4963	2024-10-31
2024-11-01	2024-11-07	71.00	8642	4963	2024-10-30
2024-11-08	2024-11-14	71.00	8642	4963	2024-11-09
2024-11-15	2024-11-21	71.00	8642	4963	2024-11-16
2024-11-22	2024-11-28	71.00	8642	4963	2024-11-21
2024-11-29	2024-12-05	71.00	8642	4963	2024-11-26
2024-12-06	2024-12-12	71.00	8642	4963	2024-12-07
2024-12-13	2024-12-19	71.00	8642	4963	2024-12-14
2024-12-20	2024-12-26	71.00	8642	4963	null
2024-12-20	2024-12-26	71.00	8642	4963	null
2024-12-20	2024-12-26	71.00	8642	4963	2024-12-25
2024-12-27	2025-01-02	71.00	8642	4963	2024-12-25