sql server, YTD profit, accumulative sum by product and week. Percentage of increate / decrease

fuck_reporting.sql

drop table if exists sales;

create table sales (
	prod_id		int,
	month_end	varchar(10),
	amount		int
);
-- need to paddle zero if no sales, but we fill manually here
insert into sales values 
 (1, '2017-01-31', 100), (1, '2017-02-28', 300),
 (2, '2017-01-31', 200), (2, '2017-02-28', 900), (2, '2017-03-31', 1000),
 (3, '2017-02-28', 300), (3, '2017-03-31', 600), (3, '2017-04-30', 700),
 (4, '2017-02-28', 400), (4, '2017-03-31', 800);

select month(sales.month_end) from sales;
select * from sales;
-- ytd, +/-, this month

select 
	month(s.month_end) as 'month',
	sum(s.amount)	   as 'monthly sales',
	(
		select sum(s.amount) from sales s
		where s.month_end between '2017-01-01' and s.month_end
	) as 'YTD'
from sales s
group by month(s.month_end);

-- MAIN REPORTING FUNCTION
select 
	pm.ProductId,
	pm.SalesMonth,
	pm.LastSales,
	pm.CurSales,
	IIF(pm.LastSales = 0, 100.0,
    	    round( cast((sum(pm.CurSales) - sum(pm.LastSales)) * 100.0/ sum(pm.LastSales) as float), 2)) as 'Percentage +/-',
	sum(pm.CurSales) over (Partition by pm.ProductId ORDER BY pm.ProductId, pm.SalesMonth) as 'YTD'
from 
(
	-- product don't have to have sales number each month.
	select 
		s.prod_id			AS ProductId,
		month(s.month_end)		AS SalesMonth, 
		s.amount			AS CurSales,
		LAG(s.amount, 1, 0) OVER (Partition by s.prod_id ORDER BY s.prod_id, month(s.month_end)) AS LastSales
	from sales s
) pm --product month
group by pm.ProductId, pm.SalesMonth, pm.CurSales, pm.LastSales
order by pm.ProductId;


select sum(s.amount) 
from sales s
where month(s.month_end) between 1 and 3;

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

http://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum#

https://dba.stackexchange.com/questions/87284/building-year-to-date-aggregations-using-sql