So I have a table which looks like this:
| ITEM | DATE | STATUS |
|---|---|---|
| A | 02.10.2022 | NEW |
| B | 01.10.2022 | IN PROG |
| A | 04.10.2022 | IN PROG |
| A | 05.10.2022 | DONE |
| B | 16.10.2022 | CANCELEED |
I would like to create extra column "NEW STATUS" which based on Item name and Date would show me the next status for specific item. So the outcome of the table would be something like this:
| ITEM | DATE | STATUS | NEW STATUS |
|---|---|---|---|
| A | 02.10.2022 | NEW | IN PROG (based on status for item A on date 04.10.2022) |
| B | 01.10.2022 | IN PROG | CANCELLED (based on status for item B on date 16.10.2022) |
| A | 04.10.2022 | IN PROG | DONE (based on status for item A on Date 05.10.2022) |
| A | 05.10.2022 | DONE | |
| B | 16.10.2022 | CANCELEED |
I actually do not know how to approach it in SQL.
CodePudding user response：
This is a job for LEAD(). This query (fiddle) ...
SELECT ITEM, STATUS_DATE, STATUS,
LEAD(STATUS_DATE, 1) OVER (PARTITION BY ITEM ORDER BY STATUS_DATE) NEXT_STATUS_DATE,
LEAD(STATUS, 1) OVER (PARTITION BY ITEM ORDER BY STATUS_DATE) NEXT_STATUS
FROM tbl
ORDER BY ITEM, STATUS_DATE;
... gives this result.
ITEM STATUS_DATE STATUS NEXT_STATUS_DATE NEXT_STATUS
A 2022-10-02 NEW 2022-10-04 IN PROG
A 2022-10-04 IN PROG 2022-10-05 DONE
A 2022-10-05 DONE null null
B 2022-10-01 IN PROG 2022-10-16 CANCELLED
B 2022-10-16 CANCELLED null null
You can use SQL string handling to convert those two NEXT columns to the narrative text you mentioned in your question.