科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

    最新消息：雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客
    你的位置： 首页>programmer>sql - Consolidate data based on date range - Stack Overflow

    sql - Consolidate data based on date range - Stack Overflow

    programmeradmin0浏览0评论

    We're working on migrating data from a legacy application and have a requirement to get all the consecutive SESSION START_DATE & END_DATE. For the SESSION_ID - 8642, SESSION_TYPE - 3256, based on the requirement using SQL Server query for the version 2014 get corresponding records of START_DATE when the date range has gaps when the date range is consecutive and there are multiple records with the same for instance START_DATE '2022-02-17' has 2 records need to get the corresponding records with the latest SESSION_ENTER_DATE. Same with START_DATE '2022-06-02' and '2024-01-02'.

    This is the result set I get:

    But, per my requirement the below is what I need to achieve.

    From the above resultset, I need to exclude START_DATE '2022-03-03' as it would fall in between START_DATE '2022-02-17' & END_DATE '2022-05-12' also START_DATE '2022-06-16' as it would fall in between START_DATE '2022-06-02' & END_DATE '2022-07-27'. DDL and data and query is below.

    Query:

    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');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('4/27/2016', '5/10/2016', '32.53', '7415', '3256', '5/3/2016');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('5/11/2016', '5/24/2016', '32.53', '7415', '3256', '5/13/2016');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('11/11/2017', '12/8/2017', '43.59', '7415', '4963', '12/5/2017');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('12/9/2017', '1/5/2018', '43.59', '7415', '4963', '1/3/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('1/6/2018', '2/2/2018', '43.59', '7415', '4963', '1/12/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('2/3/2018', '3/2/2018', '43.59', '7415', '4963', '2/16/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('3/31/2018', '4/27/2018', '43.59', '7415', '4963', '4/17/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('6/23/2018', '7/20/2018', '43.59', '7415', '4963', '7/5/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('7/21/2018', '8/17/2018', '43.59', '7415', '4963', '8/7/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('8/18/2018', '9/14/2018', '43.59', '7415', '4963', '9/10/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('9/15/2018', '10/12/2018', '43.59', '7415', '4963', '9/27/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('10/13/2018', '11/9/2018', '43.59', '7415', '4963', '10/26/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('12/9/2018', '12/28/2018', '37.02', '7415', '4963', '11/29/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('12/29/2018', '12/29/2018', '63.6', '7415', '4963', '1/2/2019');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('2/17/2022', '3/2/2022', '48.58', '8642', '3256', '2/25/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('2/17/2022', '5/12/2022', '29.27', '8642', '3256', '7/27/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('3/3/2022', '3/9/2022', '46.6', '8642', '3256', '3/7/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('3/10/2022', '3/16/2022', '46.6', '8642', '3256', '3/14/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('3/17/2022', '3/23/2022', '46.6', '8642', '3256', '3/21/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('3/24/2022', '3/30/2022', '46.6', '8642', '3256', '3/28/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('3/31/2022', '4/6/2022', '46.6', '8642', '3256', '4/4/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/7/2022', '4/13/2022', '46.6', '8642', '3256', '4/11/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/14/2022', '4/20/2022', '46.6', '8642', '3256', '4/18/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/21/2022', '4/27/2022', '46.6', '8642', '3256', '4/25/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/28/2022', '5/4/2022', '46.6', '8642', '3256', '5/10/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/5/2022', '5/11/2022', '46.6', '8642', '3256', '5/10/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/12/2022', '5/12/2022', '109.8', '8642', '3256', '5/13/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/2/2022', '6/15/2022', '85.2', '8642', '3256', '6/17/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/2/2022', '7/27/2022', '48.72', '8642', '3256', '7/27/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/16/2022', '6/22/2022', '46.6', '8642', '3256', '6/17/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/23/2022', '6/29/2022', '46.6', '8642', '3256', '6/27/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/30/2022', '7/6/2022', '46.6', '8642', '3256', '7/4/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('7/7/2022', '7/13/2022', '46.6', '8642', '3256', '7/11/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('7/14/2022', '7/20/2022', '46.6', '8642', '3256', '7/18/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('7/21/2022', '7/27/2022', '46.6', '8642', '3256', '7/25/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('7/28/2022', '8/3/2022', '61.19', '8642', '3256', '8/1/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/4/2022', '8/10/2022', '61.19', '8642', '3256', '8/8/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/11/2022', '8/17/2022', '61.19', '8642', '3256', '8/15/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/18/2022', '8/24/2022', '61.19', '8642', '3256', '8/22/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/25/2022', '8/31/2022', '61.19', '8642', '3256', '8/29/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('9/1/2022', '9/7/2022', '61.19', '8642', '3256', '9/5/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/19/2023', '4/25/2023', '61.19', '8642', '3256', '4/19/2023');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/26/2023', '5/2/2023', '61.19', '8642', '3256', '4/24/2023');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/3/2023', '5/9/2023', '61.19', '8642', '3256', '5/1/2023');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/10/2023', '5/16/2023', '61.19', '8642', '3256', '5/8/2023');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/17/2023', '5/23/2023', '61.19', '8642', '3256', '5/15/2023');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/24/2023', '5/30/2023', '61.19', '8642', '3256', '5/22/2023');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/31/2023', '6/6/2023', '61.19', '8642', '3256', '5/23/2023');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/7/2023', '6/13/2023', '61.19', '8642', '3256', '6/2/2023');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('1/2/2024', '1/8/2024', '17.48', '8642', '3256', '1/3/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('1/2/2024', '1/7/2024', '34.97', '8642', '3256', '1/19/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('2/22/2024', '4/11/2024', '18', '8642', '4963', '3/29/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/12/2024', '4/18/2024', '71', '8642', '4963', '4/13/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/19/2024', '4/25/2024', '71', '8642', '4963', '4/19/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/26/2024', '5/2/2024', '71', '8642', '4963', '4/27/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/3/2024', '5/9/2024', '71', '8642', '4963', '5/6/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/10/2024', '5/16/2024', '71', '8642', '4963', '5/10/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/17/2024', '5/23/2024', '71', '8642', '4963', '5/20/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/24/2024', '5/30/2024', '71', '8642', '4963', '5/24/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/31/2024', '6/6/2024', '71', '8642', '4963', '6/3/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/7/2024', '6/13/2024', '71', '8642', '4963', '6/7/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/14/2024', '6/20/2024', '71', '8642', '4963', '6/14/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/21/2024', '6/27/2024', '71', '8642', '4963', '6/21/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/28/2024', '7/4/2024', '71', '8642', '4963', '6/28/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('7/5/2024', '7/11/2024', '71', '8642', '4963', '7/5/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('7/12/2024', '7/18/2024', '71', '8642', '4963', '7/12/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('7/19/2024', '7/25/2024', '71', '8642', '4963', '7/19/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('7/26/2024', '8/1/2024', '71', '8642', '4963', '7/26/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/2/2024', '8/8/2024', '71', '8642', '4963', '8/2/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/9/2024', '8/15/2024', '71', '8642', '4963', '8/9/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/16/2024', '8/22/2024', '71', '8642', '4963', '8/16/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/23/2024', '8/29/2024', '71', '8642', '4963', '8/23/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/30/2024', '9/5/2024', '71', '8642', '4963', '8/30/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('9/6/2024', '9/12/2024', '71', '8642', '4963', '9/6/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('9/13/2024', '9/19/2024', '71', '8642', '4963', '9/13/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('9/20/2024', '9/26/2024', '71', '8642', '4963', '9/20/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('9/27/2024', '10/3/2024', '71', '8642', '4963', '9/27/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('10/4/2024', '10/10/2024', '71', '8642', '4963', '10/4/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('10/11/2024', '10/17/2024', '71', '8642', '4963', '10/11/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('10/18/2024', '10/31/2024', '48', '8642', '4963', '10/31/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('11/1/2024', '11/7/2024', '71', '8642', '4963', '10/30/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('11/8/2024', '11/14/2024', '71', '8642', '4963', '11/9/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('11/15/2024', '11/21/2024', '71', '8642', '4963', '11/16/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('11/22/2024', '11/28/2024', '71', '8642', '4963', '11/21/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('11/29/2024', '12/5/2024', '71', '8642', '4963', '11/26/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('12/6/2024', '12/12/2024', '71', '8642', '4963', '12/7/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('12/13/2024', '12/19/2024', '71', '8642', '4963', '12/14/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('12/20/2024', '12/26/2024', '71', '8642', '4963', NULL);
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('12/20/2024', '12/26/2024', '71', '8642', '4963', NULL);
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('12/20/2024', '12/26/2024', '71', '8642', '3256', '12/25/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('12/20/2024', '12/26/2024', '71', '8642', '4963', '12/25/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('12/27/2024', '1/2/2025', '71', '8642', '4963', '12/25/2024');


SELECT * FROM SESSION

;WITH RankedSessions AS 
(
    SELECT 
        START_DATE,
        END_DATE,
        PAID,
        SESSION_ID,
        SESSION_TYPE,
        SESSION_ENTER_DATE,
        ROW_NUMBER() OVER (
            PARTITION BY SESSION_ID, SESSION_TYPE, START_DATE 
            ORDER BY SESSION_ENTER_DATE DESC
        ) AS rn
    FROM 
        [SESSION]
),
FilteredSessions AS 
(
    SELECT 
        START_DATE,
        END_DATE,
        PAID,
        SESSION_ID,
        SESSION_TYPE,
        SESSION_ENTER_DATE
    FROM 
        RankedSessions
    WHERE 
        rn = 1
),
ConsecutiveSessions AS 
(
    SELECT 
        START_DATE,
        END_DATE,
        PAID,
        SESSION_ID,
        SESSION_TYPE,
        SESSION_ENTER_DATE,
        LAG(END_DATE) OVER (PARTITION BY SESSION_ID, SESSION_TYPE ORDER BY START_DATE) AS Prev_END_DATE
    FROM 
        FilteredSessions
)
SELECT 
    START_DATE,
    END_DATE,
    PAID,
    SESSION_ID,
    SESSION_TYPE,
    SESSION_ENTER_DATE
FROM 
    ConsecutiveSessions
WHERE 
    Prev_END_DATE IS NULL 
    OR DATEADD(DAY, 1, Prev_END_DATE) <> START_DATE
ORDER BY 
    SESSION_ID, SESSION_TYPE, START_DATE;

    Expected output: check for consecutive date range if yes then get the first START_DATE and the corresponding END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE. If there's multiple START_DATE for instance START_DATE '2022-02-17' & END_DATE '2022-05-12' get the latest SESSION_ENTER_DATE '2022-07-27' and its corresponding records. Need to exclude START_DATE '2022-03-03' END_DATE '2022-03-09' as it would fall in between START_DATE '2022-02-17' & END_DATE '2022-05-12'

    START_DATE END_DATE PAID SESSION_ID SESSION_TYPE SESSION_ENTER_DATE
    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

    We're working on migrating data from a legacy application and have a requirement to get all the consecutive SESSION START_DATE & END_DATE. For the SESSION_ID - 8642, SESSION_TYPE - 3256, based on the requirement using SQL Server query for the version 2014 get corresponding records of START_DATE when the date range has gaps when the date range is consecutive and there are multiple records with the same for instance START_DATE '2022-02-17' has 2 records need to get the corresponding records with the latest SESSION_ENTER_DATE. Same with START_DATE '2022-06-02' and '2024-01-02'.

    This is the result set I get:

    But, per my requirement the below is what I need to achieve.

    From the above resultset, I need to exclude START_DATE '2022-03-03' as it would fall in between START_DATE '2022-02-17' & END_DATE '2022-05-12' also START_DATE '2022-06-16' as it would fall in between START_DATE '2022-06-02' & END_DATE '2022-07-27'. DDL and data and query is below.

    Query:

    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');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('4/27/2016', '5/10/2016', '32.53', '7415', '3256', '5/3/2016');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('5/11/2016', '5/24/2016', '32.53', '7415', '3256', '5/13/2016');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('11/11/2017', '12/8/2017', '43.59', '7415', '4963', '12/5/2017');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('12/9/2017', '1/5/2018', '43.59', '7415', '4963', '1/3/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('1/6/2018', '2/2/2018', '43.59', '7415', '4963', '1/12/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('2/3/2018', '3/2/2018', '43.59', '7415', '4963', '2/16/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('3/31/2018', '4/27/2018', '43.59', '7415', '4963', '4/17/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('6/23/2018', '7/20/2018', '43.59', '7415', '4963', '7/5/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('7/21/2018', '8/17/2018', '43.59', '7415', '4963', '8/7/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('8/18/2018', '9/14/2018', '43.59', '7415', '4963', '9/10/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('9/15/2018', '10/12/2018', '43.59', '7415', '4963', '9/27/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('10/13/2018', '11/9/2018', '43.59', '7415', '4963', '10/26/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) 
VALUES ('12/9/2018', '12/28/2018', '37.02', '7415', '4963', '11/29/2018');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('12/29/2018', '12/29/2018', '63.6', '7415', '4963', '1/2/2019');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('2/17/2022', '3/2/2022', '48.58', '8642', '3256', '2/25/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('2/17/2022', '5/12/2022', '29.27', '8642', '3256', '7/27/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('3/3/2022', '3/9/2022', '46.6', '8642', '3256', '3/7/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('3/10/2022', '3/16/2022', '46.6', '8642', '3256', '3/14/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('3/17/2022', '3/23/2022', '46.6', '8642', '3256', '3/21/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('3/24/2022', '3/30/2022', '46.6', '8642', '3256', '3/28/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('3/31/2022', '4/6/2022', '46.6', '8642', '3256', '4/4/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/7/2022', '4/13/2022', '46.6', '8642', '3256', '4/11/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/14/2022', '4/20/2022', '46.6', '8642', '3256', '4/18/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/21/2022', '4/27/2022', '46.6', '8642', '3256', '4/25/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/28/2022', '5/4/2022', '46.6', '8642', '3256', '5/10/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/5/2022', '5/11/2022', '46.6', '8642', '3256', '5/10/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/12/2022', '5/12/2022', '109.8', '8642', '3256', '5/13/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/2/2022', '6/15/2022', '85.2', '8642', '3256', '6/17/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/2/2022', '7/27/2022', '48.72', '8642', '3256', '7/27/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/16/2022', '6/22/2022', '46.6', '8642', '3256', '6/17/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/23/2022', '6/29/2022', '46.6', '8642', '3256', '6/27/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/30/2022', '7/6/2022', '46.6', '8642', '3256', '7/4/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('7/7/2022', '7/13/2022', '46.6', '8642', '3256', '7/11/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('7/14/2022', '7/20/2022', '46.6', '8642', '3256', '7/18/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('7/21/2022', '7/27/2022', '46.6', '8642', '3256', '7/25/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('7/28/2022', '8/3/2022', '61.19', '8642', '3256', '8/1/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/4/2022', '8/10/2022', '61.19', '8642', '3256', '8/8/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/11/2022', '8/17/2022', '61.19', '8642', '3256', '8/15/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/18/2022', '8/24/2022', '61.19', '8642', '3256', '8/22/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/25/2022', '8/31/2022', '61.19', '8642', '3256', '8/29/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('9/1/2022', '9/7/2022', '61.19', '8642', '3256', '9/5/2022');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/19/2023', '4/25/2023', '61.19', '8642', '3256', '4/19/2023');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/26/2023', '5/2/2023', '61.19', '8642', '3256', '4/24/2023');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/3/2023', '5/9/2023', '61.19', '8642', '3256', '5/1/2023');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/10/2023', '5/16/2023', '61.19', '8642', '3256', '5/8/2023');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/17/2023', '5/23/2023', '61.19', '8642', '3256', '5/15/2023');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/24/2023', '5/30/2023', '61.19', '8642', '3256', '5/22/2023');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/31/2023', '6/6/2023', '61.19', '8642', '3256', '5/23/2023');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/7/2023', '6/13/2023', '61.19', '8642', '3256', '6/2/2023');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('1/2/2024', '1/8/2024', '17.48', '8642', '3256', '1/3/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('1/2/2024', '1/7/2024', '34.97', '8642', '3256', '1/19/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('2/22/2024', '4/11/2024', '18', '8642', '4963', '3/29/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/12/2024', '4/18/2024', '71', '8642', '4963', '4/13/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/19/2024', '4/25/2024', '71', '8642', '4963', '4/19/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('4/26/2024', '5/2/2024', '71', '8642', '4963', '4/27/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/3/2024', '5/9/2024', '71', '8642', '4963', '5/6/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/10/2024', '5/16/2024', '71', '8642', '4963', '5/10/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/17/2024', '5/23/2024', '71', '8642', '4963', '5/20/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/24/2024', '5/30/2024', '71', '8642', '4963', '5/24/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('5/31/2024', '6/6/2024', '71', '8642', '4963', '6/3/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/7/2024', '6/13/2024', '71', '8642', '4963', '6/7/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/14/2024', '6/20/2024', '71', '8642', '4963', '6/14/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/21/2024', '6/27/2024', '71', '8642', '4963', '6/21/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('6/28/2024', '7/4/2024', '71', '8642', '4963', '6/28/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('7/5/2024', '7/11/2024', '71', '8642', '4963', '7/5/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('7/12/2024', '7/18/2024', '71', '8642', '4963', '7/12/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('7/19/2024', '7/25/2024', '71', '8642', '4963', '7/19/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('7/26/2024', '8/1/2024', '71', '8642', '4963', '7/26/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/2/2024', '8/8/2024', '71', '8642', '4963', '8/2/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/9/2024', '8/15/2024', '71', '8642', '4963', '8/9/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/16/2024', '8/22/2024', '71', '8642', '4963', '8/16/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/23/2024', '8/29/2024', '71', '8642', '4963', '8/23/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('8/30/2024', '9/5/2024', '71', '8642', '4963', '8/30/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('9/6/2024', '9/12/2024', '71', '8642', '4963', '9/6/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('9/13/2024', '9/19/2024', '71', '8642', '4963', '9/13/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('9/20/2024', '9/26/2024', '71', '8642', '4963', '9/20/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('9/27/2024', '10/3/2024', '71', '8642', '4963', '9/27/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('10/4/2024', '10/10/2024', '71', '8642', '4963', '10/4/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('10/11/2024', '10/17/2024', '71', '8642', '4963', '10/11/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('10/18/2024', '10/31/2024', '48', '8642', '4963', '10/31/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('11/1/2024', '11/7/2024', '71', '8642', '4963', '10/30/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('11/8/2024', '11/14/2024', '71', '8642', '4963', '11/9/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('11/15/2024', '11/21/2024', '71', '8642', '4963', '11/16/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('11/22/2024', '11/28/2024', '71', '8642', '4963', '11/21/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('11/29/2024', '12/5/2024', '71', '8642', '4963', '11/26/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('12/6/2024', '12/12/2024', '71', '8642', '4963', '12/7/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('12/13/2024', '12/19/2024', '71', '8642', '4963', '12/14/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('12/20/2024', '12/26/2024', '71', '8642', '4963', NULL);
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('12/20/2024', '12/26/2024', '71', '8642', '4963', NULL);
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('12/20/2024', '12/26/2024', '71', '8642', '3256', '12/25/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('12/20/2024', '12/26/2024', '71', '8642', '4963', '12/25/2024');
INSERT INTO SESSION (START_DATE, END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE) VALUES ('12/27/2024', '1/2/2025', '71', '8642', '4963', '12/25/2024');


SELECT * FROM SESSION

;WITH RankedSessions AS 
(
    SELECT 
        START_DATE,
        END_DATE,
        PAID,
        SESSION_ID,
        SESSION_TYPE,
        SESSION_ENTER_DATE,
        ROW_NUMBER() OVER (
            PARTITION BY SESSION_ID, SESSION_TYPE, START_DATE 
            ORDER BY SESSION_ENTER_DATE DESC
        ) AS rn
    FROM 
        [SESSION]
),
FilteredSessions AS 
(
    SELECT 
        START_DATE,
        END_DATE,
        PAID,
        SESSION_ID,
        SESSION_TYPE,
        SESSION_ENTER_DATE
    FROM 
        RankedSessions
    WHERE 
        rn = 1
),
ConsecutiveSessions AS 
(
    SELECT 
        START_DATE,
        END_DATE,
        PAID,
        SESSION_ID,
        SESSION_TYPE,
        SESSION_ENTER_DATE,
        LAG(END_DATE) OVER (PARTITION BY SESSION_ID, SESSION_TYPE ORDER BY START_DATE) AS Prev_END_DATE
    FROM 
        FilteredSessions
)
SELECT 
    START_DATE,
    END_DATE,
    PAID,
    SESSION_ID,
    SESSION_TYPE,
    SESSION_ENTER_DATE
FROM 
    ConsecutiveSessions
WHERE 
    Prev_END_DATE IS NULL 
    OR DATEADD(DAY, 1, Prev_END_DATE) <> START_DATE
ORDER BY 
    SESSION_ID, SESSION_TYPE, START_DATE;

    Expected output: check for consecutive date range if yes then get the first START_DATE and the corresponding END_DATE, PAID, SESSION_ID, SESSION_TYPE, SESSION_ENTER_DATE. If there's multiple START_DATE for instance START_DATE '2022-02-17' & END_DATE '2022-05-12' get the latest SESSION_ENTER_DATE '2022-07-27' and its corresponding records. Need to exclude START_DATE '2022-03-03' END_DATE '2022-03-09' as it would fall in between START_DATE '2022-02-17' & END_DATE '2022-05-12'

    START_DATE END_DATE PAID SESSION_ID SESSION_TYPE SESSION_ENTER_DATE
    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

    as START_DATE '2022-06-02' & END_DATE '2022-07-27' have the latest SESSION_ENTER_DATE '2022-07-27' so need to get the corresponding rest of the column values.

    START_DATE END_DATE PAID SESSION_ID SESSION_TYPE SESSION_ENTER_DATE
    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

    as START_DATE '2024-01-02' & END_DATE '2024-01-07' have the latest SESSION_ENTER_DATE '2024-01-19' so need to get the corresponding rest of the column values.

    START_DATE END_DATE PAID SESSION_ID SESSION_TYPE SESSION_ENTER_DATE
    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

    Expected output:

    START_DATE END_DATE PAID SESSION_ID SESSION_TYPE SESSION_ENTER_DATE
    2022-02-17 2022-05-12 29.27 8642 3256 2022-07-27
    2022-06-02 2022-07-27 48.72 8642 3256 2022-07-27
    2023-04-19 2023-04-25 61.19 8642 3256 2023-04-19
    2024-01-02 2024-01-07 34.97 8642 3256 2024-01-19
    2024-02-22 2024-04-11 18 8642 4963 2024-03-29
    2016-04-13 2016-04-26 32.53 7415 3256 2016-04-20
    2017-11-11 2017-12-08 43.59 7415 4963 2017-12-05
    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-12-09 2018-12-28 37.02 7415 4963 2018-11-29
    • sql
    • sql-server
    • t-sql
    Share Improve this question edited Feb 2 at 22:41 pbj asked Feb 2 at 18:56 pbjpbj 7191 gold badge9 silver badges20 bronze badges 6
    • 3 As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Commented Feb 2 at 18:58
    • 4 Could you please edit your question to clarify your first paragraph, of requirements? Your explanation is hard to understand. Explain it to your rubber duck and you might get better answers here. Also, when designing queries, radical clarity in requirements is necessary. – O. Jones Commented Feb 2 at 19:32
    • 2 In addition to replacing all images by tables, and clarifying your requirements, I would be glad to understand what looks like a mismatch in your example: sessions 2024-12-20 and 2024-02-22 do not overlap with any other session, so they should be preserved in the result, however they do not appear in your "Expected output": is it a special case (but then you should clearly explain why it is not in the results) or did you simply fet to add them to the expected result (but then you should remove them from the example, as they are correct thus unuseful cases, whose presence misleads us). – Guillaume Outters Commented Feb 2 at 20:23
    • Is there a primary key field? – June7 Commented Feb 2 at 20:29
    • What with the two concurrent sessions starting from 2022-06-02? One goes straight to 2022-07-27, the other one does a 2-weeks first segment then reaches 2022-07-27 week by week, and then there are 1-week segments continuing from 2022-07-28 until 2022-09-07. So shouldn't the end_date of your 2nd "expected output" be 2022-09-07 instead of 2022-07-27? – Guillaume Outters Commented Feb 2 at 22:35
     |  Show 1 more comment

    1 Answer 1

    Reset to default 3

    (note: given all the fuzziness that still reigns about the question, it is difficult to say if the following answer responds adequately to the need. However, the results it produces - from the point of view of a rubber duck still trying to infer (from both the question and common sense) what a "session", an "entry", a "consecutive" exactly imply - are an alternative starting point; and discussing about the differences may at least help refine the need)

    You can trying using a recursive CTE that will aggregate "islands" of consecutive session segments and then elect in each island the newest entered entry.
    This is a bit bruteforce, but will successfully overcome the problem of "interrupted" sequences (a 06-02 - 07-27 entry that breaks the prettily crafted sequence of 1-week segments relaying between 06-02 and 07-27).

    Although an SQL Server 2016-compatible version is easier to read (thanks to the optimizer which allows one pure CTE),
    Here is the SQL Server 2014 version (that relies on intermediate tables, here table variables, to force the optimizer to materialize the laboriously obtained result of the recursive CTE, before addressing the easier parts):

    declare @s table(id int, gid varchar(16), pos int,
    START_DATE DATE,
    END_DATE DATE,
    PAID DECIMAL(18,2),
    SESSION_ID INT,
    SESSION_TYPE INT,
    SESSION_ENTER_DATE DATE);
declare @brotherhood table(id int, island_id int);

-- Give each entry an id, for further ease.
insert into @s
        select
            row_number() over (order by start_date, session_enter_date) id,
            concat(session_id, '.', session_type) gid,
            row_number() over (partition by session_id, session_type order by start_date, session_enter_date) pos,
            *
        from session;

with
    -- Detect if the entry is consecutive (or overlapping) to its predecessor.
    joint as
    (
        select
            id, gid, pos, start_date, end_date,
            case when lag(end_date) over (partition by gid order by pos) >= dateadd(day, -1, start_date) then lag(id) over (partition by gid order by pos) else id end island_id
        from @s
    ),
    -- Now *recursively* try to reach, from each session entry, all the other entries we can go to uniquely by jumping to *consecutive* older segments.
    bros as
    (
        select id, island_id from joint
        union all
        select me.id, previous.island_id
        from bros me join joint previous
        on me.island_id = previous.id and previous.island_id < previous.id
    )
insert into @brotherhood
    -- From all those segments, elect the oldest as the referer.
        select id, min(island_id) island_id
        from bros
        group by id;
with
    -- But in a brotherhood, maybe the last segment was not entered last. 
    last_entry as
    (
        select distinct island_id, first_value(s.id) over (partition by island_id order by session_enter_date desc, end_date desc) last_entry
        from @brotherhood b join @s s on b.id = s.id
    ),
    -- In parallel from the enter date above (1 entry of the brotherhood), compute its full range (over all entries).
    island as
    (
        select island_id, min(start_date) start_date, max(end_date) end_date
        from @brotherhood b join @s s on b.id = s.id
        group by island_id
    )
select
    i.start_date, i.end_date,
    --s.start_date entered_start_date, s.end_date entered_end_date,
    s.paid,
    s.session_id, s.session_type,
    s.session_enter_date
from island i join last_entry l on l.island_id = i.island_id join @s s on s.id = last_entry
  order by session_id desc, id;

    Note that the resulting start_date and end_date are the full range of the aggregated consecutive session segments, instead of the start and end of only the "entered" segment:

    start_date end_date paid session_id session_type session_enter_date
    2022-02-17 2022-05-12 29.27 8642 3256 2022-07-27
    2022-06-02 2022-09-07 61.19 8642 3256 2022-09-05
    2023-04-19 2023-06-13 61.19 8642 3256 2023-06-02
    2024-01-02 2024-01-08 34.97 8642 3256 2024-01-19
    2024-12-20 2024-12-26 71.00 8642 3256 2024-12-25
    2024-02-22 2025-01-02 71.00 8642 4963 2024-12-25
    2016-04-13 2016-05-24 32.53 7415 3256 2016-05-13
    2017-11-11 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-11-09 43.59 7415 4963 2018-10-26
    2018-12-09 2018-12-29 63.60 7415 4963 2019-01-02

    与本文相关的文章

    发布评论

    评论列表(0)

    1. 暂无评论