CODEWITHSUNDEEP
sqlsql-servert-sql
Adi
Adi

Reputation: 329

Get month wise YTD

I have the data in the table like below.

Company  period      sales
-------------------------
a        2018-01-01   21
a        2018-02-01   22
a        2018-03-01   23
b        2018-01-01   24 
b        2018-02-01   25
b        2018-03-01   26

My desired output looks like below:

comp  year month  YTD values                      MTD values
---------------------------------------------------------------------------
a     2018 jan   <sum of sales in jan 2018>      <sum of sales in jan 2018>
a     2018 feb   <sum of sales in jan+feb 2018>  <sum of sales in feb 2018>

and so on for further months.

I have tried with the below query :

SELECT Company
    ,month(period)
    ,sum(sales) AS MTD
FROM tablea
WHERE datediff(year, period, getdate()) = 0
GROUP BY month(period)
    ,company
ORDER BY company
    ,month(period)

Here I am getting the value for mtd correct but I also want to include a new column YTD which gives cumulative sum or running total. How can I do that?

Upvotes: 1

Views: 2009

Answers (1)

GMB
GMB

Reputation: 222652

You can use aggregation and window functions:

select
    company,
    year(period) yr,
    month(period) mn,
    sum(sales) mtd,
    sum(sum(sales)) over(partition by company, year(period) order by month(period)) ytd
from tablea
group by 
    company,
    year(period),
    month(period)
order by 
    company,
    year(period),
    month(period)

Demo on DB Fiddlde:

company |   yr | mn | mtd | ytd
:------ | ---: | -: | --: | --:
a       | 2018 |  1 |  21 |  21
a       | 2018 |  2 |  22 |  43
a       | 2018 |  3 |  23 |  66
b       | 2018 |  1 |  24 |  24
b       | 2018 |  2 |  25 |  49
b       | 2018 |  3 |  26 |  75

Upvotes: 2

Related Questions