I need to be able to create a Trailing Twelve Month report using SQL (PostgreSQL) - essentially a window/rolling 12 month sum that sums up the current month's totals + the previous 11 months for each month.
I have this table:
CREATE TABLE order_test(
order_id text,
sale_date date,
delivery_date date,
customer_id text,
vendor_id text,
order_total float);
with these values:
insert into order_test
values ('1', '2016-06-01', '2016-06-10', '2', '3', 200.10),
('2', '2016-06-02', '2016-06-11', '2', '4', 150.50),
('3', '2016-07-02', '2016-07-11', '5', '4', 100.50),
('4', '2016-07-02', '2016-07-11', '1', '4', 150.50),
('5', '2016-07-02', '2016-07-11', '1', '4', 150.50),
('6', '2016-08-02', '2016-08-11', '6', '4', 300.50),
('7', '2016-08-02', '2016-08-11', '6', '4', 150.50),
('8', '2016-09-02', '2016-09-11', '1', '4', 150.50),
('9', '2016-10-02', '2016-10-11', '1', '4', 150.50),
('10', '2016-11-02', '2016-11-11', '1', '4', 150.50),
('11', '2016-12-02', '2016-12-11', '6', '4', 150.50),
('12', '2017-01-02', '2017-01-11', '7', '4', 150.50),
('13', '2017-01-02', '2017-01-11', '1', '4', 150.50),
('14', '2017-01-02', '2017-01-11', '1', '4', 100.50),
('15', '2017-02-02', '2017-02-11', '1', '4', 150.50),
('16', '2017-02-02', '2017-02-11', '1', '4', 150.50),
('17', '2017-03-02', '2017-03-11', '2', '4', 150.50),
('18', '2017-03-02', '2017-03-11', '2', '4', 150.50),
('19', '2017-04-02', '2017-04-11', '6', '4', 120.50),
('20', '2017-05-02', '2017-05-11', '1', '4', 150.50),
('21', '2017-06-02', '2017-06-11', '2', '4', 150.50),
('22', '2017-06-02', '2017-06-11', '1', '4', 130.50),
('23', '2017-07-02', '2017-07-11', '1', '4', 150.50),
('24', '2017-07-02', '2017-07-11', '5', '4', 200.50),
('25', '2017-08-02', '2017-08-11', '1', '4', 150.50),
('26', '2017-09-02', '2017-09-11', '2', '4', 100.50),
('27', '2017-09-02', '2017-10-11', '1', '4', 150.50);
These are individual sales. For each month, I need the previous 11 months + that month's total (sale month).
I've tried a window calculation like this:
select date_trunc('month', sale_date) as sale_month,
sum(order_total) over w as total_sales
from order_test
where (delivery_date < current_date) and
(sale_date >= (date_trunc('month', current_date) - interval '1 year'))
window w as (Partition by date_trunc('month', sale_date)
order by sale_date
rows between current row and 11 following)
but it's giving me this:
sale_month total_sales
1 01.09.2016 00:00:00 150,5
2 01.10.2016 00:00:00 150,5
3 01.11.2016 00:00:00 150,5
4 01.12.2016 00:00:00 150,5
5 01.01.2017 00:00:00 401,5
6 01.01.2017 00:00:00 251
7 01.01.2017 00:00:00 100,5
8 01.02.2017 00:00:00 301
9 01.02.2017 00:00:00 150,5
10 01.03.2017 00:00:00 301
11 01.03.2017 00:00:00 150,5
12 01.04.2017 00:00:00 120,5
13 01.05.2017 00:00:00 150,5
14 01.06.2017 00:00:00 281
15 01.06.2017 00:00:00 130,5
16 01.07.2017 00:00:00 351
17 01.07.2017 00:00:00 200,5
18 01.08.2017 00:00:00 150,5
19 01.09.2017 00:00:00 100,5
where there should only be one row per month.
In inner query derived table, you need to truncate Sale_Date column to month precision using date_trunc and group by the resulting column to get the Month_total sales and then in outer query, use cumulative window sum function on month_total sales data ordering by Sale_Month to get your desired result as below.
SELECT sale_Month
,month_total
,sum(month_total) OVER (
ORDER BY sale_Month ASC rows BETWEEN 11 preceding
AND CURRENT row
) AS Sum_Series
FROM (
SELECT date_trunc('month', Sale_Date) AS Sale_Month
,sum(Order_Total) AS Month_Total
FROM order_test
GROUP BY 1
ORDER BY 1
) t
Kindly note that AND CURRENT row is optional as cumulative window function includes the current row by default, so the query can be rewritten as below.
SELECT sale_Month
,month_total
,sum(month_total) OVER (
ORDER BY sale_Month ASC rows 11 preceding
) AS Sum_Series
FROM (
SELECT date_trunc('month', Sale_Date) AS Sale_Month
,sum(Order_Total) AS Month_Total
FROM order_test
GROUP BY 1
ORDER BY 1
) t
Result:
sale_month month_total sum_series
----------------------------------------------
2016-06-01T00:00:00Z 350.6 350.6
2016-07-01T00:00:00Z 401.5 752.1
2016-08-01T00:00:00Z 451 1203.1
2016-09-01T00:00:00Z 150.5 1353.6
2016-10-01T00:00:00Z 150.5 1504.1
2016-11-01T00:00:00Z 150.5 1654.6
2016-12-01T00:00:00Z 150.5 1805.1
2017-01-01T00:00:00Z 401.5 2206.6
2017-02-01T00:00:00Z 301 2507.6
2017-03-01T00:00:00Z 301 2808.6
2017-04-01T00:00:00Z 120.5 2929.1
2017-05-01T00:00:00Z 150.5 3079.6
2017-06-01T00:00:00Z 281 3010
2017-07-01T00:00:00Z 351 2959.5
2017-08-01T00:00:00Z 150.5 2659
2017-09-01T00:00:00Z 251 2759.5
You can check the demo here