CODEWITHSUNDEEP
sqlsql-server
koala
koala

Reputation: 91

How to pivot a date range in SQL Server?

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

Upvotes: 1

Views: 162

Answers (2)

Nickolay
Nickolay

Reputation: 32081

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 ...

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

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.

Upvotes: 0

Related Questions