Switch Statement Not Accepting <,>,or = Operators

[zgadson]

I'm clearly doing something wrong and would love some guidance. I know enough SQL to dig myself into a deep hole, but not enough to get out of it, as I'm unfortunately very limited by our highly secured data which does not allow for any SQL statements outside of SELECT.

In short, I'm creating a productivity report for various warehouse shifts, and shifts that split days are proving complicated. This report basically totals up how many pallets shipped per hour with a 24 hour period starting at 5AM and ending at 4:59AM the following day. Basically I need the data from the screenshot here to be in a single line, so my thought was to simply change the date of the data to the next day for data recorded between the hours of midnight (hour 0) and 5AM.

In this example Aaron didn't do anything in the midnight hour at all, but had 464 cases picked in the hour of 1AM, so (red arrow), that 1AM data needs to be bumped to the previous date. Thought it would be simple, but with my database limitations from a locked down ODBC/AS400/IBM Client I'm not having fun. And to make matters worse, my DATE field refuses to accept any type of DateAdd functions - I'm thinking it might not be a legit field somehow but it's all I got.

SELECT

MAX(HOUR),

Switch(
MAX(HOUR) < 5, MAX(DATE) -1 DAY,
false, MAX(DATE)
) AS NEW_DATE,

MAX(LTRIM(RTRIM(HMBLDG))) AS BLDG,

MAX(LTRIM(RTRIM(HMUSER))) AS USER_ID,

MAX(LTRIM(RTRIM(USDESC))) AS USER_NAME,

MAX(CASE WHEN HMTTYP = 'AW' THEN 'Putaway'
WHEN HMTTYP = 'FM' THEN 'Floor Move'
WHEN HMTTYP = 'IS' THEN 'Staging'
WHEN HMTTYP = 'LO' THEN 'Loading'
WHEN HMTTYP = 'PK' THEN 'Picking' ELSE LTRIM(RTRIM(HMTTYP)) END) AS MOVE_TYPE,

SUM(CASE WHEN HMPQTY = 0 THEN 1 ELSE LTRIM(RTRIM(HMPQTY)) END) AS PLT

FROM LOREPORTH

GROUP BY  DATE, HOUR, HMBLDG, HMUSER, USDESC, HMPQTY

ORDER BY DATE, HOUR, HMBLDG ASC

Any guidance, ideas, suggestions, etc. would be immensely appreciated.

 

[petertenthije]

Fixed it! And it was way easier than I thought! Rather than changing the date, I used a subquery in the WHERE clause to select the data from the next record for data collected after midnight and before 5AM.

I am not sure that's a good idea.

If someone works overtime, then the overhours won't be reported on the previous day.
Likewise, if someone starts early, the early time might be reported under the previous day.

You are close though.
What I would suggest is to check the amount of work done between for instance 21:00 and 23:59 AND to check the work done between 19:00 and 21:00.

• 19:00-21:00 > 0 AND 21:00-23:59 = 0 -> Afternoon shift
• 19:00-21:00 > 0 AND 21:00-23:59 > 0 -> Afternoon shift with overtime
• 19:00-21:00 = 0 AND 21:00-23:59 > 0 -> Night shift

If someone worked the night shift, then move ALL their hours from 00:00 to 09:00 to the previous day.

(modify the hours as you see fit)

Assuming no-one does double shifts, this will guarantee that overtime is still correctly reported.




EDITED TO ADD:
another alternative way to determine the shift that might be a bit easier.
Check for each employee the MINIMUM time of the previous 12 hours. If the minimum time is equal or greater then 21:00, then he/she is night shift. Don't check the minimum of the previous 24 hours, because then you will get the previous day's night shift.