SQL gaps and island issue

Raj D 611

Greetings!!!

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'.

User's image

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

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'

User's image

as START_DATE '2022-06-02' & END_DATE '2022-07-27' has latest SESSION_ENTER_DATE '2022-07-27' so need get the corresponding rest of the column values. User's image

as START_DATE '2024-01-02' & END_DATE '2024-01-07' has latest SESSION_ENTER_DATE '2024-01-19' so need get the corresponding rest of the column values. User's image

The output would look like below.

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

Olaf Helper 46,036 Reputation points

2025-01-21T07:35:01.96+00:00

Please post table design as DDL, some sample data as DML statement and the expected result.
Erland Sommarskog 117.5K Reputation points MVP

2025-01-21T22:57:36.5+00:00

Thanks for the sample data and the table definition. But can you fix the formatting of the expected results?

Also, what if the PAID value changes for the same SESSION_ID/SESSION_TYPE for adjacent dates?
LiHongMSFT-4306 30,901 Reputation points

2025-01-22T01:33:13.4633333+00:00

Hi @Raj D

Please clarify what is the main difference between below result and your desired result.
Olaf Helper 46,036 Reputation points

2025-01-22T07:16:28.1633333+00:00

How often do you want to post the same question again and again; I counted 4 times.

Again:

That's really to less on information.

Please post table design as DDL, some sample data as DML statement and the expected result.
Raj D 611 Reputation points

2025-01-22T13:32:07.19+00:00

Hi @Erland Sommarskog @LiHongMSFT-4306 @Olaf Helper I apologize for posting the same question over and over as I was running into page not found. I updated the OP with the information.

1 answer

Erland Sommarskog 117.5K MVP

I changed the first CTE to read:

;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]  A
    WHERE NOT EXISTS (SELECT * 
                      FROM   SESSION B
                      WHERE  A.SESSION_ID = B.SESSION_ID
                        AND  A.SESSION_TYPE = B.SESSION_TYPE
                        AND  A.START_DATE >= B.START_DATE
                        AND  A.END_DATE <= B.END_DATE
                        AND  NOT (A.START_DATE = B.START_DATE AND A.END_DATE = B.END_DATE))
),

This still have one more row than your desired result. But then again, I fail to see why that row should be

Raj D 611 Reputation points

2025-01-23T03:43:15.38+00:00

Hi @Erland Sommarskog Thank you for your response. Also, The record with START_DATE '2024-01-02' should be START_DATE '2024-01-02' & END_DATE '2024-01-07' as the latest SESSION_ENTER_DATE is '2024-01-19' so need to get the corresponding rest of the column values. Modifying the RankedSessions CTE now brings back the below record.

Erland Sommarskog 117.5K MVP

Sorry, I failed to observed the significance of SESSION_START_DATE.

What I did was to add a filter to filter out sessions that are inside other sessions, but what I added the filter where I did it, I changed the conditions for the row_number.

Here is the same thing again, but later in an extra CTE. I'm not sure that this actually correct. I don't have a whole lot of time to spend on this problem for the moment, so I am shooting from the hip.

;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]  A
),
FilteredSessions AS (
    SELECT 
        START_DATE,
        END_DATE,
        PAID,
        SESSION_ID,
        SESSION_TYPE,
        SESSION_ENTER_DATE
    FROM RankedSessions
    WHERE rn = 1
), ExtraCTE AS (
    SELECT 
        START_DATE,
        END_DATE,
        PAID,
        SESSION_ID,
        SESSION_TYPE,
        SESSION_ENTER_DATE
    FROM FilteredSessions A
    WHERE NOT EXISTS (SELECT * 
                      FROM   FilteredSessions B
                      WHERE  A.SESSION_ID = B.SESSION_ID
                        AND  A.SESSION_TYPE = B.SESSION_TYPE
                        AND  A.START_DATE >= B.START_DATE
                        AND  A.END_DATE <= B.END_DATE
                        AND  NOT (A.START_DATE = B.START_DATE AND A.END_DATE = B.END_DATE))
), 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 ExtraCTE
)
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;

Raj D 611 Reputation points

2025-02-13T03:28:42.2233333+00:00

Hi @Erland Sommarskog I'm close but not there yet. I get the START_DATE but still struggling to retrieve the corresponding END_DATE and the rest of the columns. Please find my query.
Erland Sommarskog 117.5K Reputation points MVP

2025-02-13T22:34:30.36+00:00

Unfortunately, I am not able to fully understand the problem. I am sure that you have been staring at this for weeks. I've been staring on it for ten minutes. Thus, your level of understanding is better than mine. I would really need a step-by-step description of the logic. And possibly one that works from an example with less data.

Raj D 611

Hi @Erland Sommarskog Thanks very much for your response. So, if we take the example of SESSION_ID 8642 we need to first start looking at the START_DATE based on the SESSION_TYPE. So, The data starts from 2022-02-17 but it has 2 records with same START_DATE and check if the next records have consecutive dates meaning in this example the START_DATE '2022-02-17' even though has 2 records with END_DATE '2022-03-02' & '2022-05-12' and next START_DATE is '2022-03-03' it has dates all the way until START_DATE '2022-09-01' & END_DATE '2022-09-07' we take the record with the latest SESSION_ENTER_DATE which would be in the output. And the next one starts at START_DATE '2023-04-19' and it would be straight forward as it has no ties on the

START_DATE. We bring back all the corresponding data relevant to that START_DATE.

DATA:

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
2022-03-03	2022-03-09	46.60	8642	3256	2022-03-07

OUTPUT:

2022-02-17	2022-05-12	29.27	8642	3256	2022-07-27
2023-04-19	2023-04-25	61.19	8642	3256	2023-04-19

Erland Sommarskog 117.5K Reputation points MVP

2025-02-15T14:05:57.9266667+00:00

Sorry, but I'm lost. If we take SESSION_ID = 8642 and SESSION_TYPE = 3256, you query produce five rows:

When I look at the source data, I can indeed identify five blocks. The first block looks like this, after I pasted it into Excel and applied strikethrough to rows that should be ignored, because they are covered by a row with a later SESSION_ENTER_DATE_

There is only one row left, and it agrees with the first row in your output.

This is the second block:

And indeed the second row from your query goes from 2022-06-02 to 2022-09-07. PAID is 48.72 and not 61.19, but I don't know what the rules for PAID are.

In the next block there are no superfluous rows:

And indeed, it agrees with the third row from your query which goes from START_DATE of the first row to END_DATE of the last.

The fourth blocks is just two rows:

As I understand it, it is the first row that we want, since SESSION_START_DATE is later. And indeed, this what your query returns.

The last block is a single row:

And this row is the result set.

So I don't understand what is wrong in your query or why you are dissatisfied with the result. Maybe there are other SESSION_ID/SESSION_TYPE where the result is wrong, but I am not going through the entire result on my own. If there are such cases, can you point to them and say which result you want instead?

Share via

SQL gaps and island issue

1 answer

Your answer