CODEWITHSUNDEEP
sql-server
Shafro
Shafro

Reputation: 3

Create start and end date from single date column

Input:

Id |Status  |Modified_date
-----------------------------------------
1  |active  |20-10-2016   
1  |removed |09-11-2016    
2  |active  |21-10-2016    
2  |removed |11-01-2017

I would like to add start date and end date as new columns and the output should look like

ID       | status   | start_Date  | end_date
-----------------------------------------
1/1/1900 | active   | 20-10-2016  |9/11/2016             
1/1/1900 | removed  | 9/11/2016   |99-99-9999
1/2/1900 | active   | 21-10-2016  |11/1/2017    
1/2/1900 | removed  | 11/1/2017   |99-99-9999

Please let me know how this is possible

Upvotes: 0

Views: 1765

Answers (1)

gofr1
gofr1

Reputation: 15997

Simple OUTER APPLY should do the thing:

SELECT  c.id,
        c.[status],
        c.[Modified_date] as [start_date],
        COALESCE(t.[Modified_date],'99-99-9999') as end_date --or ISNULL
FROM YourTable c
OUTER APPLY (
    SELECT TOP 1 [Modified_date]
    FROM YourTable 
    WHERE ID = c.ID AND [Modified_date] > c.[Modified_date]
    ) as t

Output:

id          status  start_date end_date
----------- ------- ---------- ----------
1           active  20-10-2016 09-11-2016
1           removed 09-11-2016 99-99-9999
2           active  21-10-2016 11-01-2017
2           removed 11-01-2017 99-99-9999

Or using LEAD (starting from SQL Server 2012):

SELECT  id,
        [status],
        [Modified_date] as [start_date],
        LEAD([Modified_date],1,'99-99-9999') OVER (PARTITION BY Id ORDER BY [Modified_date] DESC) as end_date
FROM YourTable

Upvotes: 1

Related Questions