在存储过程中的特定条件下显示的日期计数，例如 [0-6 天]、[7-14 天] 等

Question

我正在创建一个 ssrs 报告和 SP。 所以我在下面有这样的要求。 我有一个名为管理的表，因为我们有一个列描述，所以数据下 描述只有"开放"和"关闭"。

我有一个名为用户、开始日期和结束日期的参数

我需要显示所有用户都有介于开始日期和之间的未结案例计数 以下条件下的结束日期。 条件是 0-5天 6-11天 12-18天。

例如： 如果我输入开始日期(月/日/年(为 12-1-2019，结束日期为 12-31-2019 所以我需要显示未结案例计数在 0-5 天、6-11 天和 12-18 天之间的用户。

Answer 1

这样的事情怎么样？

select   [0-5] = SUM(IIF(DATEDIFF(day,OpenDate, CloseDate) >= 0 and DATEDIFF(day,OpenDate, CloseDate) <= 5,1,0))
,[6-11] = SUM(IIF(DATEDIFF(day,OpenDate, CloseDate) >= 6 and DATEDIFF(day,OpenDate, CloseDate) <= 11,1,0))
,[12-18] = SUM(IIF(DATEDIFF(day,OpenDate, CloseDate) >= 12 and DATEDIFF(day,OpenDate, CloseDate) <= 18,1,0))
from tblOpenClose

Answer 2

带有一点条件聚合。
结合交叉申请范围。

SELECT t.UserId, a.DateDiffRange
, COUNT(CASE WHEN t.Description LIKE 'Open%' THEN 1 END) AS [Open]
, COUNT(CASE WHEN t.Description LIKE 'Closed%' THEN 1 END) AS [Closed]
, COUNT(*) AS [Total]
FROM management AS t
CROSS APPLY
(
SELECT 
(CASE 
WHEN DATEDIFF(day, t.[Start Date], t.[End Date]) BETWEEN 0 AND 5 THEN '00-05'
WHEN DATEDIFF(day, t.[Start Date], t.[End Date]) BETWEEN 6 AND 11 THEN '06-11'
WHEN DATEDIFF(day, t.[Start Date], t.[End Date]) BETWEEN 12 AND 18 THEN '12-18'
WHEN DATEDIFF(day, t.[Start Date], t.[End Date]) BETWEEN 19 AND 30 THEN '19-30'
END) AS DateDiffRange
) AS a
WHERE t.[Start Date] >= CAST('2019-12-01' AS DATE)
AND t.[End Date] BETWEEN t.[Start Date] AND EOMONTH(t.[Start Date])
GROUP BY YEAR(t.[Start Date]), MONTH(t.[Start Date]), t.UserId, a.DateDiffRange
ORDER BY t.UserId, a.DateDiffRange;

以及每个用户的更多指标？
看看这个灵感：

SELECT (YEAR(t.[Start Date])*100+MONTH(t.[Start Date])) AS YearMonth, t.UserId
, SUM(CASE WHEN a.DateDiff BETWEEN 0 AND 5 THEN a.IsOpen END) AS [Opened Within 0-5]
, SUM(CASE WHEN a.DateDiff BETWEEN 6 AND 11 THEN a.IsOpen END) AS [Opened Within 6-11]
, SUM(CASE WHEN a.DateDiff BETWEEN 12 AND 18 THEN a.IsOpen END) AS [Opened Within 12-18]
, SUM(CASE WHEN a.DateDiff BETWEEN 19 AND 30 THEN a.IsOpen END) AS [Opened Within 19-30]
, SUM(CASE WHEN a.DateDiff BETWEEN 0 AND 5 THEN a.IsClosed END) AS [Closed Within 0-5]
, SUM(CASE WHEN a.DateDiff BETWEEN 6 AND 11 THEN a.IsClosed END) AS [Closed Within 6-11]
, SUM(CASE WHEN a.DateDiff BETWEEN 12 AND 18 THEN a.IsClosed END) AS [Closed Within 12-18]
, SUM(CASE WHEN a.DateDiff BETWEEN 19 AND 30 THEN a.IsClosed END) AS [Closed Within 19-30]
, SUM(CASE WHEN DAY(t.[Start Date]) BETWEEN 1 AND 5 THEN a.IsOpen END) AS [Opened Start 1-5]
, SUM(CASE WHEN DAY(t.[Start Date]) BETWEEN 6 AND 11 THEN a.IsOpen END) AS [Opened Start 6-11]
, SUM(CASE WHEN DAY(t.[Start Date]) BETWEEN 12 AND 18 THEN a.IsOpen END) AS [Opened Start 12-18]
, SUM(CASE WHEN DAY(t.[Start Date]) BETWEEN 19 AND 30 THEN a.IsOpen END) AS [Opened Start 19-31]
, SUM(CASE WHEN DAY(t.[Start Date]) BETWEEN 1 AND 5 THEN a.IsClosed END) AS [Closed Start 0-5]
, SUM(CASE WHEN DAY(t.[Start Date]) BETWEEN 6 AND 11 THEN a.IsClosed END) AS [Closed Start 6-11]
, SUM(CASE WHEN DAY(t.[Start Date]) BETWEEN 12 AND 18 THEN a.IsClosed END) AS [Closed Start 12-18]
, SUM(CASE WHEN DAY(t.[Start Date]) BETWEEN 19 AND 31 THEN a.IsClosed END) AS [Closed Start 19-31]
, COUNT(*) AS [Total]
FROM management AS t
CROSS APPLY
(
SELECT 
DATEDIFF(day, t.[Start Date], t.[End Date]) AS [DateDiff],
CASE WHEN t.Description LIKE 'Open%' THEN 1 END AS [IsOpen],
CASE WHEN t.Description LIKE 'Closed%' THEN 1 END AS [IsClosed]
) AS a
WHERE t.[Start Date] >= CAST('2019-12-01' AS DATE)
AND t.[End Date] BETWEEN t.[Start Date] AND EOMONTH(t.[Start Date])
GROUP BY YEAR(t.[Start Date]), MONTH(t.[Start Date]), t.UserId
ORDER BY YearMonth, t.UserId;

年月 |用户标识 |0-5内开盘 |6-11内开业 |12-18年内开业 |19-30内开业 |0-5 内关闭 |6-11 内关闭 |12-18 日内关闭 |19-30 内关闭 |打开开始 1-5 |打开开始 6-11 |开赛 12-18 |开赛 19-31 |关闭开始 0-5 |封闭式开始 6-11 |封闭式开始 12-18 |封闭式开始 19-31 |总 --------: |-----: |----------------: |-----------------: |------------------: |------------------: |----------------: |-----------------: |------------------: |------------------: |---------------: |----------------: |-----------------: |-----------------: |---------------: |----------------: |-----------------: |-----------------: |----:  201912 |   101 |                4 |                 4 |                  3 |                  1 |空|                 1 |                  3 |                  1 |              10 |空|                 2 |空|               5 |空|空|空|   17  201912 |   102 |                3 |                 2 |空|空|空|空|空|空|               5 |空|空|空|空|空|空|空|    5

在这里测试数据库<>小提琴