Solved

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

Posted on 2011-03-21
3
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 41

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

740 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