troubleshooting Question

Extract exact time and categorize

Avatar of Michael Larimore
Michael LarimoreFlag for United States of America asked on
Microsoft AccessMicrosoft SQL Server
2 Comments1 Solution94 ViewsLast Modified:
I have a field that is formatted like "9/9/2016 8:00AM".  I would like to use this field to categorize whether an entry is "On Time" or "Late".  So far I am able to say that any thing after the hour of 5AM is late, however this categorizes the the 5:00AM time as "Late".  We also have two shifts/day so my code contains a nested IF to look at both 5am and 2pm.  

Ideally it would count if anything between 5:01AM and 12:00PM and between 2:01PM and 11:00PM as "Late" in the "Status" column.  Here is my code so far:

SELECT EmpNumber, FirstName, LastName, ClockIn, ClockOut, IIf((DatePart(hour,ClockIn) Between 5 And 12) Or (DatePart(hour,clockin) Between 14 And 21),'Late','On Time') AS Status

FROM v_MES_TcActivities

GROUP BY EmpNumber, FirstName, LastName, ClockIn, ClockOut, IIf((DatePart(hour,clockin) Between 5 And 12) Or (DatePart(hour,clockin) Between 14 And 21),'Late','On Time'), CostCtr

HAVING (((ClockIn) Between Getdate()-30 And Getdate()) AND ((IIf((DatePart(hour,clockin) Between 5 And 12) Or (DatePart(hour,clockin) Between 14 And 21),'Late','On Time')) Like 'Late') AND ((CostCtr)='106330'))

ORDER BY LastName, ClockIn DESC
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros