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).
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')