CODEWITHSUNDEEP
sqloracle11g
cpwm86
cpwm86

Reputation: 33

Generate dates between to date columns

I am trying to write a function that will populate a temp table. This temp table will be populated by the rows that I have in a table called 'Orders' (Both the temp table and Orders share the same column names). The rows in Orders are Item, Location, Qty, SendDate, and EndDate (SendDate and EndDate are in 'yyyymmdd' format). The difference between SendDate and Enddate will need to be taken and spread the qty out by the difference in the dates. Example: A row with a qty of 200 and the SendDate is 3/16 and the EndDate is 3/20, the function would need to populate the temp table with the rows for 3/16-3/20 with a qty of 40.

Sample Data:
- Item    Location  Qty   SendDate    EndDate
 -Toy     Store     200    20150316    20150320

Desired result: 
 - Item    Location  Qty   SendDate    EndDate
  -Toy     Store     40    20150316    20150320
  -Toy     Store     40    20150317    20150320
  -Toy     Store     40    20150318    20150320
  -Toy     Store     40    20150319    20150320
  -Toy     Store     40    20150320    20150320

I am novice in cursor statements so I am unsure how to attempt this. Thank you in advance.

Upvotes: 2

Views: 314

Answers (2)

Aramillo
Aramillo

Reputation: 3216

You can use CONNECT BY clause:

WITH YOUR_TABLE (Item,Location,Qty,SendDate,EndDate)
     AS (SELECT 'Toy','Store',200,20150316,20150320 FROM DUAL) -- sample data
    SELECT item,
           location,
           qty,
           TO_NUMBER (
              TO_CHAR (TO_DATE (senddate, 'yyyymmdd') + LEVEL - 1, 'yyyymmdd'))
              senddate,
           enddate
      FROM YOUR_TABLE
CONNECT BY LEVEL<=(TO_DATE(enddate,'yyyymmdd') - TO_DATE(senddate,'yyyymmdd')) + 1;

Upvotes: 1

Erik Blessman
Erik Blessman

Reputation: 693

Using a UNION ALL within a common table expression can give you what you are looking for.

I'm unfamiliar with Oracle's date manipulation, but something like this could work:

WITH x ( d ) AS (
    SELECT TO_DATE('2014-12-25', 'yyyy-mm-dd')
    FROM   dual
    UNION ALL
    SELECT d + interval '1' day
    FROM   x
    WHERE  d < TO_DATE('2014-12-31', 'yyyy-mm-dd')
)
SELECT *
FROM   x

Upvotes: 1

Related Questions