Howdy, Stranger!

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

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.