Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

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

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
Bob_Sheppard
Asked:
Bob_Sheppard
  • 3
  • 2
  • 2
1 Solution
 
mankowitzCommented:
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
 
Bob_SheppardAuthor Commented:
What about the last record within the PO.
0
 
mankowitzCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
oh nevermind,  sorry,  I'm thinking of different analytics (like row_number)
0
 
Bob_SheppardAuthor Commented:
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now