List all days between two dates in Oracle

Question

I am converting a postgres app to an Oracle app.

I came across this query:

WITH cost AS (SELECT 
    well_schedules.id,
    generate_series(well_schedules.start_date::timestamp, well_schedules.end_date, '1 Day') AS "Date",
    (well_schedules.drilling_engineering_estimate * well_schedules.well_estimated_working_interest)/((well_schedules.end_date - well_schedules.start_date) + 1) AS "Cost Per Day"
FROM 
    well_schedules
    )

SELECT date_trunc('quarter', "Date"), COUNT("Cost Per Day"), id
FROM  cost
GROUP BY  id, date_trunc('quarter', "Date")
ORDER BY  date_trunc('quarter', "Date")

The part I am struggling with is the generate_series line.

That line takes a start_date and end_date and lists all days between those two dates. We need that information to compile per day/week/month/quarter/year reports (or at least we assume we need that info).

Our data looks like this:

well_schedules

| id | start_date   | end_date     | cost |
| 1  | '2015-01-01' | '2015-03-20' | 100  |

We assume cost_per_day is equal across all days, so we'd like to generate a report that lets us look at cost_per_day, cost_per_week, cost_per_month, cost_per_year, and cost_per_quarter. cost_per_week/month/quarter/year is calculated by grouping the days by week/month/quarter/year and summing the associated cost_per_days

Answer 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE well_schedules ( id, start_date, end_date, cost ) AS
SELECT 1 , DATE '2015-01-01', DATE '2015-01-20', 100 FROM DUAL;

Query 1:

SELECT ID,
       COLUMN_VALUE AS Day,
       COST / ( end_date - start_date + 1 ) AS Cost_per_day
FROM   well_schedules,
       TABLE (
         CAST(
           MULTISET(
             SELECT start_date + LEVEL - 1
             FROM   DUAL
             CONNECT BY start_date + LEVEL - 1 <= end_date
           )
           AS SYS.ODCIDATELIST
         )
       )

Results:

| ID |                       DAY | COST_PER_DAY |
|----|---------------------------|--------------|
|  1 | January, 01 2015 00:00:00 |            5 |
|  1 | January, 02 2015 00:00:00 |            5 |
|  1 | January, 03 2015 00:00:00 |            5 |
|  1 | January, 04 2015 00:00:00 |            5 |
|  1 | January, 05 2015 00:00:00 |            5 |
|  1 | January, 06 2015 00:00:00 |            5 |
|  1 | January, 07 2015 00:00:00 |            5 |
|  1 | January, 08 2015 00:00:00 |            5 |
|  1 | January, 09 2015 00:00:00 |            5 |
|  1 | January, 10 2015 00:00:00 |            5 |
|  1 | January, 11 2015 00:00:00 |            5 |
|  1 | January, 12 2015 00:00:00 |            5 |
|  1 | January, 13 2015 00:00:00 |            5 |
|  1 | January, 14 2015 00:00:00 |            5 |
|  1 | January, 15 2015 00:00:00 |            5 |
|  1 | January, 16 2015 00:00:00 |            5 |
|  1 | January, 17 2015 00:00:00 |            5 |
|  1 | January, 18 2015 00:00:00 |            5 |
|  1 | January, 19 2015 00:00:00 |            5 |
|  1 | January, 20 2015 00:00:00 |            5 |

Answer 2

I will suggest the code below that consider the first and last day of the month from two dates:

Example:

Date Initial: 01/10/2014  
Date Final: 12/21/2018  

The code will return:

01/01/2014  
02/01/2014  
03/01/2014  
04/01/2014  
...  
12/28/2018  
12/29/2018  
12/30/2018  
12/31/2018  

The Code:

SELECT
     CAL.DT AS "Date"

    ,TO_NUMBER(TO_CHAR(CAL.DT,'DD')) AS "Day"

    ,TO_NUMBER(TO_CHAR(CAL.DT,'MM')) AS "Month"

    ,TO_NUMBER(TO_CHAR(CAL.DT,'YY')) AS "YearYY"

    ,TO_NUMBER(TO_CHAR(CAL.DT,'YYYY')) AS "YearYYYY"

    ,TO_CHAR(CAL.DT,'day') AS "Description_Day"

    ,TO_CHAR(CAL.DT,'dy') AS "Description_Day_Abrev"

    ,TO_CHAR(CAL.DT,'Month') AS "Description_Month"

    ,TO_CHAR(CAL.DT,'Mon') AS "Description_Month_Abrev"

    ,TO_CHAR(CAL.DT,'dd month yyyy') AS "Date_Text"

FROM (
    SELECT
        (
            TO_DATE(SEQ.MM || SEQ.YYYY, 'MM/YYYY')-1
        ) + SEQ.NUM AS "DT"
    FROM
    (
        SELECT RESULT NUM, 
        TO_CHAR(( -- Minimum Date
            TO_DATE('01/01/2014', 'DD/MM/YYYY')
            ) , 'MM') AS "MM",
        TO_CHAR(( -- Minimum Date
            TO_DATE('01/01/2014', 'DD/MM/YYYY')
            ) , 'YYYY') AS "YYYY"
        FROM
        (
            SELECT ROWNUM RESULT FROM DUAL CONNECT BY LEVEL <= (
                (
                -- Maximum Date 
                LAST_DAY(TO_DATE('31/12/2018', 'DD/MM/YYYY')) -- Always Last Day
                -
                -- Maximum Date
                TRUNC(TO_DATE('01/01/2014', 'DD/MM/YYYY')) -- Always First Day of Month
                ) + 1 -- Because the First Day (RESULT) don't begin at zero
            )
        ) -- How many sequences (RESULT) to generate
    ) SEQ
) CAL
    ;