Need to skip 'lunch hour' when calculating time worked 1

[songwritingguy]

Greetings,

I am using a query which captures 'logon' time for each user and then 'Time of last activity'. I'm then doing simple subtraction to return 'Actual hours worked'. However, I need to skip the lunch hour in my calculation because no work is being performed. Here's the portion of my query that deals with the time:

CVDate([LastOfACTIVITY_DATE_TIME]-[FirstOfLOGON_DATE_TIME]) AS [Time Logged]

currently, when I run the query at 1pm, I get the following results: user#1 who started at 8am and quit at 12pm shows time logged as 4 hrs which is correct. User #2 started at 8am and took a 1 hour lunch at noon, yet his time logged shows 5 hrs.

How can I ammend my query to reflect the downtime?
Having my user logoff at lunch is not an option due to other complicated issues.

Thanks,
Drew

 

[traingamer]

You need to determine a rule for when to subtract an hour. For example if you determine that any continuous time over 5 hours assumes a one hour lunch, you could:

 iif( CVDate([LastOfACTIVITY_DATE_TIME] - [FirstOfLOGON_DATE_TIME]) >= 5:00:00, CVDate([LastOfACTIVITY_DATE_TIME] - [FirstOfLOGON_DATE_TIME]) AS [Time Logged] - 1:00:00, CVDate([LastOfACTIVITY_DATE_TIME] - [FirstOfLOGON_DATE_TIME]) AS [Time Logged]) AS [Time Logged]

which basically checks to see if logged time is over 5 hours - if so, subtract 1 hour, else use logged time.
(I'm not sure if the 1:00:00 and 5:00:00 are valid code, but you get the idea).

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[SkipVought]

Hi,

How do you KNOW that user #1 did NOT take any lunch break before he clocked out?

How do you know that any user's lunch break is exactly 1 hour?

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[PHV]

DateAdd('h', IIf([FirstOfLOGON_DATE_TIME]<=#12:00:00# And [LastOfACTIVITY_DATE_TIME]>=#13:00:00#,-1,0), CVDate([LastOfACTIVITY_DATE_TIME]-[FirstOfLOGON_DATE_TIME])) AS [Time Logged]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[songwritingguy]

Thanks for the help guys!
Skip - 1 hour lunch is mandator for my guys, so I don't think this will be a problem.

 

[songwritingguy]

PHV,

Your response looks to me like it would acheive the results I need. Forgive me, but I cannot figure how to incorporate this into my statement. Can you show me where to plug this in? Here's my complete SQL:

SELECT [OPP-User First Activity Logged Today Summary].USER_NAME, [OPP-User First Activity Logged Today Summary].FirstOfLOGON_DATE_TIME, [OPP-User Last Activity Logged Today Summary].LastOfACTIVITY_DATE_TIME, CVDate([LastOfACTIVITY_DATE_TIME]-[FirstOfLOGON_DATE_TIME]) AS [Time Logged]
FROM [OPP-User First Activity Logged Today Summary] INNER JOIN [OPP-User Last Activity Logged Today Summary] ON [OPP-User First Activity Logged Today Summary].USER_NAME = [OPP-User Last Activity Logged Today Summary].USER_NAME;


Thanks,
Drew

 

[PHV]

Replace this:
CVDate([LastOfACTIVITY_DATE_TIME]-[FirstOfLOGON_DATE_TIME]) AS [Time Logged]
By this:
DateAdd('h', IIf([FirstOfLOGON_DATE_TIME]<=#12:00:00# And [LastOfACTIVITY_DATE_TIME]>=#13:00:00#,-1,0), CVDate([LastOfACTIVITY_DATE_TIME]-[FirstOfLOGON_DATE_TIME])) AS [Time Logged]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[songwritingguy]

PHV,

thanks. I plugged this in as suggested, but it still seemed to return results that include the hour. Any idea why? Here are the results:
LOGON Last Time
8:06 14:47 6:40
8:11 14:47 6:36
8:05 14:50 6:45
7:58 14:38 6:40
8:04 14:44 6:40
8:04 14:50 6:45
13:25 13:48 0:22
8:03 14:37 6:33
8:12 14:36 6:24
10:28 10:29 0:00
9:56 13:51 3:55

 

[PHV]

And this ?
DateAdd('h', IIf(TimeValue([FirstOfLOGON_DATE_TIME])<=#12:00:00# And TimeValue([LastOfACTIVITY_DATE_TIME])>=#13:00:00#,-1,0), CVDate([LastOfACTIVITY_DATE_TIME]-[FirstOfLOGON_DATE_TIME])) AS [Time Logged]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[songwritingguy]

PHV,

BadaBing!
That did it!

Thanks,
Drew