Solved

How do I create date ranges based on a group of transactions?

Posted on 2008-06-25
7
244 Views
Last Modified: 2010-04-21
I have the following table:

PO          EFF_Date
1            01/01/2008
1            02/01/2008
1            03/01/2008
2            04/01/2008
2            05/01/2008

I want to build a table that looks like the following. The beg date will be the EFF_Date and the end date will be the day before the next EFF_Date. The last record of the PO will be "01/01/2010".

PO          Beg_Date             End_Date
1            01/01/2008           01/31/2008
1            02/01/2008           02/28/2008
1            03/01/2008           01/01/2010
2            04/01/2008           04/30/2008
2            05/01/2008           01/01/2010


I know there is a way of joining a table to itself to do this I just do not know how to do it.
0
Comment
Question by:Bob_Sheppard
  • 3
  • 2
  • 2
7 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 21869499
It is very expensive to join a table to itself this way, you are much more efficient doing this in your application logic...... but here goes

select po, Eff_Date as Beg_date, (select top 1 Eff_Date from table where eff_date>Beg_date order by eff_date) as End_Date
0
 

Author Comment

by:Bob_Sheppard
ID: 21869512
What about the last record within the PO.
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 250 total points
ID: 21869548
Oh, that won't work... maybe this:

select po, Eff_Date as Beg_date, coalesce((select top 1 Eff_Date - 1 from table where eff_date>Beg_date order by eff_date),'1/1/2010') as End_Date
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 73

Expert Comment

by:sdstuber
ID: 21869665
SELECT po, eff_date beg_date,
       NVL(LEAD(eff_date) OVER(PARTITION BY po ORDER BY eff_date ASC) - 1,
           TO_DATE('01/01/2010', 'mm/dd/yyyy')
          ) end_date
  FROM yourtable
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21869791
oh, that's oracle syntax.

in version 2005, you should still be able to use the LEAD analytic function.

try this...


SELECT po, eff_date beg_date,
       coalesce(LEAD(eff_date) OVER(PARTITION BY po ORDER BY eff_date ASC) - 1,01/01/2010 ) end_date
  FROM yourtable
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 21869802
oh nevermind,  sorry,  I'm thinking of different analytics (like row_number)
0
 

Author Closing Comment

by:Bob_Sheppard
ID: 31470737
I just needed to add a match to the PO in addition to the EFF Date for this to be correct. The concept was right on though.

Thanks
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

810 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