how to find working hours

[ssoni]

how can i find total working hours for an employee by calculating his check in and check out time?
Suppose if employee work for 2 hrs and check out for some reason and again check in then how to find total working hours. In database time is given in datetime format. thanks in advance.

[C CSR]

Where are you going to make your calculations? What language?

[ssoni]

Have to create a stored procedure for it in sql server 2008.

[C CSR]

When they clock out, does it do any totally of the hours for that period already? Or, do you have it set up to just input the PunchinTime and the PunchoutTime, and want to do the totally per period at some point later, like weekly, biweekly, etc. A good app would handle the totaling in realtime. Example (hope it fits):

EmpID Date Punchin Punchout PeriodTotalHrs
1 1/20/2012 10:32:00PM NULL NULL
1/20/2012 10:32:00PM 04:25:00 AM [calculated now]

(note this is the same record, edited on Punchout)

Then you would just be totaling the PeriodTotalHrs column at the point of "delivery" (a later time).

If you don't have an app set up to do something like this already, and you have records in there with Punchin/Punchout times but nothing in the PeriodTotalHrs column (or it doesn't exist), then we have 2 types of calculations to do. Do you need both?

[C CSR]

Sorry, That "table" above didn't format very well.

EmpID Date      Punchin    Punchout   PeriodTotalHrs
1     1/20/2012 10:32:00PM NULL       NULL
      1/20/2012 10:32:00PM 04:25:00AM [calculated now]
 

[ssoni]

I have one table in which total hrs of employee is mentioned. like first check in and last check out. and another table is there in which every check in and out are mentioned. now i want to exclude those hrs in which employee is out.
thanks.

[C CSR]

I need to make sure what you're telling me. You said:

A) "one table in which total hrs of employee is mentioned. like first check in and last check out." Does it look like this?

Tbl1 '            
    Checkin    Checkout    Total hrs
Day 1    [time1]    [time out    8
Day 2    [time1]    [time out    6.5
Day 3    [time1]    [time out    7
 

Then you said:

B) "another table is there in which every check in and out are mentioned." Does it look like this?

Tbl2            
    Checkin    Checkout    
Day 1    [time1]    [time out]    
Day 2    [time1]    [time out]    
Day 3    [time1]    [time out]    
 

Then you said:

C) "i want to exclude those hrs in which employee is out."

'---------
What do you mean by "exclude" hours when "employee is out"? That part doesn't make since to me.

Please take each piece of your reply as I have above and explain like this:

A) answer:
B) answer:
C) answer:

Be very clear because the coding solution can be different depending on the structure of the information as it appears in the table(s).

Sorry for the delay in my response.

[ssoni]

Hey thnx for ur work..you are nearly close to what i want to explain.

A) First table looks like your table.

B) 1. Tbl2
2. Checkin Checkout Empid
3. Day 1 9:00 11:35 1
4. 12:00 13:05 1
5. 13:40 18:00 1

Now I need to calculate the working hours of employee for which he was in to the office.

Is that possible?

Thanks

[C CSR]

Okay. I assume the data is already in a "Time format" and that you want the result in "decimal" format to be used for other calculations: from the result = 08:00:00 to the result = 8.00 hours. Date/Time calculations automatically does a conversion to the type "double" and the result is given in a ratio that can be converted from seconds to minutes or hours.

For good measure you need a date field (e.g.; 1/20/2012) to make sure you can calculate a time difference if someone checks in late at night on one day and checks out early the next morning (e.g.; 11:00 PM to 1:00 AM). The date and the times have to be in the same value. Then you would have: checkin "1/20/2012 06:00:00 PM" and checkout "1/22/2012 02:00:00 AM." The time calculations will see the date crossover because of its conversion to the "double type."

The calculation follows this order:

1)Subtract the Date/Times:  Result is a Date/Time type.
 
2) Convert to Double type (0.00000000) 
 
3) Multiply the result * 86400 (to get seconds) and divide by 60 (to get minutes) and divide by 60 again (to get  hours).
 
4) "Round" to the precision desired (2 or maybe 3) to get  for example, 8.25 hours.
 

There are built in time functions for various programming languages that you could use but I have to know what it is if you want to work the calculations outside of the SQL server.

Here are 2 Update queries that should work in SQL server. One only uses a conversion to double, the other uses a function called DateDiff to make the conversion and shorten the math.

The inside calculation is:

CDbl([Hours]![checkout]-[Hours]![checkIn])*86400/60/60,

then I Round it so the final looks like this (note the precision 2 on the end):

Round(CDbl([Hours]![checkout]-[Hours]![checkIn])*86400/60/60,2)

UPDATE Hours SET Timeclock.TotHours = Round(CDbl([Hours]![checkout] - [Hours]![checkin]) * 86400/60/60, 2)
WHERE (((Hours.EmpID)="100"));
 

You might also be able to use: Round(DateDiff("n",[Hours]![checkIn],[Hours]![PunchOut]) /60,2), where the "DateDiff" function take away the seconds and minutes calculations.

UPDATE Hours SET Timeclock.TotHours = Round(DateDiff("n",[Hours]![checkIn],[Hours]![checkOut])/60,2)
WHERE (((Hours.EmpID)="100"));
 

Are there any complications I'm unaware of?

[C CSR]

Also, these queries assume you have a Field called "ToTHours" (for each checkin/checkout record)in the same table to capture the Result. Once those columns are filled you can total them in a query based on the EmpID. I don't know where you want that total to end up (another table?) and I don't know when you will run the query as part of your program, so I didn't include it in my answer.

[C CSR]

To avoid confusion, I typed in "Timeclock" as the tablename, but it should have said "Hours.TotHours" to correspond with the other fields, like "[Hours]![checkin] and [Hours!checkout], etc. Sorry for my sloppy work :(

[swathee]

For more information u click below links:

none Time Clock Table Design - Revisited - MSDN - MicrosoftSolving

Complex T-SQL Problems, Step-By-Step - Simple Talk