Switch Statement Not Accepting <,>,or = Operators

zgadson

Board Regular
Joined
Jul 16, 2015
Messages
68
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.


2.png


SQL:
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.
 
Last edited:

petertenthije

Board Regular
Joined
Sep 25, 2012
Messages
236
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.
 
Last edited:

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Watch MrExcel Video

Forum statistics

Threads
1,123,359
Messages
5,601,153
Members
414,431
Latest member
JustmemyselfandI

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top