I'm having trouble writing this query to give me any results. I'm using MariaDB as well.
SELECT CallDate AS Week_Of, AgentName,
COUNT(*) AS TOTAL_Calls,
SUM(case when Accepted = 'ANSWERED' then 1 ELSE 0 END) AS Answered,
SUM(case when Accepted = 'NO ANSWER' then 1 ELSE 0 end) AS NoAnswer
FROM jshou_custom.afterhours
WHERE CallDate >= DATE_ADD(NOW(), INTERVAL -1 WEEK)
AND TIME(CallDate) BETWEEN '17:00:00' AND '08:00:00'
GROUP BY AgentName
The DATE_ADD clause works just fine and gives results within that interval, but as soon as I add in the TIME function nothing is returned in the results. The CallDate format is 2021-09-21 HH:MM:SS
I have tried using HOUR as well in place of TIME, but it also returns nothing.
I'm trying to pull calls from any day within the range specified in the DATE_ADD clause. As long as it's between 1700 and 0800 (after hours calls).
CodePudding user response:
I think you need to check both dates something like this and adjust the times used on each date as well
WHERE
(CallDate > DATE_ADD(CURDATE(), INTERVAL -7 DAY) AND TIME(CallDate) > '17:00:00')
OR
(CallDate > DATE_ADD(CURDATE(), INTERVAL -6 DAY) AND TIME(CallDate) < '08:00:00')