Selecting sum of rows, with a twist

SSAS and SSRS
1

Using SQL 2014, creating a stored procedure to feed an SSRS report. Within it I am using a temporary table with data like:
Customer_____Sales____Date
1_____________25_____1/15/2016
1_____________30_____1/23/2016
1_____________20_____2/22/2016
2______________5_____1/8/2016
2_____________20_____1/25/2016
2_____________10_____3/10/2016
3_____________45_____2/20/2016

I want to select from this table a simple result set with one row for each customer and the sum of sales for month to date and year to date. So for date 2/28/16 the result set would be:

Customer_____SalesMTD____Sales YTD
1_____________20____________75
2_____________0_____________25
3_____________45____________45

I am trying to do it within one select statement but am having trouble with limiting the date range and grouping on customer in the same statement. My next idea is to create a temporary table that will just be a list of the customers and join them, but I I'm not sure about that route and think there must be an elegant solution I am missing.
Any ideas?

2

select customer, sum(sales)

where date > start date and date <= end date
group by customer

3

Sorry I had stared at it too long and oversimplified my example and missed what was keeping me from doing it the obvious way. The issue is that I need the results to have multiple columns to give sums for different date ranges. Specifically I need month to date on one column and, year to date on another. I have date parameters with all the beginning and end dates, but get tripped up since the "where" clause is different for the various columns. I have edited the sample tables to show...

4

use window functions

5

I have not ever used those. I will have to do some reading.

6 
SELECT ...
       , SUM(CASE WHEN MyTransactionDate >= @MonthStart AND MyTransactionDate < @MonthEnd
                  THEN MyAmount
                  ELSE 0.0
                  END) AS MonthToDate
       , SUM(CASE WHEN MyTransactionDate >= @YearStart AND MyTransactionDate < @YearEnd
                  THEN MyAmount
                  ELSE 0.0
                  END) AS YearToDate

I would make @MonthEnd and @YearEnd as the day AFTER the end of the period - this is so that if the MyTransactionDate is a DATE and TIME datatype then any time value on the last day will be included

If the end point is "now" then you can just use < GetDate()

1 Like