I have a table that has date range and i want to calculate the days that has been elapsed between 2 date
| workorder | sequence | start date | end date |
|---|---|---|---|
| 123 | 1 | 2024-01-02 | 2024-01-05 |
| 123 | 2 | 2024-02-01 | 2024-02-06 |
| 123 | 3 | 2024-02-10 | 2024-02-11 |
| 200 | 1 | 2024-03-01 | 2024-03-02 |
| 200 | 2 | 2024-03-15 | 2024-03-16 |
Basically, i want to know the date elapsed from the end date of 123 | 1 to the start date of 123 | 2
Im expecting to get output such as below
| workorder | sequence | start date | end date | elapsed day |
|---|---|---|---|---|
| 123 | 1 | 2024-01-02 | 2024-01-05 | 0 |
| 123 | 2 | 2024-02-01 | 2024-02-06 | 27 |
| 123 | 3 | 2024-02-10 | 2024-02-11 | 4 |
| 200 | 1 | 2024-03-01 | 2024-03-02 | 0 |
| 200 | 2 | 2024-03-15 | 2024-03-16 | 13 |
The criteria is based on the workorder number and sequence
Example Calc:
- End Date of 123|1 - Start Date of 123|2 = 27
- End Date of 123|2 - Start Date of 123|3 = 4
- End Date of 200|1 - Start Date of 200|2 = 13
The example of 2 workorder number which are 123 and 200 but the elapsed calculation based on the same work order number and according to its own sequence.
Im lost