'Assign a value based on previous row to the all rows of the next group in SQL

Need to create a Bingo column to assign value 1 to the next any group of rows (grouped by flight no and plane no) or the same flight no at the current Row that have below conditions :

(following group get values based on previous group condition)

1 - event = 'landed'
2 - event be after the canceled date if any exist
3 - not be the first historical "landed" event for the plane no

CREATE TABLE tab (PlaneNo INT,
                  FlightNo INT,
                  Destination CHAR(9),
                  EventTime TIMESTAMP,
                  Evnet CHAR(9),
                  Canceled DATE);

INSERT INTO tab
VALUES (1111, 101, 'LAX', '2019-10-03T18:10:00', 'landed', NULL),
       (1111, 101, 'LAX', '2019-10-03T18:22:00', 'check2', NULL),
       (1111, 101, 'LAX', '2019-10-03T18:33:00', 'check3', NULL),
       (1111, 102, 'LAX', '2017-12-03T11:10:00', 'check1', NULL),
       (1111, 102, 'LAX', '2017-12-03T11:14:00', 'check5', NULL),
       (1111, 102, 'LAX', '2017-12-03T12:10:00', 'check11', NULL),
       (1111, 102, 'PHX', '2017-12-03T13:28:00', 'check12', NULL),
       (1111, 103, 'PHX', '2020-01-05T15:10:00', 'landed', NULL),
       (1111, 103, 'PHX', '2020-01-05T16:00:00', 'check2', NULL),
       (1111, 103, 'PHX', '2020-01-05T18:23:00', 'check7', NULL),
       (1111, 103, 'PHX', '2020-01-05T19:14:00', 'check9', NULL),
       (1111, 104, 'LAX', '2021-08-03T11:14:00', 'landed', '2021-08-03'),
       (1111, 104, 'LAX', '2021-08-03T18:13:00', 'check2', '2021-08-03'),
       (1111, 104, 'LAX', '2021-08-03T13:17:00', 'check4', '20121-08-03'),
       (1111, 105, 'LAX', '2022-04-03T00:10:00', 'landed', NULL),
       (1111, 105, 'LAX', '2022-04-03T03:10:00', 'check3', NULL),
       (1111, 105, 'LAX', '2022-04-23T05:10:00', 'check6', NULL),
       (1111, 106, 'LAX', '2022-05-04T01:13:00', 'landed', NULL);

SELECT *,
       CASE WHEN LAG(Event) OVER (PARTITION BY FlightNo ORDER BY EventTime) = 'landed'
            AND  LAG(Canceled) OVER (PARTITION BY FlightNo, EventTime) IS NOT NULL THEN
                1
            ELSE
                FlightNo
            END Bingo
FROM tab;

I found a way how to assign to immediate next or previous rows but I don't know how to deploy LAG() or lead() for next or previous groups or partitioned values at a dataset.

I want to see the result as below :

PlaneNo flightNo Destination EventTime Evnet Canceled Bingo
1111 101 LAX 2019-10-03 18:10:00 landed NULL 101
1111 101 LAX 2019-10-03 18:22:00 check2 NULL 101
1111 101 LAX 2019-10-03 18:33:00 check3 NULL 101
1111 102 LAX 2017-12-03 11:10:00 check1 NULL 102
1111 102 LAX 2017-12-03 11:14:00 check5 NULL 102
1111 102 LAX 2017-12-03 12:10:00 check11 NULL 102
1111 102 PHX 2017-12-03 13:28:00 check12 NULL 102
1111 103 PHX 2020-01-03 15:10:00 landed NULL 103
1111 103 PHX 2020-01-03 16:00:00 check2 NULL 103
1111 103 PHX 2020-01-03 18:23:00 check7 NULL 103
1111 103 PHX 2020-01-03 19:14:00 check9 NULL 103
1111 104 LAX 2021-08-03 11:14:00 landed 2021-08-03 104
1111 104 LAX 2021-08-03 18:13:00 check2 2021-08-03 104
1111 104 LAX 2021-08-03 13:17:00 check4 2021-08-03 104
1111 105 LAX 2022-04-03 00:10:00 landed NULL 1
1111 105 LAX 2022-04-03 03:10:00 check3 NULL 1
1111 105 LAX 2022-04-03 05:10:00 check6 NULL 1
1111 106 LAX 2022-05-03 01:13:00 landed NULL 106


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source