我必须改进此查询,效果很好。
DECLARE @timTimeout int,
@iniDate varchar(20),
@endDate varchar(20)
SET @iniDate = '2014-07-20 00:00:00'
SET @endDate = '2014-11-24 23:59:59'
SET @timTimeout = 4000
SET ANSI_WARNINGS OFF
SELECT
'Approved (0200)' = ISNULL(SUM(CASE CodMsgIncome WHEN '0200' THEN 1 END), 0),
'Approved Off (0220)' = ISNULL(SUM(CASE CodMsgIncome WHEN '0220' THEN 1 END), 0),
'Cancel (0400)' = ISNULL(SUM(CASE CodMsgIncome WHEN '0400' THEN 1 END), 0),
'Regret (0420)' = ISNULL(SUM(CASE CodMsgIncome WHEN '0420' THEN 1 END), 0),
'TOTAL' = COUNT(*),
'Time-outs' = ISNULL(SUM(CASE WHEN DATEDIFF(ms, DateMsgIncome, DateMsgSent) > @timTimeout THEN 1 END), 0),
'Disponibility (%)' = (1 - CAST(ISNULL(SUM(CASE WHEN DATEDIFF(ms, DateMsgIncome, DateMsgSent) > @timTimeout THEN 1 END), 0) as money) / COUNT(*)) * 100
FROM Message (NOLOCK)
WHERE DateMsgIncome BETWEEN @iniDate AND @endDate
AND CodMsgIncome IN ('0200', '0220', '0400', '0420', '0800', '0900', '9080', '9085')
AND DescMsgIncome <> '0220'
现在,我必须准备一个报告,其中包含按月组织的总计数据。
输出错误似乎是这样的:
Approved (0200) | Approved Off (0220) | Cancel | Total | Time-outs | Disponibility (%)
July | 35 15 12 62 0 100.00
.
.
.
编辑:这只是我查询中的一个表。
Table Message:
DateMsgIncome date,
DateMsgSent date,
CodMsgIncome varchar(4),
DescMsgIncome varchar(4),
CodMsgAnswer int.
任何建议都欢迎。提前致谢。
我通过代码格式化程序运行您的查询,以帮助清理它。我也更改了变量声明,因为您似乎不明白我在说什么。为了记录下来,您编码的方式可能是在一天的最后几毫秒中遗漏了一些行。
我更改了DATEDIFF函数,以使用日期部分的名称拼写清楚,因为使用错误的缩写太容易导致错误。我还简化了最后一列的计算。如果将1-更改为1.0-,则不需要强制转换为金钱。您应该避免为对象名称使用保留字,并避免在列名称中使用空格。让前端进行这种漂亮的格式化。
我还添加了使用表提示时所需的WITH关键字。(我建议您在使用NOLOCK之前先了解它的真正含义)。
DECLARE @timTimeout int
, @iniDate date
, @endDate date
SET @iniDate = '2014-07-20'
SET @endDate = '2014-11-25'
SET @timTimeout = 4000
SELECT MONTH(DateMsgIncome) as MyMonthColumn
, 'Approved (0200)' = ISNULL(SUM(CASE CodMsgIncome WHEN '0200' THEN 1 END), 0)
, 'Approved Off (0220)' = ISNULL(SUM(CASE CodMsgIncome WHEN '0220' THEN 1 END), 0)
, 'Cancel (0400)' = ISNULL(SUM(CASE CodMsgIncome WHEN '0400' THEN 1 END), 0)
, 'Regret (0420)' = ISNULL(SUM(CASE CodMsgIncome WHEN '0420' THEN 1 END), 0)
, 'TOTAL' = COUNT(*)
, 'Time-outs' = ISNULL(SUM(CASE WHEN DATEDIFF(MILLISECOND, DateMsgIncome, DateMsgSent) > @timTimeout THEN 1 END), 0)
, 'Disponibility (%)' = (1.0 - ISNULL(SUM(CASE WHEN DATEDIFF(MILLISECOND, DateMsgIncome, DateMsgSent) > @timTimeout THEN 1 END), 0) / COUNT(*)) * 100
FROM [Message] WITH (NOLOCK) --Ack!!! I wouldn't let this fly on my system due to inconsistencies with this hint unless accuracy is not important (like
WHERE DateMsgIncome >= @iniDate
AND DateMsgIncome < @endDate
AND CodMsgIncome IN
(
'0200'
, '0220'
, '0400'
, '0420'
, '0800'
, '0900'
, '9080'
, '9085'
)
AND DescMsgIncome <> '0220'
GROUP BY MONTH(DateMsgIncome)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句