How to pivot a date range in SQL Server?

Question

I have a dataset like this:

sID   Key   date 
-----------------------
AAA   123   2009-05-27 
AAA   457   1985-01-01
BBB   890   1900-01-01
BBB   785   2008-09-05

I want to have a pivoted view of the dates, it should look like this :

sID   Key   start_date   end_date
-----------------------------------
AAA   123   2009-05-27   2050-12-30
AAA   457   1985-01-01   2009-05-26

The end date of the second column should not overlap with the start date of the first column. The end date of the first column is just a random date I chose

Nickolay · Accepted Answer · 2018-04-19 11:55:47Z

1

Assuming you're on SQL Server 2012+ and the input table does not have duplicate dates for any given ID, you can use the lead analytic function:

select sID
     , Key
     , start_date
     , lead(dateadd(day, -1, start_date) -- one day before the `start_date`
            , 1, '99990101')             -- from the "next" row (or a special value if this is the last row)
         over (partition by sID          -- grouping rows by sID
               order by start_date asc)  -- "next" row assuming ordering by ascending start_date
       as end_date
from ...

edited Apr 19, 2018 at 11:55

answered Apr 18, 2018 at 21:01

Nickolay

32.5k13 gold badges111 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

koala Over a year ago

what is start_date in the first select?

Nickolay Over a year ago

@koala Sorry, it's the field called date in your first table.

Paul Maxwell Over a year ago

ps: after Key, there is an unwanted comma

Yogesh Sharma · Accepted Answer · 2018-04-19 06:38:23Z

0

I suspect you want lag() function

select sid, Key, date as start_date,
       lag(dateadd(day,-1,[date]), 1, <default_date>) 
                   over(partition by sid order by ?) as end_date
from table t;

? specifies your actual ordering cols.

answered Apr 19, 2018 at 6:38

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

How to pivot a date range in SQL Server?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related