Extract date range from one date column 1

[amrog]

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.

 

[SantaMufasa]

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>

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

 

[Dagon]

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.

SELECT Code, effective_date,
lead(effective_date) over (partition by code order by effective_date) - 1
from eff_Dates

 

[SantaMufasa]

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.

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

 

[amrog]

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]

If you do not have access to Oracle Analytics, then the solution is a bit messier:

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.

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

 

[amrog]

I understand the code and testing the results. Still waiting for the server to finish.

 

[amrog]

Many thanks. The results are exactly match my needs.