• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

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

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
rsmuckles
Asked:
rsmuckles
1 Solution
 
8080_DiverCommented:
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
 
SharathData EngineerCommented:
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
 
rsmucklesAuthor Commented:
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now