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!

Categories

calculating total hours worked

bhavanibhavani 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,


Comments

  • raymcdraymcd 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




Sign In or Register to comment.