DB2 – Using Running totals to calculate Month To Date Sales

I’m calculating the Month to date sales using DB2 code to compare July 2019 vs July 2018 totals.

It needs to be rolling to compare the current month of data to the same month last year, otherwise I am comparing two different periods of data.

EG if it were static, I’d be comparing mtd July (2019) week 2 sales vs mtd July week 4 (2018).

I need mtd July 2019 week 2 vs mtd July (2018) week 2 mtd Sales.

How can I get MTD Rolling sales in DB2??

I’ve tried a static MTD – Which is ; however, this is not the ideal method. For rolling, I’ve tried a sum() over but this returns a syntax error (-119 & -727).

Sample table

Year |Month | Week | Sales

2018 | July | 1 | 2000

2018 | July | 2 | 1500

2018 | July | 3 | 1000

2018 | July | 4 | 2000

2019 | July | 1 | 1750

2019 | July | 2 | 2100

Expected Results

Year |Month | Week | Sales | Running Total

2018 | July | 1 | 2000 | 2000

2018 | July | 2 | 1500 | 3500

2018 | July | 3 | 1000 | 4500

2018 | July | 4 | 2000 | 6500

2019 | July | 1 | 1750 | 1750

2019 | July | 2 | 2100 | 3850

— Incorrect results gathered from static

Year |Month | Week | Sales | Running Total

2018 | July | 1 | 2000 | 6500

2018 | July | 2 | 1500 | 6500

2018 | July | 3 | 1000 | 6500

2018 | July | 4 | 2000 | 6500

2019 | July | 1 | 1750 | 3850

2019 | July | 2 | 2100 | 3850

Code from Static (works but incorrect for comparing current month):

Select
     a.Year,
     a.Month,
     a.Week,
     sum(a.sales) as weekly
     mtd.mtd_sales as MTD
from sales a 
inner join 
     (Select
          a.Year,
          a.Month,
          sum(a.sales) as mtd_sales
      from sales a
      group by
          a.Year,
          a.Month) MTD on MTD.week = a.week
group by
     a.Year,
     a.Month,
     a.Week,
     mtd.mtd;

I’ve tried using the below code for rolling, but it produces error (SQL State -119 & – 727) which I believe indicates a syntax error.

Code for rolling

Select
  a.Year,
  a.Month,
  a.Week,
  sum(a.sales) over (Partition by week order by 
month) 
from sales a
group by
    a.year,
    a.month,
    a.week;

Answer

Simply run the window function, SUM() OVER(), at original row level and not inside an aggregate query. Additionally, adjust the PARTITION BY and ORDER BY clauses as your groupings should be year and month and week should be the ordering column.

SELECT
  s.Year,
  s.Month,
  s.Week,
  s.Sales,
  SUM(s.sales) over (PARTITION BY s.Year, s.Month ORDER BY s.Week) as running_total
FROM sales s;

dbfiddle demo (using Db2 Developer-C 11.1 version)

Year |Month | Week | Sales

Advertisement

Answer