Create start and end date from single date column

Question

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

I just provided an answer for this exact situation less than an hour ago. https://stackoverflow.com/questions/44437174/merge-values-in-two-rows-in-an-sql-table-appear-in-two-columns-of-one-row/ — Sean Lange, Jun 08 '17 at 14:01
@SeanLange i think it's a little bit different now = ) but has something in common with what You did. — Whencesoever, Jun 08 '17 at 14:03
Rather than all 9's for an end date, you'll be better off using NULL here. The presence of all 9's and the format also makes me wonder if these are varchar columns. If that's the case, you **really** should convert this to DateTime columns. What you have, in the format you're using, will be very difficult to handle correctly in SQL code in terms of comparing dates. — Joel Coehoorn, Jun 08 '17 at 14:04
Also, in the results, is that date value really what you want for the ID? — Joel Coehoorn, Jun 08 '17 at 14:08

gofr1 · Accepted Answer · 2017-06-09T12:06:07.680

1

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

edited Jun 09 '17 at 12:06

answered Jun 08 '17 at 14:13

gofr1

15,741
11
42
52

Thanks a lot for your answer @gofr1 I tried the code, unfortunately it didnt work as I expected. Because, there are more records for an ID than I expected. Below are the inputs Patient ID | Status| Type| Modified_date ----------------------------- 1 |Removed |HHH |08/07/2015 1 |Removed |HHH |08/07/2017 1 |Removed |HHH |08/07/2018 2 |Removed |HHH |08/07/2015 2 |Active |Nurse |08/09/2016 2 |Active |Base |09/09/2016 3 |Removed |HHH |08/06/2017 3 |Removed |HHH |08/07/2017 – Shafro Jun 09 '17 at 09:03
Then you must change your question. :) – gofr1 Jun 09 '17 at 09:10
Check out the answer! LEAD part is working fine and I fixed OUTER APPLY part. – gofr1 Jun 09 '17 at 12:06

Create start and end date from single date column

1 Answers1