问题

[DB FIDDLE][1]

我正在尝试在SSRS中创建一个表格，该表格显示按产品名称分组的本周至今、本月至今和本年至今的总销售额。我的查询表格有一个名为'TTPrice'的列，其中包含产品的销售额。本周至今的总销售额是从所选日期所在的周的总销售额开始计算，从星期一开始计算。如果所选日期是星期一，那么它只会获取当天的销售额。如果是星期二，那么它将获取星期一（昨天）和星期二（所选日期）的销售额，其余依此类推。本月至今的总销售额是从所选日期所在月的第一天开始计算，直到所选日期结束，例如如果所选日期是2021-06-15，则它将获取从2021-06-01到2021-06-15的销售额。本年至今是从本年的第一天到所选日期的总销售额。因此，表格将如下所示：

这三个计算列可能没有值，因为这完全取决于所选日期。以下是获取产品销售额的查询：

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)

我有一个名为'CurrentDate'的日期/时间参数，用于选择的日期，并尝试使用表达式和日期/时间参数来找到本周、本月和本年至今的总销售额。

本周至今列表达式

=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)

本月至今列表达式

=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)

本年至今列表达式

=IF(Fields!CREATEDATE.Value >= DATESERIAL(YEAR(Parameters!CurrentDate.Value), 1, 1) AND Fields!CREATEDATE.Value <= Parameters!CurrentDate.Value, Sum(Fields!TTPrice.Value), 0)

在预览报表后，我注意到本周和本月至今列显示为0，而只有本年至今列显示了正确的值。只有当这三列巧合地具有相同的值时，本周和本月至今列才会有值，如下所示：

我已经检查了我的数据，确实有销售额在所选日期的周和月，但表达式只返回了0，忽略了我的IF语句中的条件。我还尝试通过以下查询生成总销售额：

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

<details>
<summary>英文:</summary>

**[DB FIDDLE][1]**

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 &#39;TTPrice&#39; column that has the sales of the product. The week to date total sales is obtained from the total sales of the selected date&#39;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&#39;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:
1. Is there something wrong with the conditions in the expressions? Or is it a bug caused by the row grouping of Table Wizard?
2. Should I try to do it in the query instead of using expressions in SSRS?
3. If so, how do I fix my query so that I will get the table as expected?
[1]: https://www.db-fiddle.com/f/naiwUmEJjZn8QJ2xuHqGAX/3
</details>
# 答案1
**得分**: 1
对于T-SQL/SQL Server，使用7的模数是一种方便且可靠的方法，可以确定任何日期是星期几。在`datediff`函数中，第0天（1900年1月1日）是星期一，因此以下代码将始终对任何星期一返回-0，对任何星期二返回-1，依此类推。
```sql
-datediff(day,0,@CurrentDate) % 7

使用这种方法，并将日期范围与您的数据交叉连接：

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
;

请注意，这种处理日期范围的方法适用于任何createdate的精度（日期，datetime，datetime2）。

fiddle

-datediff(day,0,@CurrentDate) % 7

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
;