为了找到一个星期的数据总和(在我的情况下为CallsAnswered),但从6 PM到12 AM之间是特定的小时数,我在下面设置了一个查询,但是它跳过了示例数据中的一天数据。您能否验证我的查询,并帮助我找到失踪的地方?
创建查询:
Create Table tblSingleBox (ScanDate DateTime, SkillTargetID Varchar(10), CallsAnswered int)
插入查询
Insert into tblSingleBox
Values ('2018-02-18 19:17:01', '10888', 32),
('2018-02-18 23:59:59', '10888', 01),
('2018-02-19 00:10:01', '10888', 15),
('2018-02-19 17:59:59', '10889', 12),
('2018-02-19 20:59:59', '10889', 90),
('2018-02-25 21:59:59', '10889', 40)
查询:
SELECT convert(varchar(10),ScanDate,101)ScanDate,SkillTargetID, SUM(CallsAnswered)CallsAnswered
FROM tblSingleBox (nolock)
WHERE DATEPART(Hour, scandate) between 18 and 24
and (scandate between '2018-02-18' and '2018-02-25')
GROUP BY convert(varchar(10),ScanDate,101) ,SkillTargetID
order by convert(varchar(10),ScanDate,101)
预期产量:
ScanDate SkillTargetID CallsAnswered
18-02-2018 10888 33
18-02-2018 10889 11
19-02-2018 10889 90
25-02-2018 10889 40
我得到的实际输出是:
ScanDate SkillTargetID CallsAnswered
02/18/2018 10888 33
02/18/2018 10889 11
02/19/2018 10889 90
scandate
有时间并且2018-02-25 21:59:59
大于2018-02-25
(2018-02-25 00:00:00
与DATETIME比较时被视为)。
因此强制转换scandate
为DATE
(截止时间)或更改
scandate between '2018-02-18' and '2018-02-25'
至
scandate >= '2018-02-18' and scandate < '2018-02-26'
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句