SQL Server使用范围创建输出表
SQL Server使用范围创建输出表
提问于 2018-05-31 16:59:22
我有以下疑问：
select a.ID, a.Date_Reported AS [Date Sent to X], b.Date_Received AS [Date Returned from X],
(datediff(dd, a.date_reported, b.date_received)
+ CASE WHEN Datepart(dw, b.date_received) = 7 THEN 1 ELSE 0 END
- (Datediff(wk, a.date_reported, b.date_received) * 2 )
- CASE WHEN Datepart(dw, b.date_received) = 1 THEN 1 ELSE 0 END +
- CASE WHEN Datepart(dw, b.date_received) = 1 THEN 1 ELSE 0
END) AS [Time_Spent]
from Tx_Ex a
join Tx b on b.id = a.id简单地说，该查询的作用是在两个表中查找两个日期(收到日期-报告日期)之间的工作日差异。
我想调整查询，使我的输出看起来像下面这样：
Time Taken (days) | 0-3 | 4 | 5 | 6-8 | 9+ | less than 0 days
Count | 2 | 1 | 2 | 1 | 1 | 3
% | 20 | 10| 20| 10 | 10 | 30因此，基本上，我只是为count和%添加了虚拟值，以便更好地了解我想要什么。本质上，我希望调整上面的查询，这样我就有了上面的范围和另外两个带有count和%的行。
上表中的示例，查询将告诉我有两个实例，其中两个日期的差值落在0-3范围内，因此占总计数的20%。此外，还有一些情况(由于错误)所用的时间可能是负数(即报告的日期实际上晚于收到的日期)，所以我添加了“小于0范围”。
如果有什么不清楚的地方，请告诉我。
回答 1
Stack Overflow用户
发布于 2018-05-31 18:52:53
您可以使用条件聚合将结果放在一行(而不是两行)中：
with t as (
<your query here>
)
select sum(case when time_spent < 0 then 1 else 0 end) as days_lt_0,
sum(case when time_spent between 0 and 3 then 1 else 0 end) as days_0_3,
sum(case when time_spent 4 then 1 else 0 end) as days_4,
sum(case when time_spent 5 then 1 else 0 end) as days_5,
sum(case when time_spent between 6 and 8 then 1 else 0 end) as days_6_38,
sum(case when time_spent >= 9 then 1 else 0 end) as days_9pl,
avg(case when time_spent < 0 then 1.0 else 0 end) as ratio_lt_0,
avg(case when time_spent between 0 and 3 then 1.0 else 0 end) as ratio_0_3,
avg(case when time_spent 4 then 1.0 else 0 end) as ratio_4,
avg(case when time_spent 5 then 1.0 else 0 end) as ratio_5,
avg(case when time_spent between 6 and 8 then 1.0 else 0 end) as ratio_6_38,
avg(case when time_spent >= 9 then 1.0 else 0 end) as ratio_9pl
from t;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50620410
复制相关文章
相似问题