Solved

Derive date from two (or more) existing dates - Sql Server

Posted on 2011-03-21
3
288 Views
Last Modified: 2012-05-11
I have a situation in which I'm expected to derive end dates for a record from a single field for a variable amount of records...and if the record has no next record then the final record = '2099-12-31'  

For instance this:
Member    Identify_Date  
14             2011-01-01            
14             2011-02-14
14             2011-03-01
Needs to be coded to end up as this
Member    Identify_Date     End_Date  
14             2011-01-01       2011-02-13
14             2011-02-14       2011-02-28
14             2011-03-01       2099-12-31

I've been working on this all day.  Can't figure it out.  Thanks so much for your help.

Julia
0
Comment
Question by:rsmuckles
3 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35185403
I think the first step is to create a query (which I might use as a CTE) that finds the next Identify_Date for each given member's row.  (This will, of course, fail to provide a "next date" for the last row.)  I think SQL-1, below should work for that

-- SQL_1
SELECT  M1.Member
       ,M1.Identify_Date
       ,MIN(M2.Identify_Date) AS End_Date
FROM   yourmembertable M1
INNER JOIN yourmembertable M2
  ON    M1.Member = M2.Member
WHERE  M2.Identify_Date > M1.Identify_Date

Open in new window


Now, what you need to do is LEFT OUTER JOIN the results of that query to the original table and then COALESCE the End_Date with "2099-12-31".

-- SQL_1
SELECT  M1.Member
                 ,M1.Identify_Date
                 ,COALESC(Z.End_Date, '2099-12-31') End_Date
FROM   yourmembertable M1
LEFT OUTER JOIN
(
 SELECT  M1.Member
                  ,M1.Identify_Date
                  ,COALESC(Z.End_Date, '2099-12-31') End_Date
 FROM   yourmembertable M1
 INNER JOIN yourmembertable M2
   ON    M1.Member = M2.Member
 WHERE  M2.Identify_Date > M1.Identify_Date
) Z
ON Z.Member = M1.Member AND
       Z.Identify_Date = M.Identify_Date;

Open in new window

0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 35185463
try this query.
;WITH cte 
     AS (SELECT *, 
                ROW_NUMBER() 
                  OVER(PARTITION BY Member ORDER BY Identify_Date) rn 
           FROM @table) 
SELECT c1.Member, 
       c1.Identify_Date, 
       ISNULL(DATEADD(DAY,-1,c2.Identify_Date),'2099-12-31') End_Date 
  FROM cte c1 
       LEFT JOIN cte c2 
         ON c1.Member = c2.Member 
            AND c1.rn + 1 = c2.rn

Open in new window

Result for your sample data.
DECLARE  @table  TABLE(Member INT, Identify_Date DATE) 

INSERT @table 
VALUES(14,'2011-01-01'), 
      (14,'2011-02-14'), 
      (14,'2011-03-01'); 

;WITH cte 
     AS (SELECT *, 
                ROW_NUMBER() 
                  OVER(PARTITION BY Member ORDER BY Identify_Date) rn 
           FROM @table) 
SELECT c1.Member, 
       c1.Identify_Date, 
       ISNULL(DATEADD(DAY,-1,c2.Identify_Date),'2099-12-31') End_Date 
  FROM cte c1 
       LEFT JOIN cte c2 
         ON c1.Member = c2.Member 
            AND c1.rn + 1 = c2.rn
/*
Member	Identify_Date	End_Date
14	2011-01-01	2011-02-13
14	2011-02-14	2011-02-28
14	2011-03-01	2099-12-31
*/

Open in new window

0
 

Author Comment

by:rsmuckles
ID: 35190481
Thanks so much Sharath.  I'd never heard of CTE so I looked it up and this was the best explanation I could find for those of you who don't know.
http://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/

I really appreciate the help.  

0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question