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.

1 Answer 1


You can use a window function:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.