Hey everyone,
I have a database that has columns like the below table and I want to write a query to have a new column to correct the information in the type column. The logic is if there's a C type record after an L type record and the END time is larger than the L type record and they have the same ID, the type should be updated to L. One important thing that makes this query complicated for me is if there's a second L type record for the same ID, I should compare the next C record to the second L type, nit the first one. For example, sequence 5 should be compared to sequence 4 not the sequence 1.
| ID | Sequence | Type
| End time |
|---|
| 1234A | 1 | L | 5/13/2018 4:00:00 PM |
| 1234A | 2 | C | 5/13/2018 3:00:00 PM |
| 1234A | 3 | C | 5/13/2018 4:30:00 PM |
| 1234A | 4 | L | 5/13/2018 5:00:00 PM |
| 1234A | 5 | C | 5/13/2018 5:45:00 PM |
In summary, the result of the query should be like this:
| D | Sequence | Type
| End time | True Type |
|---|
| 1234A | 1 | I | 5/13/2018 4:00:00 PM | I |
| 1234A | 2 | C | 5/13/2018 3:00:00 PM | C |
| 1234A | 3 | C | 5/13/2018 4:30:00 PM | I |
| 1234A | 4 | I | 5/13/2018 5:00:00 PM | I |
| 1234A | 5 | C | 5/13/2018 5:45:00 PM | I |
Thank you in advance, and I look forward to your guidelines.