SQL、SSRS、SQL SERVER

Question

我在SQL中有一个基于事务的表，我正在从中创建一个SSRS报告。我有一个基于交易日期的过滤器，如下所示：

Label 7-13 value 1
Label 14-28 value 2
Label 7-28 value 3

如果用户选择1，那么我需要显示从今天起7-13天内发生的所有交易。对其他人也是如此。请告诉我如何在查询级别做到这一点。

Answer 1

下面是一种包含示例数据的方法：

declare @value int
select @value = 1

;with cte as
(
    select cast(getdate() as date) dt
    union all
    select dateadd(day, -1, dt)
    from cte
    where dt > getdate() - 30
)
select * 
from cte
where (@value = 1 and dt between DATEADD(day, -13, cast(getdate() as date)) and DATEADD(day, -7, cast(getdate() as date)))
or (@value = 2 and dt between DATEADD(day, -28, cast(getdate() as date)) and DATEADD(day, -14, cast(getdate() as date)))
or (@value = 3 and dt between DATEADD(day, -28, cast(getdate() as date)) and DATEADD(day, -7, cast(getdate() as date)))

关键在于where子句：

WHERE (@value = 1 AND [do this if @value = 1])
OR (@value = 2 AND [do this if @value = 2])
etc.