MySQL 8.0

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

CREATE TABLE Survey_Table
    (`Incident_ID` bigint, `Survey_sent_date` datetime, `survey_id` int, `response` varchar(1))
;
    
INSERT INTO Survey_Table 
    (`Incident_ID`, `Survey_sent_date`, `survey_id`, `response`)
VALUES
    (7496137492, '2021-11-29 14:56:40', 4231, NULL),
    (7496137492, '2021-12-01 07:30:25', 12534, 'N'),
    (7496137492, '2021-12-01 09:10:32', 94353, 'Y'),
    (7496137492, '2021-12-01 10:02:12', 1453474, 'Y'),
    (7496137492, '2021-12-02 15:31:21', 53425, 'N'),
    (7496137492, '2021-12-09 08:41:43', 52345, 'N'),
    (7496137492, '2021-12-13 11:23:28', 53453, 'Y'),
    (7496137492, '2021-12-22 15:25:48', 5245, 'Y'),
    (7496137492, '2022-01-03 06:55:25', 4613, 'Y'),
    (7496137492, '2022-01-31 14:20:57', 311, 'Y'),
    (7496137492, '2022-02-08 01:40:56', 64123, 'Y')
;

CREATE TABLE Advise_Ring
    (`Incident_ID` bigint, `advise_ring_resolve_date` datetime, `advise_ring_id` int)
;
    
INSERT INTO Advise_Ring
    (`Incident_ID`, `advise_ring_resolve_date`, `advise_ring_id`)
VALUES
    (7496137492, '2021-12-15 13:39:55', 1),
    (7496137492, '2021-12-23 13:19:30', 12),
    (7496137492, '2021-12-28 07:43:12', 93),
    (7496137492, '2022-02-13 12:30:47', 174),
    (7496137492, '2022-02-13 12:52:31', 134),
    (7496137492, '2022-02-13 14:07:33', 139),
    (7496137492, '2022-02-14 17:54:14', 29),
    (7496137492, '2022-02-15 12:07:29', 229),
    (7496137492, '2022-03-09 11:41:20', 9867),
    (7496137492, '2022-03-09 15:50:09', 41),
    (7496137492, '2022-03-09 16:40:46', 42),
    (7496137492, '2022-03-09 16:48:17', 256),
    (7496137492, '2022-03-10 14:06:32', 23),
    (7496137492, '2022-03-11 17:32:35', 53)
;
SELECT 2 AS SortOrder, 'Advise' AS Type, advise_ring_id, advise_ring_resolve_date as TheDate FROM Advise_Ring 
UNION ALL
SELECT 1 AS SortOrder, 'Survey' AS Type, survey_id, Survey_sent_date FROM Survey_Table 
ORDER BY TheDate, SortOrder


SortOrder
Type
advise_ring_id
TheDate


1
Survey
4231
2021-11-29 14:56:40

1
Survey
12534
2021-12-01 07:30:25

1
Survey
94353
2021-12-01 09:10:32

1
Survey
1453474
2021-12-01 10:02:12

1
Survey
53425
2021-12-02 15:31:21

1
Survey
52345
2021-12-09 08:41:43

1
Survey
53453
2021-12-13 11:23:28

2
Advise
1
2021-12-15 13:39:55

1
Survey
5245
2021-12-22 15:25:48

2
Advise
12
2021-12-23 13:19:30

2
Advise
93
2021-12-28 07:43:12

1
Survey
4613
2022-01-03 06:55:25

1
Survey
311
2022-01-31 14:20:57

1
Survey
64123
2022-02-08 01:40:56

2
Advise
174
2022-02-13 12:30:47

2
Advise
134
2022-02-13 12:52:31

2
Advise
139
2022-02-13 14:07:33

2
Advise
29
2022-02-14 17:54:14

2
Advise
229
2022-02-15 12:07:29

2
Advise
9867
2022-03-09 11:41:20

2
Advise
41
2022-03-09 15:50:09

2
Advise
42
2022-03-09 16:40:46

2
Advise
256
2022-03-09 16:48:17

2
Advise
23
2022-03-10 14:06:32

2
Advise
53
2022-03-11 17:32:35


WITH surveys AS (
   SELECT *
          , LAG(Survey_Sent_Date, 1) OVER( 
              PARTITION BY Incident_Id ORDER BY Survey_Sent_Date
          ) AS Prev_Survey_Sent_Date
   FROM   Survey_Table
)
SELECT t.Incident_ID
       , t.Survey_sent_date
       , t.survey_id
       , t.response
       , t.advise_ring_id 
FROM (
       SELECT s.Incident_ID
             , s.Survey_sent_date
             , s.survey_id
             , s.response
             , ar.advise_ring_id
             , ar.advise_ring_resolve_date
             , ROW_NUMBER() OVER(
                PARTITION BY s.survey_id 
                ORDER BY ar.advise_ring_resolve_date, ar.advise_ring_id
             ) AS advise_row_num
       FROM   surveys s INNER JOIN Advise_Ring ar 
                ON s.Incident_ID = ar.Incident_ID
                AND s.Prev_Survey_Sent_Date < ar.advise_ring_resolve_date
                AND s.Survey_Sent_Date > ar.advise_ring_resolve_date
     ) t
WHERE  t.advise_row_num = 1
ORDER BY t.Survey_Sent_Date         

;


Incident_ID
Survey_sent_date
survey_id
response
advise_ring_id


7496137492
2021-12-22 15:25:48
5245
Y
1

7496137492
2022-01-03 06:55:25
4613
Y
12

SortOrder	Type	advise_ring_id	TheDate
1	Survey	4231	2021-11-29 14:56:40
1	Survey	12534	2021-12-01 07:30:25
1	Survey	94353	2021-12-01 09:10:32
1	Survey	1453474	2021-12-01 10:02:12
1	Survey	53425	2021-12-02 15:31:21
1	Survey	52345	2021-12-09 08:41:43
1	Survey	53453	2021-12-13 11:23:28
2	Advise	1	2021-12-15 13:39:55
1	Survey	5245	2021-12-22 15:25:48
2	Advise	12	2021-12-23 13:19:30
2	Advise	93	2021-12-28 07:43:12
1	Survey	4613	2022-01-03 06:55:25
1	Survey	311	2022-01-31 14:20:57
1	Survey	64123	2022-02-08 01:40:56
2	Advise	174	2022-02-13 12:30:47
2	Advise	134	2022-02-13 12:52:31
2	Advise	139	2022-02-13 14:07:33
2	Advise	29	2022-02-14 17:54:14
2	Advise	229	2022-02-15 12:07:29
2	Advise	9867	2022-03-09 11:41:20
2	Advise	41	2022-03-09 15:50:09
2	Advise	42	2022-03-09 16:40:46
2	Advise	256	2022-03-09 16:48:17
2	Advise	23	2022-03-10 14:06:32
2	Advise	53	2022-03-11 17:32:35

Incident_ID	Survey_sent_date	survey_id	response	advise_ring_id
7496137492	2021-12-22 15:25:48	5245	Y	1
7496137492	2022-01-03 06:55:25	4613	Y	12