Need help to get dates between two date ranges for multiple records in oracle

2
votes

Hi Can you please help me with this query

I have a table like this

     From_date    to_date       price Product
     13-Apr-2012  15-Apr-2012   15    hammer
     1-may-2012   7-May-2012    15    Scredriver

From this table , I should derive a new field calc_date - which should contain the dates between from_date and to_date for every row with price and product data being replicated

Result Set :

   From_date    to_date       calc_date     price     Product
     13-Apr-2012  15-Apr-2012  13-Apr-2012   15          hammer
     13-Apr-2012  15-Apr-2012  14-Apr-2012   15          hammer
     13-Apr-2012  15-Apr-2012  15-Apr-2012   15          hammer
     1-may-2012   5-May-2012    1-may-2012   15        Scredriver 
     1-may-2012   5-May-2012    2-may-2012   15        Scredriver 
     1-may-2012   5-May-2012    3-may-2012   15        Scredriver
     1-may-2012   5-May-2012    4-may-2012   15        Scredriver
     1-may-2012   5-May-2012    5-may-2012   15        Scredriver

Sql's that I have tried doesn't fetch me the needed data (Result Set)

      SELECT from_date,
           TO_DATE,
           from_date + (LEVEL - 1) AS calc_date,
           price,
           product
      FROM (SELECT from_date,
                   TO_DATE,
                   price,
                   product
              FROM details)
CONNECT BY LEVEL <= TO_DATE - (from_date - 1)

Can you please help me with query which gives the desired result set mentioned above

1
sqloracle11ghierarchical-data

1 Answers

4
votes

I would be inclined to build a numbers table that is big enough and then use this:

with numbers as (
      select level - 1 as n
      from dual
      connect by level <= 500
     )
select from_date, to_date, from_date + n.n as calc_date, price, product
from details d join
     numbers n
     on n.n <= to_date - from_date;

You can also do this for each record. I just find this easier to follow.

If you are worried about the range, you can do:

with numbers as (
      select level - 1 as n
      from dual
      connect by level <= (select max(to_date - from_date) + 1 from details)
     )
select from_date, to_date, from_date + n.n as calc_date, price, product
from details d join
     numbers n
     on n.n <= to_date - from_date;