CODEWITHSUNDEEP
sqloracle-database
Beto
Beto

Reputation: 171

Query to obtain start and end dates from table with only start dates

I have a table with dates and values, something like:

START_DATE    VALUE
------------  -----
01-JAN-2015     A
01-MAR-2015     B
01-AUG-2015     (null)
15-AUG-2015     A
01-SEP-2015     C
01-JAN-2016     B
01-JUN-2016     C

Each start_date represents the date when the value changed. I'm trying to obtain an output that includes the end date as the next date (in chronological order) minus one day, that is:

START_DATE    END_DATE    VALUE
----------    ----------  -----
01-JAN-2015   28-FEB-2015   A
01-MAR-2015   31-JUL-2015   B
01-AUG-2015   14-AUG-2015   (null)
15-AUG-2015   31-AUG-2015   A
01-SEP-2015   31-DEC-2015   C
01-JAN-2016   31-MAY-2016   B
01-JUN-2016   (null)        C

Is there a query I can use to obtain the start and end date for each interval?... maybe using hierarchical queries?

Here, an excerpt I'm using during development that can save some time:

with my_table 
          as(
          select to_date('01-JAN-2015') start_date,'A' value from dual
          union
          select to_date('01-MAR-2015') start_date,'B' value from dual
          union
          select to_date('01-AUG-2015') start_date,'' value from dual
          union
          select to_date('15-AUG-2015') start_date,'A' value from dual
          union
          select to_date('01-SEP-2015') start_date,'C' value from dual
          union
          select to_date('01-JAN-2016') start_date,'B' value from dual
          union
          select to_date('01-JUN-2016') start_date,'C' value from dual
          )
          select ...

Upvotes: 0

Views: 112

Answers (2)

Rajendra Kalepu
Rajendra Kalepu

Reputation: 97

Try this

WITH A AS (SELECT ROWNUM AS RN , A.* FROM SALESNEW A)

SELECT X.START_DATE, Y.START_DATE-1 AS END_DATE, X.VALUE FROM A X , A Y WHERE (CASE WHEN X.RN>=1 THEN X.RN+1 END) = Y.RN(+);

Upvotes: -1

user5683823
user5683823

Reputation: 

select start_date, lead(start_date) over (order by start_date) - 1 as end_date, value
from   my_table
;

Upvotes: 2

Related Questions