Skip to content
Advertisement

Oracle SQL- Stack dates if they match diagonally

The goal is to stack dates if DT and VAR are same, and END_DATE matches the START_DATE of next highest START_DATE In the example, row 2 and 3 are combined because END_DATE of row 2 matches START_DATE of row 3.

Original

Expected

I have tried the query below but the solution won’t work when there is additional rows.

Advertisement

Answer

This is a gaps-and-islands problem. I think the simplest way is to use lag() and date comparisons to define where a group begins. Then use a cumulative sum to define the groups and aggregate:

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement