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