I need to create a Sales Report that shows all years sales per month, and cumulative sales. The database table is simple:
x
Transactions
(
ID INT,
TransactionDate DATETIME,
SalesAmount MONEY
)
I want the results to look similar to ExcelSheet below (I am showing only 2017/2018 amounts, but actual query needs to return results for all available years according to TransactionDate)
Advertisement
Answer
Try it:
With Q
as
(
Select DatePart(yyyy,TransactionDate) 'Year',DatePart(m,TransactionDate) 'Month', sum(SalesAmount) 'Sales'
From Transactions
Group by DatePart(yyyy,TransactionDate),DatePart(m,TransactionDate)
)
Select q.Year,q.Month,q.sales,( Select sum(q1.Sales)
From Q q1
Where q1.Year=q.Year
And q1.Month <= q.Month
) 'Cumulative Sale'
From Q q
Order by q.Year,q.Month