asked on

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

8080_Diver

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

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rsmuckles

ASKER

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.