I have an SQL table that provides “GGATEVISITNUMBER”, “SUB_TYPE”, and a time context field (date/time).
Every row is unique but a GATEVISITNUMBER can have multiple SUB_TYPEs.
| GATEVISITNUMBER | SUB_TYPE | TIME |
|---|---|---|
| 98553336 | RM | 6:03 am |
| 98553336 | DI | 6:20 am |
What I want to achieve is for every unique GATEVISITNUMBER what was the first sub_type and the sub_type that followed after.
For the above example, GATEVISITNUMBER 98553336 was a RM then DI.
The issue is in SQL when I use lag(), it pulls the previous sub_type even if it is a different GATEVISITNUMBER. So if GATEVISITNUMBER 12345678 only has 1 sub_type, lag() is extracting GATEVISITINUMBER 98553336 sub_type which is incorrect.
The logic I need:
If GATEVISITNUMBER has more than 1 SUB_TYPE, than give me the first sub_type and the sub_type followed after. Else if GATEVISITNUMBER only has 1 entry than the second sub_type field should be null
| GATEVISITNUMBER | SUB_TYPE | SUB_TYPE2 |
|---|---|---|
| 98553336 | RM | DI |
| 12345678 | DI | null |
I tried using LAG and JOIN
Andre Padilla is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.