Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...This is a very good site. Please keep it running. Thanks and wishing a great health and success for the site and its owners..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Oracle: Oracle release - 9i and earlier Forum

Extract date range from one date columnHelpful Member! 

thread759-1484489
Read More Threads Like This One
amrog (MIS)
2 Jul 08 10:54
I have a table with the following data:

Code Effective_date
A     1/1/2008
A     1/2/2008
A     15/5/2008
B     1/1/2008
B     15/2/2008
C     1/2/2008
C     1/4/2008

I need to get the following output:

Code     Effective_1     Effective_2
A          1/1/2008        31/1/2008
A          1/2/2008        14/5/2008
A          15/5/2008       Null
B          1/1/2008        14/2/2008
B          15/2/2008       Null
C          1/2/2008        31/3/2008
C          1/4/2008        Null

I need this using one single SQL statement without any Packages.

Is it possible?  I need your help.
 
Helpful Member!  SantaMufasa (TechnicalUser)
2 Jul 08 11:00

Quote (Amroq):

I need this using one single SQL statement without any Packages...Is it possible?
Certainly it is possible to do this in a single SQL statement.

It would, however, be helpful for you to disclose a clear, specific algorithm you want us to use to arrive at "Effective_2" rather than my supplying some random DATE beyond "Effective_1" as it appears presently. <grin>

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
A fo ben, bid bont.

Dagon (MIS)
2 Jul 08 11:17
Your problem is perfect for a solution based on lag and lead analytic functions, but I don't know whether they are available in your version of Oracle.

CODE

SELECT Code, effective_date,
lead(effective_date) over (partition by code order by effective_date) - 1
from eff_Dates
SantaMufasa (TechnicalUser)
2 Jul 08 11:46
Sorry, Amrog, for my earlier, hasty comment about your lack of an intuitive algorithm...The European DATE format obscured my properly seeing what is now an obvious algorithm.

Dagon's solution is very well done. Let us know if your version of Oracle supports Oracle Analytics.

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
A fo ben, bid bont.

amrog (MIS)
2 Jul 08 15:46
My oracle database server is version 8. This is part of the Oracle ERP old version 11.0.

I need this SQL to provide a table that I will use it to in determining effective forecast date range. Actually, there is an additional column not shown representing the relevant quantity.
The date format is dd/mm/yyyy

I think my oracle ver 8 does not support this query. Other solutions?
SantaMufasa (TechnicalUser)
2 Jul 08 16:21
If you do not have access to Oracle Analytics, then the solution is a bit messier:

CODE

select * from amrog;

Code EFFECTIVE_DATE
---- --------------
A    01-JAN-08
A    01-FEB-08
A    15-MAY-08
B    01-JAN-08
B    15-FEB-08
C    01-FEB-08
C    01-APR-08

col code_id heading "Code" format a4
col effective_1 format a11
col effective_2 format a11
select b.code_id
      ,to_char(effective_date,'dd/mm/yyyy') effective_1
      ,nvl(to_char(effective_2,'dd/mm/yyyy'),'Null') effective_2
  from (select x.code_id, x.effective_date effective_1, y.effective_date-1 effective_2
          from amrog x
              ,amrog y
         where x.code_id = y.code_id
           and y.effective_date = (select min(effective_date) from amrog
                                       where effective_date > x.effective_date
                                         and code_id = x.code_id)
       ) a
      ,amrog b
 where a.code_id(+) = b.code_id
   and a.effective_1(+) = b.effective_date
/

Code EFFECTIVE_1 EFFECTIVE_2
---- ----------- -----------
A    01/01/2008  31/01/2008
A    01/02/2008  14/05/2008
A    15/05/2008  Null
B    01/01/2008  14/02/2008
B    15/02/2008  Null
C    01/02/2008  31/03/2008
C    01/04/2008  Null
Let us know if this code is satisfactory and understandable.

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
A fo ben, bid bont.

amrog (MIS)
3 Jul 08 2:46
I understand the code and testing the results. Still waiting for the server to finish.
amrog (MIS)
3 Jul 08 6:18
Many thanks. The results are exactly match my needs.
Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Oracle: Oracle release - 9i and earlier

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close