Populating Monthly cost to daily records

Question

I have 3 columns (Date, Flag, cost) The date starts from the beginning of the year, the flag is either daily or monthly and the cost.

For daily values it is fine. For monthly values, I would like to Sum the entire monthly flaged values and divide by the number of days in that month. The resulted rate, populate it in the entire month

Date      Flag      Cost
1/1/2014        
1/2/2014  DAILY     10
1/3/2014  DAILY     15
1/4/2014  DAILY     56
1/5/2014  DAILY     22
1/6/2014  DAILY     32
1/7/2014        
1/8/2014  MONTHLY   3500
1/9/2014        
1/10/2014

Result should be

Date        Cost
1/1/2014    112.9032258 
1/2/2014    122.9032258 
1/3/2014    127.9032258 
1/4/2014    168.9032258 
1/5/2014    134.9032258 
1/6/2014    144.9032258 
1/7/2014    112.9032258 
1/8/2014    112.9032258 
1/9/2014    112.9032258 
1/10/2014   112.9032258 
          .
          .
          .
1/30/2014   112.9032258 
1/31/2014   112.9032258

The total number of days in a month or just the number of days which appear in the table and having any cost? — Wernfried Domscheit, Feb 03 '15 at 06:05
Total number of days in that month, not which appear in the table. If January then it's 31. — szakwani, Feb 03 '15 at 06:07
Could you have both daily and monthly values for the same month ? — Sylvain Leroux, Feb 03 '15 at 06:32
The result does not regard monthly values any more after it has split the monthly values to the daily and added them to the current daily cost. For example, 1/2/2014 had a daily cost of 10 and the monthly contribution for that day split is 112.9032258 thus total daily cost is 122.9032258. Hope that answers. — szakwani, Feb 03 '15 at 06:39
@szakwani Is the couple (date, flag) unique ? Or could you have several monthly costs for the same month ? Several daily costs for the same day ? — Sylvain Leroux, Feb 03 '15 at 06:49
@SylvainLeroux Yes the Date and Flag are unique dependently. — szakwani, Feb 03 '15 at 06:57
Create a row generator for dates, outer join it with your table, and compute the cost. See my answer. — Lalit Kumar B, Feb 03 '15 at 07:56

score 1 · Accepted Answer · edited May 23 '17 at 11:59

If I understand it well, this should give you the average per day of the "monthly" values:

SELECT "Cost" / EXTRACT(DAY FROM LAST_DAY("Date")) "cost_per_day",
       LAST_DAY("Date") "month"
FROM T
WHERE "Flag" = 'MONTHLY'

Once you have that, you final query could be written like:

WITH monthly AS (
    SELECT "Cost" / EXTRACT(DAY FROM LAST_DAY("Date")) "cost_per_day",
           LAST_DAY("Date") "month"
    FROM T
    WHERE "Flag" = 'MONTHLY'
)

SELECT T."Date", NVL("Cost",0) + NVL("cost_per_day",0) "cost"
FROM T FULL JOIN monthly ON LAST_DAY(T."Date") = "month"
WHERE T."Flag" = 'DAILY'
ORDER BY T."Date";

See http://sqlfiddle.com/#!4/cea34/14

As about getting "all day in month" this has already been answered several times (oracle sql query to list all the dates of previous month, Generate a range of dates using SQL)

This would result in the average but for one month only, I am looking for a dynamic result which would undertake the coming months. I am also looking to populate in daily basis the result just as the example. — szakwani, Feb 03 '15 at 06:44

score 0 · Answer 2 · answered Feb 03 '15 at 07:37

0

What's about this solution?

SELECT THE_DATE, Flag, COST, 
    CASE Flag
    WHEN 'DAILY' THEN COST
    WHEN 'MONTHLY' THEN
        COST/EXTRACT(DAY FROM LAST_DAY(THE_DATE))
    ELSE NULL
    END AS AVG_COST
FROM THE_TABLE;

answered Feb 03 '15 at 07:37

Wernfried Domscheit

54,457
9
76
110

I would like to populate the split in the respective days of the months. – szakwani Feb 03 '15 at 07:44

score 0 · Answer 3 · answered Feb 03 '15 at 07:56

First let look at the ROW GENERATOR to get the list of all dates for the month including the equal number of buckets for COST divided equally for each day of the month.

I am hard-coding the value "3500/3" just for the ease of demonstration. In your original query, you just need to join with your table and fetch the **MONTHLY" value from table.

SQL> WITH DATA AS
  2    (SELECT to_date('01/01/2014', 'DD/MM/YYYY') date1,
  3      to_date('31/01/2014', 'DD/MM/YYYY') date2
  4    FROM dual
  5    )
  6  SELECT TO_CHAR(date1+level-1, 'DD/MM/YYYY') the_date,
  7    3500/31 AS "cost"
  8  FROM data
  9    CONNECT BY LEVEL <= date2-date1+1
 10  /

THE_DATE         cost
---------- ----------
01/01/2014 112.903226
02/01/2014 112.903226
03/01/2014 112.903226
04/01/2014 112.903226
05/01/2014 112.903226
06/01/2014 112.903226
07/01/2014 112.903226
08/01/2014 112.903226
09/01/2014 112.903226
10/01/2014 112.903226
11/01/2014 112.903226
12/01/2014 112.903226
13/01/2014 112.903226
14/01/2014 112.903226
15/01/2014 112.903226
16/01/2014 112.903226
17/01/2014 112.903226
18/01/2014 112.903226
19/01/2014 112.903226
20/01/2014 112.903226
21/01/2014 112.903226
22/01/2014 112.903226
23/01/2014 112.903226
24/01/2014 112.903226
25/01/2014 112.903226
26/01/2014 112.903226
27/01/2014 112.903226
28/01/2014 112.903226
29/01/2014 112.903226
30/01/2014 112.903226
31/01/2014 112.903226

31 rows selected.

SQL>

All you need to do is, add nvl("cost", 0) to the cost above. You could do an OUTER JOIN with your table and add the nvl("cost", 0) for those rows having a value for "cost".

score 0 · Answer 4 · answered Feb 03 '15 at 08:03

This request will do it only for rows that already exist in the table

SELECT TO_CHAR(D.DAY, 'MM/DD/YYYY') "Date",
       NVL(D.COST, 0) + M.COST / EXTRACT(DAY FROM LAST_DAY(M.DAY)) "Cost"
    FROM SO28292226 D
    JOIN SO28292226 M ON EXTRACT(MONTH FROM M.DAY) = EXTRACT(MONTH FROM D.DAY)
    WHERE NVL(D.FLAG, 'DAILY') = 'DAILY'
        AND M.FLAG = 'MONTHLY';

Populating Monthly cost to daily records

4 Answers4