一周中特定小时部分的数据总和-MS SQL查询

萨提亚·库玛(Sathiya Kumar)

为了找到一个星期的数据总和(在我的情况下为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
伊万·斯塔诺汀(Ivan Starostin)

scandate有时间并且2018-02-25 21:59:59大于2018-02-252018-02-25 00:00:00与DATETIME比较时被视为)。

因此强制转换scandateDATE(截止时间)或更改

scandate between '2018-02-18' and '2018-02-25'

scandate >= '2018-02-18' and scandate < '2018-02-26'

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章