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