We have 3 shifts running at our plant,
day 0720 - 1520
swing 1520 - 1120
night 2320 - 0720
I have a table storing the bags packed on each shift, and now need to display the team (tech, packer_l, packer_r etc) with the greatest number of packed bags depending on the time of day it is now.
So if it is 1100 (am) I want the query to return the best day shift result for this month.
The query works fine if I give it a time range manually but I cannot get the case to work right. I have tried if, else without success.
select day(timestamp) as timestamp, tech, packer_l, packer_r, (packed_l + packed_r) as packed, timestamp as ts
FROM abm_status
where
time(ts) between
case time(now())
when between '07:20:00' and '15:19:59'
then '07:20:00' and '15:19:59'
when between '15:20:00' and '23:19:59'
then '15:20:00' and '23:19:59'
when between '23:20:00' and '07:19:59'
then '23:20:00' and '07:19:59'
end case
and tech != ''
and tech != 'Dummy Login'
and YEAR(timestamp) = YEAR(CURDATE()) AND MONTH(timestamp) = MONTH(CURDATE())
order by packed desc limit 1