SQL Server 2022

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

CREATE TABLE table1 (
    ID INT,
    N INT,
    START_DATE DATE,
    STOP_DATE DATE,
    GROUPS VARCHAR(255)
);


INSERT INTO table1(ID, N, START_DATE, STOP_DATE, GROUPS)
VALUES
    (1, 1, '2023-01-26', '2023-04-13', 0),
    (1, 2, '2023-02-05', '2023-04-13', 0),
    (1, 3, '2023-02-27', '2023-04-13', 0),
    (1, 4, '2023-04-14', '2023-09-13', 1),
    (1, 5, '2023-04-14', '2023-09-13', 1),
    (1, 6, '2023-04-14', '2023-09-13', 1);

SELECT *
FROM table1

CREATE TABLE table2 (
    ID INT,
    EVENT_HAPPENED DATE

);

INSERT INTO table2(ID, EVENT_HAPPENED)
VALUES
    (1, '2023-03-12'),
    (1, '2023-03-18'),
    (1, '2023-04-07'),
    (1, '2023-05-31')

SELECT *
FROM table2




ID
N
START_DATE
STOP_DATE
GROUPS


1
1
2023-01-26
2023-04-13
0

1
2
2023-02-05
2023-04-13
0

1
3
2023-02-27
2023-04-13
0

1
4
2023-04-14
2023-09-13
1

1
5
2023-04-14
2023-09-13
1

1
6
2023-04-14
2023-09-13
1




ID
EVENT_HAPPENED


1
2023-03-12

1
2023-03-18

1
2023-04-07

1
2023-05-31


WITH EventDates AS (
    SELECT
        ID,
        MIN(EVENT_HAPPENED) AS MIN_EVENT_DATE
    FROM
        Table2
    GROUP BY
        ID
),
IntervalDates AS (
    SELECT
        t1.ID,
        t1.N,
        t1.START_DATE,
        t1.STOP_DATE,
        t1.GROUPS,
        ed.MIN_EVENT_DATE AS HAPPENED_FIRST_TIME,
        ROW_NUMBER() OVER (PARTITION BY t1.ID, GROUPS ORDER BY t1.N DESC) AS RN
    FROM
        Table1 t1
    LEFT JOIN
        EventDates ed ON t1.ID = ed.ID 
                      AND ed.MIN_EVENT_DATE BETWEEN t1.START_DATE AND t1.STOP_DATE
)
SELECT *
FROM
    IntervalDates
ORDER BY ID, N


ID
N
START_DATE
STOP_DATE
GROUPS
HAPPENED_FIRST_TIME
RN


1
1
2023-01-26
2023-04-13
0
2023-03-12
3

1
2
2023-02-05
2023-04-13
0
2023-03-12
2

1
3
2023-02-27
2023-04-13
0
2023-03-12
1

1
4
2023-04-14
2023-09-13
1
null
3

1
5
2023-04-14
2023-09-13
1
null
2

1
6
2023-04-14
2023-09-13
1
null
1


WITH EventDates AS (
    SELECT
        ID,
        MIN(EVENT_HAPPENED) AS MIN_EVENT_DATE
    FROM
        Table2
    GROUP BY
        ID
),
IntervalDates AS (
    SELECT
        t1.ID,
        t1.N,
        t1.START_DATE,
        t1.STOP_DATE,
        t1.GROUPS,
        ed.MIN_EVENT_DATE AS HAPPENED_FIRST_TIME,
        ROW_NUMBER() OVER (PARTITION BY t1.ID, GROUPS ORDER BY t1.N DESC) AS RN
    FROM
        Table1 t1
    LEFT JOIN
        EventDates ed ON t1.ID = ed.ID
)
SELECT
    ID,
    N,
    START_DATE,
    STOP_DATE,
    GROUPS,
    CASE
        WHEN HAPPENED_FIRST_TIME <= START_DATE THEN 1
        ELSE 0
    END AS MARKER,
    CASE
        WHEN RN = 1 AND HAPPENED_FIRST_TIME BETWEEN START_DATE AND STOP_DATE THEN 1
        ELSE 0
    END AS HAPPENED_FIRST_TIME,
    CASE
        WHEN RN = 1 AND HAPPENED_FIRST_TIME BETWEEN START_DATE AND STOP_DATE THEN HAPPENED_FIRST_TIME
        ELSE NULL
    END AS HAPPENED_ON
FROM
    IntervalDates
ORDER BY ID, N


ID
N
START_DATE
STOP_DATE
GROUPS
MARKER
HAPPENED_FIRST_TIME
HAPPENED_ON


1
1
2023-01-26
2023-04-13
0
0
0
null

1
2
2023-02-05
2023-04-13
0
0
0
null

1
3
2023-02-27
2023-04-13
0
0
1
2023-03-12

1
4
2023-04-14
2023-09-13
1
1
0
null

1
5
2023-04-14
2023-09-13
1
1
0
null

1
6
2023-04-14
2023-09-13
1
1
0
null

ID	N	START_DATE	STOP_DATE	GROUPS
1	1	2023-01-26	2023-04-13	0
1	2	2023-02-05	2023-04-13	0
1	3	2023-02-27	2023-04-13	0
1	4	2023-04-14	2023-09-13	1
1	5	2023-04-14	2023-09-13	1
1	6	2023-04-14	2023-09-13	1

ID	N	START_DATE	STOP_DATE	GROUPS	HAPPENED_FIRST_TIME	RN
1	1	2023-01-26	2023-04-13	0	2023-03-12	3
1	2	2023-02-05	2023-04-13	0	2023-03-12	2
1	3	2023-02-27	2023-04-13	0	2023-03-12	1
1	4	2023-04-14	2023-09-13	1	null	3
1	5	2023-04-14	2023-09-13	1	null	2
1	6	2023-04-14	2023-09-13	1	null	1