Date sorting - Add and subtract 1 day to arrange dates

Question

I have date range with start and end date columns with unique key for each date range. I need to sort the date range in order to achieve this by adding or subtracting dates. For example:input is below for one unique key. There are thousands of unique keys with similar or different date ranges.

Start Dt - end Dt    - uniquekey
1/1/2012 - 1/31/2012 - x
1/15/2012 - 2/2/2012 - x
1/1/2013 - 12/31/9999 -x

Expected outcome

1/1/2012 - 1/14/2012 -x
1/15/2012- 1/31/2012 -x
2/1/2012- 2/2/2012 -x
1/1/2013 - 12/31/9999 -x

I subtracted 1 from 1/15/2012 to achieve 1/14/2012. Added 1 to 1/31/2012 to achieve 2/1/2012

Basically the first end Dt should be greater than first start Dt, then next start date should be greater than the first end date and the next 2nd end date should be greater than the second start date and so on.

Answer 1

You can use a window function:

SELECT startdate,
       least(
         enddate,
         lead(startdate)
            OVER (PARTITION BY uniquekey
                  ORDER BY startdate) - 1
       ) AS enddate,
       uniquekey
FROM tab;