Time card calculations (1 Viewer)

[Skater]

Hello all.

I have a database that collects workers punch times. Table fields are as follows
(TransactionKey,UserId,Customer,PunchTime,PunchType)
I calculate the time spent at the customer by using a datediff between a "Punch in" and a "Punch out"
My issue is some users have started visiting customers multiple times a day. I now get the time spent from both visits as well as the cumulative time between the first punch in and the last punch out.
Is there a way to fix this?

Thank you

 

[gemma-the-husky]

Well, yes - but it's finding a way to accumulate the time on all a given days, or maybe taking the earliest punchin, and the latest punchout.. It depends on your business rules, really.

 

[plog]

The best way to communicate data issues is with data. Please provide 2 sets to demonstrate your issue:

A. Starting data from your table(s). Include field and table names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect the final query to produce based on the data in A.

Again, 2 sets of data--Starting and expected results of starting.

 

[Skater]

Here is the data that pertains to my problem. The highlighted segment represents two different times the user entered an account in the same day. Right now I just use a datediff between "Punch in" and "Punch out" The problem is it calculates the difference from both entries do I get erroneous results.

Thanks for the help

Andy

Table Info

Report Results

 

[plog]

That's not a good example. You've got flaws in both your starting and expected data:

Starting - You're starting data has about 30 records, of which 5 (maybe 6) are highlighted--for reasons I cannot understand. Why isn't 44266834 or 43839549 part of the data to report on? It would seem if you wanted PunchIn/PunchOut matching you should have an even number of records which would start with a PunchIn and end with a PunchOut, yours sample starting data does the opposite.

Expected - You've got 6 records in expected results, of which 3 are highlighted--for reasons I cannot understand. How do those 5 initial records turn into 3 in the expected results? Also, I don't know how 'Horizon Warehouse' nor 'Norton' are to get into the expected results because you have no starting data table to account for them. Lastly, you have 3 columns of numbers which make no sense. 2 of them start with 7:37:02 which I see in your starting data, but surely 2 records in the result cannot share that record.

Please provide better starting and sample data to demonstrate what you have and what you want.

 

[Pat Hartman]

The best solution is to have punch outs on the same record as the punch ins. When a person punches in, you verify that there are no open punch in records. You have to decide how to deal with them. Then when they punch out, you find the open punch in. Again, you deal with missing in records at that time.

Dealing with bad data as it happens is always preferable to dealing with it later.

 

[Skater]

Thank you Pat. This is not an error. What causes this is that the user may go back to the same account later in the day.

 

[Pat Hartman]

I understand that you are not talking about an error but that doesn't mean that they don't happen. Having the in/out in the same record makes everything much easier was the point I was trying to make. Since you are in the early stages of development, I would change the table to make the rest of the process easier rather than writing a more complicated process to properly map multiple in/out punches for the same day. Allowing for multiple in/out's during the day just complicates the missing punch logic also.