Oracle SQL revenue YTD computation

Question

I want to write an oracle SQL query to compute monthly YTD revenue (cumulative sum) for all possible combinations of the given dimensions. There are also some months where there are no transactions and hence no revenue, in this case the previous month YTD revenue must be displayed for that dimension combination. Given table:

| Month   | site | channel | type | revenue |
| -----   | ---- | ------- | ---- | ------- |
| 2017-02 | abc  |    1    |  A   |   50    |
| 2017-04 | abc  |    2    |  B   |   100   |
| 2018-12 | xyz  |    1    |  A   |   150   |

Sample Desired output:

| Month   | site | channel | type | ytd revenue |
| -----   | ---- | ------- | ---- | ------- |
| 2017-01 | abc  |    1    |  A   |    0    |
| 2017-02 | abc  |    1    |  A   |    50   |
| 2017-03 | abc  |    1    |  A   |    50   |
| 2017-04 | abc  |    1    |  A   |    50   |
| ------  | ---  |    --   |  --  |   ---   |
| 2018-12 | abc  |    1    |  A   |  1000   |
| -----   | --   |   --    |  --  |   ---   |
| 2017-04 | abc  |    2    |  A   |    100  |
| ----    | ---  |    -    |  -   |    --   |
| 2018-12 | abc  |    2    |  A   |    10   |
| ---     | --   |    -    |  -   |    --   |
| 2018-12 | xyz  |    1    |  A   |   150   |

the fiscal year starts in 1st month and ends in 12th month. So the cumulative sum or YTD revenue must be from 1st month to 12th month every year for all dimension combinations as illustrated in the sample output above.

I don't understand the result set. Based on your description, all the cells should have values. — Gordon Linoff, Dec 19 '20 at 18:49

MT0 · Accepted Answer · 2020-12-20T18:36:15.903

Use a PARTITION OUTER JOIN:

SELECT ADD_MONTHS( t.year, c.month - 1 ) AS month,
       t.site,
       t.channel,
       t.type,
       SUM( COALESCE( t.revenue, 0 ) ) OVER (
         PARTITION BY t.site, t.channel, t.type, t.year
         ORDER BY c.month
       ) AS ytd_revenue
FROM   (
         SELECT LEVEL AS month
         FROM   DUAL
         CONNECT BY LEVEL <= 12
       ) c
       LEFT OUTER JOIN (
         SELECT t.*,
                TRUNC( month, 'YY' ) AS year
         FROM   table_name t
       ) t
       PARTITION BY ( site, channel, type, year )
       ON ( c.month = EXTRACT( MONTH FROM t.month ) );

Which, for the sample data:

CREATE TABLE table_name ( Month, site, channel, type, revenue ) AS
SELECT DATE '2017-02-01', 'abc', 1, 'A',  50 FROM DUAL UNION ALL
SELECT DATE '2017-04-01', 'abc', 2, 'B', 100 FROM DUAL UNION ALL
SELECT DATE '2018-12-01', 'xyz', 1, 'A', 150 FROM DUAL;

Outputs:

MONTH               | SITE | CHANNEL | TYPE | YTD_REVENUE
:------------------ | :--- | ------: | :--- | ----------:
2017-01-01 00:00:00 | abc  |       1 | A    |           0
2017-02-01 00:00:00 | abc  |       1 | A    |          50
2017-03-01 00:00:00 | abc  |       1 | A    |          50
2017-04-01 00:00:00 | abc  |       1 | A    |          50
2017-05-01 00:00:00 | abc  |       1 | A    |          50
2017-06-01 00:00:00 | abc  |       1 | A    |          50
2017-07-01 00:00:00 | abc  |       1 | A    |          50
2017-08-01 00:00:00 | abc  |       1 | A    |          50
2017-09-01 00:00:00 | abc  |       1 | A    |          50
2017-10-01 00:00:00 | abc  |       1 | A    |          50
2017-11-01 00:00:00 | abc  |       1 | A    |          50
2017-12-01 00:00:00 | abc  |       1 | A    |          50
2017-01-01 00:00:00 | abc  |       2 | B    |           0
2017-02-01 00:00:00 | abc  |       2 | B    |           0
2017-03-01 00:00:00 | abc  |       2 | B    |           0
2017-04-01 00:00:00 | abc  |       2 | B    |         100
2017-05-01 00:00:00 | abc  |       2 | B    |         100
2017-06-01 00:00:00 | abc  |       2 | B    |         100
2017-07-01 00:00:00 | abc  |       2 | B    |         100
2017-08-01 00:00:00 | abc  |       2 | B    |         100
2017-09-01 00:00:00 | abc  |       2 | B    |         100
2017-10-01 00:00:00 | abc  |       2 | B    |         100
2017-11-01 00:00:00 | abc  |       2 | B    |         100
2017-12-01 00:00:00 | abc  |       2 | B    |         100
2018-01-01 00:00:00 | xyz  |       1 | A    |           0
2018-02-01 00:00:00 | xyz  |       1 | A    |           0
2018-03-01 00:00:00 | xyz  |       1 | A    |           0
2018-04-01 00:00:00 | xyz  |       1 | A    |           0
2018-05-01 00:00:00 | xyz  |       1 | A    |           0
2018-06-01 00:00:00 | xyz  |       1 | A    |           0
2018-07-01 00:00:00 | xyz  |       1 | A    |           0
2018-08-01 00:00:00 | xyz  |       1 | A    |           0
2018-09-01 00:00:00 | xyz  |       1 | A    |           0
2018-10-01 00:00:00 | xyz  |       1 | A    |           0
2018-11-01 00:00:00 | xyz  |       1 | A    |           0
2018-12-01 00:00:00 | xyz  |       1 | A    |         150

Or, if you want the complete date range rather than just each year:

WITH calendar ( month ) AS (
  SELECT ADD_MONTHS( start_month, LEVEL - 1 )
  FROM   (
    SELECT MIN( ADD_MONTHS( TRUNC( ADD_MONTHS( month, -3 ), 'YY' ), 3 ) ) AS start_month,
           ADD_MONTHS( MAX( TRUNC( ADD_MONTHS( month, -3 ), 'YY' ) ), 14 ) AS end_month
    FROM   table_name
  )
  CONNECT BY
          ADD_MONTHS( start_month, LEVEL - 1 ) <= end_month
)
SELECT TO_CHAR( c.month, 'YYYY-MM' ) AS month,
       t.site,
       t.channel,
       t.type,
       SUM( COALESCE( t.revenue, 0 ) ) OVER (
         PARTITION BY t.site, t.channel, t.type, TRUNC( c.month, 'YY' )
         ORDER BY c.month
       ) AS ytd_revenue
FROM   calendar c
       LEFT OUTER JOIN (
         SELECT t.*,
                TRUNC( month, 'YY' ) AS year
         FROM   table_name t
       ) t
       PARTITION BY ( site, channel, type )
       ON ( c.month = t.month )
ORDER BY
       site, channel, type, month;

Which outputs:

MONTH               | SITE | CHANNEL | TYPE | YTD_REVENUE
:------------------ | :--- | ------: | :--- | ----------:
2017-01-01 00:00:00 | abc  |       1 | A    |           0
2017-02-01 00:00:00 | abc  |       1 | A    |          50
2017-03-01 00:00:00 | abc  |       1 | A    |          50
2017-04-01 00:00:00 | abc  |       1 | A    |          50
2017-05-01 00:00:00 | abc  |       1 | A    |          50
2017-06-01 00:00:00 | abc  |       1 | A    |          50
2017-07-01 00:00:00 | abc  |       1 | A    |          50
2017-08-01 00:00:00 | abc  |       1 | A    |          50
2017-09-01 00:00:00 | abc  |       1 | A    |          50
2017-10-01 00:00:00 | abc  |       1 | A    |          50
2017-11-01 00:00:00 | abc  |       1 | A    |          50
2017-12-01 00:00:00 | abc  |       1 | A    |          50
2018-01-01 00:00:00 | abc  |       1 | A    |           0
2018-02-01 00:00:00 | abc  |       1 | A    |           0
2018-03-01 00:00:00 | abc  |       1 | A    |           0
2018-04-01 00:00:00 | abc  |       1 | A    |           0
2018-05-01 00:00:00 | abc  |       1 | A    |           0
2018-06-01 00:00:00 | abc  |       1 | A    |           0
2018-07-01 00:00:00 | abc  |       1 | A    |           0
2018-08-01 00:00:00 | abc  |       1 | A    |           0
2018-09-01 00:00:00 | abc  |       1 | A    |           0
2018-10-01 00:00:00 | abc  |       1 | A    |           0
2018-11-01 00:00:00 | abc  |       1 | A    |           0
2018-12-01 00:00:00 | abc  |       1 | A    |           0
2017-01-01 00:00:00 | abc  |       2 | B    |           0
2017-02-01 00:00:00 | abc  |       2 | B    |           0
2017-03-01 00:00:00 | abc  |       2 | B    |           0
2017-04-01 00:00:00 | abc  |       2 | B    |         100
2017-05-01 00:00:00 | abc  |       2 | B    |         100
2017-06-01 00:00:00 | abc  |       2 | B    |         100
2017-07-01 00:00:00 | abc  |       2 | B    |         100
2017-08-01 00:00:00 | abc  |       2 | B    |         100
2017-09-01 00:00:00 | abc  |       2 | B    |         100
2017-10-01 00:00:00 | abc  |       2 | B    |         100
2017-11-01 00:00:00 | abc  |       2 | B    |         100
2017-12-01 00:00:00 | abc  |       2 | B    |         100
2018-01-01 00:00:00 | abc  |       2 | B    |           0
2018-02-01 00:00:00 | abc  |       2 | B    |           0
2018-03-01 00:00:00 | abc  |       2 | B    |           0
2018-04-01 00:00:00 | abc  |       2 | B    |           0
2018-05-01 00:00:00 | abc  |       2 | B    |           0
2018-06-01 00:00:00 | abc  |       2 | B    |           0
2018-07-01 00:00:00 | abc  |       2 | B    |           0
2018-08-01 00:00:00 | abc  |       2 | B    |           0
2018-09-01 00:00:00 | abc  |       2 | B    |           0
2018-10-01 00:00:00 | abc  |       2 | B    |           0
2018-11-01 00:00:00 | abc  |       2 | B    |           0
2018-12-01 00:00:00 | abc  |       2 | B    |           0
2017-01-01 00:00:00 | xyz  |       1 | A    |           0
2017-02-01 00:00:00 | xyz  |       1 | A    |           0
2017-03-01 00:00:00 | xyz  |       1 | A    |           0
2017-04-01 00:00:00 | xyz  |       1 | A    |           0
2017-05-01 00:00:00 | xyz  |       1 | A    |           0
2017-06-01 00:00:00 | xyz  |       1 | A    |           0
2017-07-01 00:00:00 | xyz  |       1 | A    |           0
2017-08-01 00:00:00 | xyz  |       1 | A    |           0
2017-09-01 00:00:00 | xyz  |       1 | A    |           0
2017-10-01 00:00:00 | xyz  |       1 | A    |           0
2017-11-01 00:00:00 | xyz  |       1 | A    |           0
2017-12-01 00:00:00 | xyz  |       1 | A    |           0
2018-01-01 00:00:00 | xyz  |       1 | A    |           0
2018-02-01 00:00:00 | xyz  |       1 | A    |           0
2018-03-01 00:00:00 | xyz  |       1 | A    |           0
2018-04-01 00:00:00 | xyz  |       1 | A    |           0
2018-05-01 00:00:00 | xyz  |       1 | A    |           0
2018-06-01 00:00:00 | xyz  |       1 | A    |           0
2018-07-01 00:00:00 | xyz  |       1 | A    |           0
2018-08-01 00:00:00 | xyz  |       1 | A    |           0
2018-09-01 00:00:00 | xyz  |       1 | A    |           0
2018-10-01 00:00:00 | xyz  |       1 | A    |           0
2018-11-01 00:00:00 | xyz  |       1 | A    |           0
2018-12-01 00:00:00 | xyz  |       1 | A    |         150

db<>fiddle here

Fiscal Years (April to March):

WITH calendar ( month ) AS (
  SELECT ADD_MONTHS( start_month, LEVEL - 1 )
  FROM   (
    SELECT MIN( TRUNC( ADD_MONTHS( month, -3 ), 'YY' ) ) AS start_month,
           ADD_MONTHS( MAX( TRUNC( ADD_MONTHS( month, -3 ), 'YY' ) ), 11 ) AS end_month
    FROM   table_name
  )
  CONNECT BY
          ADD_MONTHS( start_month, LEVEL - 1 ) <= end_month
)
SELECT TO_CHAR( ADD_MONTHS( c.month, 3 ), 'YYYY-MM' ) AS month,
       t.site,
       t.channel,
       t.type,
       SUM( COALESCE( t.revenue, 0 ) ) OVER (
         PARTITION BY t.site, t.channel, t.type, TRUNC( c.month, 'YY' )
         ORDER BY c.month
       ) AS ytd_revenue
FROM   calendar c
       LEFT OUTER JOIN (
         SELECT ADD_MONTHS( month, -3 ) AS month,
                site,
                channel,
                type,
                revenue,
                TRUNC( ADD_MONTHS( month, -3 ), 'YY' ) AS year
         FROM   table_name t
       ) t
       PARTITION BY ( site, channel, type )
       ON ( c.month = t.month )
ORDER BY
       site, channel, type, month;

db<>fiddle here

fantastic! I tried getting the month column in yyyy-mm format, but couldn't solve it so far — N91, Dec 20 '20 at 15:15
actually the fiscal year is also from april to april but I thought I had that figure by using ltrim(TO_CHAR(add_months(month,-3),'mm-yyyy'),'0') AS month and then in the end adding 3 months to the month column in the final output. now that seems a bit of a pickle — N91, Dec 20 '20 at 15:29
sorry I just checked it does not cumulate the ytd revenue till next year march. its still till december. — N91, Dec 20 '20 at 17:07

Gordon Linoff · Answer 2 · 2020-12-19T19:25:39.410

If I understand correctly, you can use cross join to get all the rows and then left join and a cumulative sum to get the most recent value:

select m.month, sc.site, sc.channel, sc.type,
       sum(revenue) over (partition by sc.site, sc.channel, sc.type, trunc(m.month, 'YYYY') order by m.month) as ytd_revenue
from (select distinct month from t) m cross join
     (select distinct site, channel, type from t) sct left join
     t
     on t.month = m.month and t.site = sct.site and
        t.channel = sc.channel and t.type = sct.type;

This assumes that all months are available in the data. If not, you need to generate the months . . . either with an explicit list or using some sort of generator such as:

with months(month) as (
       select date '2019-01-01' as month
       from dual
       union all
       select month + interval '1' month
       from months
       where month < date '2021-1-01'
     )

what about the missing months? that are not present in the table. — N91, Dec 19 '20 at 19:05

Oracle SQL revenue YTD computation

2 Answers2

Fiscal Years (April to March):