CODEWITHSUNDEEP
sql-serversql-server-2014datepart
Sathiya Kumar V M
Sathiya Kumar V M

Reputation: 517

Sum of data from a Specific Hour part for a Week - MS SQL Query

In order to find the Sum of data(CallsAnswered in my case) for a Week but a specific Hours between 6 PM to 12 AM., I have below set of a query, but it is skipping one-day data in my sample data. Could you please validate my query and help me out me to find where I'm missing?

Create Query:

Create Table tblSingleBox (ScanDate DateTime, SkillTargetID Varchar(10), CallsAnswered int)

Insert Query

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)

Query:

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)

Expected Output:

ScanDate    SkillTargetID   CallsAnswered
18-02-2018  10888   33
18-02-2018  10889   11
19-02-2018  10889   90
25-02-2018  10889   40

Actual Output I'm getting:

ScanDate    SkillTargetID   CallsAnswered
02/18/2018  10888   33
02/18/2018  10889   11
02/19/2018  10889   90

Upvotes: 1

Views: 132

Answers (1)

IVNSTN
IVNSTN

Reputation: 9299

scandate has time in it and 2018-02-25 21:59:59 is greater than 2018-02-25 (which is treated as 2018-02-25 00:00:00 when compared to DATETIME).

So cast scandate to DATE (to cutoff time) or change

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

to 

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

Upvotes: 2

Related Questions