Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-03-21
3
Medium Priority
?
310 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 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

618 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