Server 2008 R2 -存储顶级结果并在SSRS中使用

Question

我有一份SSRS报告，显示自收到投诉以来已过期的总天数。此SQL查询是最近收到的投诉的日期和日期之间的差异。

SELECT DATEDIFF(day, MAX(complaints.ComplaintReceived1Date),CURRENT_TIMESTAMP) as total 
FROM complaints WITH (nolock)

例如，如果这被设置为30 (天)，然后在我的SSRS报告中收到一个投诉，我想显示30作为以前的天数，没有投诉记录。是否有方法存储以前的结果并回忆这些数据？也许是临时桌子？

Answer 1

您已经将其存储在SQL查询引用的表中。

我会从那里取回它：

; with previouscomplaint as (

select 
complaintreceived1date,
RN = ROW_NUMBER() over (partition by complaintreceived1dateorder by complaintreceived1date desc))


select datediff(day,complaintreceived1date,current_timestamp) as previoustotal from previouscomplaint where RN=2

如果您想要两行之间的日期，请执行第二条语句：

select datediff(day, (select complaintreceived1date from previouscomplaint where rn = 2),(select complaintreceived1date from previouscomplaint where rn = 1)) as previoustotal

这没有经过测试，但应该有效。