Solved

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

Posted on 2011-03-21
3
270 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

912 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now