CODEWITHSUNDEEP
sql-server
Sqriousl
Sqriousl

Reputation: 13

Total sales, MTD sales per date

Need to show total sales for each date between Jun 13th 2019 - Jun 27th 2019, along with MTD sales for each date (Jun 1st 2019 - current date).

This is my query:

select date, sum(sales) as 'Daily Sales', 
sum(sales) over (partition by datepart(month, date) order by date ROWS between unbounded preceding and current row) as 'MTD Sales' 
from Orders 
where date between '13/06/2019' and '27/06/2019'
group by date;

Upon adding the where clause, it changes the MTD sales to show the running total starting only from the 13th, when I want the MTD running total to start from the 1st.

This is the output desired:

Date    | Daily Sales | MTD Sales
---------------------------------
13 June | 200         | 700
15 June | 300         | 1000
20 June | 500         | 1500

Upvotes: 1

Views: 591

Answers (1)

Jeff
Jeff

Reputation: 12785

Because the window function calculated after the where clause is evaluated, you have to execute this as two queries. Here's an example:

;with [MTD sales] as (
    select date, sum(sales) as 'Daily Sales', 
    sum(sales) over (partition by datepart(month, date) order by date ROWS between unbounded preceding and current row) as 'MTD Sales' 
    from Orders 
    group by date;
)
select *
from [MTD sales]
where date between '13/06/2019' and '27/06/2019'

This uses a CTE to run calculate the correct to date sum, and then applies the filtering as a second step after the correct calculations have been run.

Upvotes: 2

Related Questions