calculating total hours worked - Programmers Heaven

#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# calculating total hours worked

Posts: 9Member
hi all,

Can anyone tell me the solution to my problem....
i want to calculate the total hours worked by each employee. there are 100 cardnumbers.
my table structure:
EID EDATETIME CARDNUMBER
1 2003-04-25 14:26:27.000 10024
1 2003-04-25 14:26:44.000 10054
2 2003-04-25 14:26:03.000 10024
2 2003-04-25 14:26:14.000 10054
like the above there are thousands of records storing the in-time and out-time.

EID 2 means exit and 1 is entry

i want to calculate the total working hours and first-in and last-out of each employee (ASP AND SQL SERVER)

bye,

• Posts: 284Member
[b][red]This message was edited by raymcd at 2003-4-29 11:37:12[/red][/b][hr]
[b][red]This message was edited by raymcd at 2003-4-29 11:35:44[/red][/b][hr]
: hi all,
:
: Can anyone tell me the solution to my problem....
: i want to calculate the total hours worked by each employee. there are 100 cardnumbers.
: my table structure:
: EID EDATETIME CARDNUMBER
: 1 2003-04-25 14:26:27.000 10024
: 1 2003-04-25 14:26:44.000 10054
: 2 2003-04-25 14:26:03.000 10024
: 2 2003-04-25 14:26:14.000 10054
: like the above there are thousands of records storing the in-time and out-time.
:
: EID 2 means exit and 1 is entry
:
: i want to calculate the total working hours and first-in and last-out of each employee (ASP AND SQL SERVER)
:
: bye,
:

I wrote this SQL... It makes several assumptions.

1- Only one punch in, punch out pair per day.
2- NO shifts that cross a date boundry (work past midnight).
3- I Don't know what happens with Daylight Savings.

[code]
SELECT CardNumber, SUM(MinWorked) as TotalWorked
FROM(SELECT TimeIn.CardNumber, TimeIn.DateTime PunchIn,
TimeOut.DateTime PunchOut,
DateDiff(minute, TimeIn.DateTime, TimeOut.Datetime) as MinWorked
FROM CardTransactions TimeIn, CardTransactions TimeOut
WHERE TimeIn.CardNumber = TimeOut.CardNumber
AND DateDiff(day, TimeIn.DateTime, TimeOut.DateTime) = 0
AND TimeIn.EID = 1
AND TimeIn.DateTime > '1/1/1900'
AND TimeIn.DateTime < '1/1/9999'
AND TimeOut.EID = 2) Tmp
Group By CardNumber
Order by CardNumber
[/code]
This will spit out the number of minutes worked for each cardnumber. Also, the date criteria is only in there as a place holder so that you could tailor it to get the info for a single week/month/year/whatever.

-Ray