CODEWITHSUNDEEP
sqloracle-database
bytebiscuit
bytebiscuit

Reputation: 3496

Cumulatively adding up sales from last years closing balance to current years opening balances

I am trying to cumulatively add sales from last years closing balance bringing it forward to first month and continuing like this from month to month. For instance if we have the following data:

select 4 id, 'A' name, 'group1' group, '2015' year, '10' month, 20 sales from dual union all
select 5 id, 'C' name,'group2' group, '2015' year, '12' month, 89 sales  from dual union all
select 13 id,'B' name, 'group2' group, '2016' year, '01' month, 10 sales  from dual union all
select 14 id,'A' name, 'group3' group, '2016' year, '02' month,  8 sales  from dual union all
select 15 id,'B' name, 'group1' group, '2016' year, '02' month,  16 sales  from dual union all
select 16 id,'D' name,'group2' group, '2016' year, '04' month, 15 sales  from dual union all
select 17 id,'D' name,'group4' group, '2016' year, '05' month, 23 sales  from dual union all
select 18 id,'D' name,'group3' group, '2016' year, '06' month, 39 sales  from dual union all
select 19 id,'D' name,'group3' group, '2016' year, '07' month, 45 sales  from dual union all
select 20 id,'D' name,'group3' group, '2016' year, '08' month, 12 sales  from dual union all
select 21 id,'D' name,'group4' group, '2016' year, '09' month, 20 sales  from dual union all
select 22 id,'D' name,'group3' group, '2016' year, '10' month, 4 sales  from dual union all
select 23 id,'D' name,'group3' group, '2016' year, '11' month, 98 sales  from dual union all
select 24 id,'D' name,'group4' group, '2016' year, '12' month, 70 sales  from dual

Note, for Year=2015 the closing balance for that year is month=12 balance which in this case is 89 for group2 and 20 for group1. If we are in 2016, I want the cumulative query to return something like this:

year, month, group, opening_balance, closing_balance
2016   01    group2      89               99               (89+10)
2016   02    group3      0                8                 (0+8)
2016   02    group1      20               36               (20 + 16)
2016   04    group2      99               114              (99 + 15)
2016   05    group4      0                23               (0 + 23 - note group4 has no prior balances)
2016   06    group3      8                47              (8 + 39)
2016   07    group3      47               92              (47 + 45)
and so on

This looks like it needs involving the analytical function sum() over (partition by .... order by ... )

But I haven't figured out the correct way to do this.

Thanks in advance.

Upvotes: 0

Views: 82

Answers (1)

MT0
MT0

Reputation: 168701

Oracle Setup:

CREATE TABLE sales ( id, name, grp, year, month, sales ) AS
  SELECT  4, 'A', 'group1', '2015', '10', 20 FROM DUAL UNION ALL
  SELECT  5, 'C', 'group2', '2015', '12', 89 FROM DUAL UNION ALL
  SELECT 13, 'B', 'group2', '2016', '01', 10 FROM DUAL UNION ALL
  SELECT 14, 'A', 'group3', '2016', '02',  8 FROM DUAL UNION ALL
  SELECT 15, 'B', 'group1', '2016', '02', 16 FROM DUAL UNION ALL
  SELECT 16, 'D', 'group2', '2016', '04', 15 FROM DUAL UNION ALL
  SELECT 17, 'D', 'group4', '2016', '05', 23 FROM DUAL UNION ALL
  SELECT 18, 'D', 'group3', '2016', '06', 39 FROM DUAL UNION ALL
  SELECT 19, 'D', 'group3', '2016', '07', 45 FROM DUAL UNION ALL
  SELECT 20, 'D', 'group3', '2016', '08', 12 FROM DUAL UNION ALL
  SELECT 21, 'D', 'group4', '2016', '09', 20 FROM DUAL UNION ALL
  SELECT 22, 'D', 'group3', '2016', '10',  4 FROM DUAL UNION ALL
  SELECT 23, 'D', 'group3', '2016', '11', 98 FROM DUAL UNION ALL
  SELECT 24, 'D', 'group4', '2016', '12', 70 FROM DUAL;

Query:

SELECT *
FROM   (
  SELECT year,
         month,
         grp,
         COALESCE(
           SUM( sales ) OVER ( PARTITION BY grp
                               ORDER BY dt
                               RANGE BETWEEN UNBOUNDED PRECEDING
                                         AND INTERVAL '1' MONTH PRECEDING
                             ),
           0
         ) AS opening_balance,
         SUM( sales ) OVER ( PARTITION BY grp
                             ORDER BY dt
                             RANGE BETWEEN UNBOUNDED PRECEDING
                                       AND CURRENT ROW
                           ) AS closing_balance
  FROM   (
    SELECT s.*,
           TO_DATE( year || month, 'YYYYMM' ) AS dt
    FROM   sales s
  )
)
WHERE  year = 2016
ORDER BY year, month, grp;

Query - Alternative without RANGE BETWEEN:

SELECT *
FROM   (
  SELECT year,
         month,
         grp,
         SUM( sales ) OVER ( PARTITION BY grp
                             ORDER BY year, month )
           - sales AS opening_balance,
         SUM( sales ) OVER ( PARTITION BY grp
                             ORDER BY year, month
                           ) AS closing_balance
  FROM   sales
)
WHERE  year = 2016
ORDER BY year, month, grp;

Output:

YEAR MO GRP    OPENING_BALANCE CLOSING_BALANCE
---- -- ------ --------------- ---------------
2016 01 group2              89              99
2016 02 group1              20              36
2016 02 group3               0               8
2016 04 group2              99             114
2016 05 group4               0              23
2016 06 group3               8              47
2016 07 group3              47              92
2016 08 group3              92             104
2016 09 group4              23              43
2016 10 group3             104             108
2016 11 group3             108             206
2016 12 group4              43             113

Upvotes: 1

Related Questions