I am trying to create a table in SSRS that has total sales of week to date, month to date and year to date, grouped by product name. My query table has a 'TTPrice' column that has the sales of the product. The week to date total sales is obtained from the total sales of the selected date's week starting from Monday. If the selected date is Monday, then it will only get the sales of that day. If it was Tuesday then it will get the sales of Monday (yesterday) and Tuesday (selected date) and the rest follows. The month to date total sales is obtained from the total sales starting from the first day of the selected date's month until the selected date, for example if the selected date is 2021-06-15 then it will get the sales from the range of 2021-06-01 to 2021-06-15. The year to date is the total sales from the first day of the year to the selected date. Thus, the table will look something like below:
| PH1NAME | PH2NAME | ProductCode | ProductName | Week-To-Date | Month-To-Date | Year-To-Date |
|---|---|---|---|---|---|---|
| FROZEN | FROZEN GOODS | 123 | ICE CREAM | 2.00 | 10.00 | 100.00 |
| FOOD | SNACK | 222 | CHIPS | 3.00 | 20.00 | 150.00 |
| INSTANT | NOODLES | 134 | RAMEN | 0.00 | 10.00 | 90.00 |
All 3 calculated columns may have no value as it all depends on the selected date. Below are my query to get the sales of the product:
Select t.PRDCODE, t.PRDNAME, t.CREATEDATE, t.TTPrice
From [dbo].[TRAN] t
WHERE (t.CREATEDATE >= DATEADD(DD, 1 - DATEPART(DW, @CurrentDate), @CurrentDate) AND t.CREATEDATE <= @CurrentDate) OR (t.CREATEDATE >= DATEADD(month, DATEDIFF(month, 0, @CurrentDate), 0) AND t.CREATEDATE <= @CurrentDate) OR (t.CREATEDATE >= DATEFROMPARTS(YEAR(@CurrentDate), 1, 1) AND t.CREATEDATE <= @CurrentDate)
I have a date/time parameter with the name 'CurrentDate' for the selected date and I tried to find the week, month and year to date total sales by using expressions and selected date with date/time parameter.
Week to date column expression
=IF(Fields!CREATEDATE.Value >= DATEADD("d", 1 - DATEPART("w", Parameters!CurrentDate.Value, FirstDayOfWeek.Monday), Parameters!CurrentDate.Value) AND Fields!CREATEDATE.Value <= Parameters!CurrentDate.Value, Sum(Fields!TTPrice.Value), 0)
Month to date column expression
=IF(Fields!CREATEDATE.Value >= DATESERIAL(YEAR(Parameters!CurrentDate.Value), MONTH(Parameters!CurrentDate.Value), 1) AND Fields!CREATEDATE.Value <= Parameters!CurrentDate.Value, Sum(Fields!TTPrice.Value), 0)
Year to date column expression
=IF(Fields!CREATEDATE.Value >= DATESERIAL(YEAR(Parameters!CurrentDate.Value), 1, 1) AND Fields!CREATEDATE.Value <= Parameters!CurrentDate.Value, Sum(Fields!TTPrice.Value), 0)
After previewing the report, I noticed that the week and month to date columns displayed 0 while only the year to date column has the correct value. The only time the week and month to date columns will have values was when all 3 columns have the same values coincidentally as below:
| PH1NAME | PH2NAME | ProductCode | ProductName | Week-To-Date | Month-To-Date | Year-To-Date |
|---|---|---|---|---|---|---|
| FROZEN | FROZEN GOODS | 123 | ICE CREAM | 0.00 | 0.00 | 100.00 |
| FOOD | SNACK | 222 | CHIPS | 0.00 | 0.00 | 150.00 |
| INSTANT | NOODLES | 134 | RAMEN | 0.00 | 0.00 | 90.00 |
| FOOD | GROCERY | 155 | COOKING OIL | 50.00 | 50.00 | 50.00 |
| FROZEN | SEAFOOD | 175 | LOBSTER | 0.00 | 100.00 | 100.00 |
I have checked through my data and there are indeed sales on the selected date's week and month but the expression only returned 0, ignoring my conditions in the IF statement. I have also tried to generate the total sales through query as below:
Set DATEFIRST 1
Select t.TRANCODE, t.PRDCODE, t.PRDNAME, t1.WeekToDate, t2.MonthToDate, t3.YearToDate
From [dbo].[TRAN] t
LEFT JOIN (Select t.PRDNAME AS PRDNAME, SUM(t.TTPrice) AS WeekToDate From [dbo].[TRAN] t
WHERE t.CREATEDATE >= DATEADD(DD, 1 - DATEPART(DW, @CurrentDate), @CurrentDate) AND t.CREATEDATE <= @CurrentDate
GROUP BY t.PRDNAME) t1
ON t.PRDNAME = t1.PRDNAME
JOIN (Select t.PRDNAME AS PRDNAME, SUM(t.TTPrice) AS MonthToDate From [dbo].[TRAN] t
WHERE t.CREATEDATE >= DATEADD(month, DATEDIFF(month, 0, @CurrentDate), 0) AND t.CREATEDATE <= @CurrentDate
GROUP BY t.PRDNAME) t2
ON t1.PRDNAME = t2.PRDNAME
JOIN (Select t.PRDNAME AS PRDNAME, SUM(t.TTPrice) AS YearToDate From [dbo].[TRAN] t
WHERE t.CREATEDATE >= DATEFROMPARTS(YEAR(@CurrentDate), 1, 1) AND t.CREATEDATE <= @CurrentDate
GROUP BY t.PRDNAME) t3
ON t2.PRDNAME = t3.PRDNAME
The query above will only returned rows that have all 3 To Date columns filled with no null. I want it to returned all the records of the selected date even those that are null or 0 in either week or month or year to date columns. My questions are:
For T-SQL/SQL Server using modulus of 7 is a convenient - and reliable - way of figuring what day of the week any date is. In the datediff function day zero (January 1, 1900) is a Monday, so the following will always return -0 for any Monday, or -1 for any Tuesday and so on.
-datediff(day,0,@CurrentDate) % 7
Using that approach, and cross joining the date ranges to your data:
declare @CurrentDate date = '20210615';
with ranges as (
select
dateadd(day,-datediff(day,0,@CurrentDate) % 7,@CurrentDate) wk_start
, DATEADD(month, DATEDIFF(month, 0, @CurrentDate),0) mn_start
, DATEFROMPARTS(YEAR(@CurrentDate), 1, 1) yr_start
, dateadd(day,1,@CurrentDate) cutoff_dt
)
select
t.prdcode
, sum(case when t.createdate >= r.wk_start then t.TTPrice else 0 end) as this_week
, sum(case when t.createdate >= r.mn_start then t.TTPrice else 0 end) as this_month
, sum(case when t.createdate >= r.yr_start then t.TTPrice else 0 end) as this_year
from [tran] t
cross join ranges r
where t.createdate >= r.yr_start and t.createdate < r.cutoff_dt
group by
t.prdcode
;
Please note this approach to the date ranges should work for any precision of createdate (date, datetime, datetime2).
| prdcode | this_week | this_month | this_year |
|---|---|---|---|
| 121 | 0 | 2.5 | 2.5 |
| 123 | 10 | 10 | 10 |
| 125 | 0 | 0 | 2.5 |
| 126 | 0 | 0 | 3 |
| 132 | 0 | 0 | 4 |