Time card calculations

[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.

 

[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.