Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115

    Unanswered: employee attendance with in/out times (was "Query Help Needed...")

    Dear Friends,
    I am in problem & have to solve one query.
    I have a one table with the employee time in & time out data, employee can go out & come in fequently in a day.

    I want to know that how much time every emp have attend in the company per day.

    Kindly, do reply as soon as possible.

    I am enclosing data defination in txt file along with the data in the MS Excel file.

    Thanx ....
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42
    I would advice ( this is how i would have done it anyway)

    1>Join column iodate and iotime together cause MSSQL has datatype datetime, no seperate date and time datatype(atleasnt not to my knowledge)

    2>Create another column iodatetimeexit, which has the exit datetime in the same row. So we would have two columns iodatetimeenter and iodatetimeexit

    3>There is a function is tsql DATEDIFF(), this should make you task much easier.


    This way anytime someone enter the building create a new row, everytime a person leaves a building find an existing row with iodatetimeexit empty and fill it up(for that person obvious). Then at the end of the day or whenever you feel like create a function to use DATEDIFF() and group by Cardno,Holderno. (you would lose iogatename info)
    I ain't to good with datetime so this is my opinion.
    Last edited by SPere; 04-08-06 at 09:36.

  3. #3
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Thanx Spere,
    But I am new guy to SQL, can you help me to write a query..?

  4. #4
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42
    Is it necessary for a person to exit from the same iogatename they entered?
    How do you come to know if the iotime is am or pm??
    Last edited by SPere; 04-08-06 at 16:14.

  5. #5
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by SPere
    Is it necessary for a person to exit from the same iogatename they entered?
    How do you come to know if the iotime is am or pm??
    I think the main gate should be the exit point,and 24 hrs format should help in this case...well thats my guess work,I am waiting to hear Rajesh's comment on it.
    Last edited by rudra; 04-09-06 at 08:31.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  6. #6
    Join Date
    Apr 2006
    Location
    System.Web.UI.WebControls.Home.BedRoom2
    Posts
    42

    Lightbulb Stuff that needs to be clarified

    I think some records are missing.After you run this command, observe the result set:

    select * from iodata where cardno=3 and iogatename='MAIN DOOR(I/O)' order by iodate,iotime

    Record 41 shows Santosh enters through the main door on 02-03-2006,than
    Record 42 shows Santosh enters through the main door on 03-03-2006,than
    Record 43 shows Santosh enters through the main door on 09-03-2006,
    which means he never exited the building during this time

    Is there any need for SERVICE ROOM(I/O),assuming once inside the MAIN DOOR(I/O), Santosh is doing some useful work??
    Last edited by SPere; 04-09-06 at 09:45.

  7. #7
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    No, Its not necessary for a person to exit from the same iogatename they entered. I need only total hours which he spent in the office.

  8. #8
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Quote Originally Posted by SPere
    I think some records are missing.After you run this command, observe the result set:

    select * from iodata where cardno=3 and iogatename='MAIN DOOR(I/O)' order by iodate,iotime

    Record 41 shows Santosh enters through the main door on 02-03-2006,than
    Record 42 shows Santosh enters through the main door on 03-03-2006,than
    Record 43 shows Santosh enters through the main door on 09-03-2006,
    which means he never exited the building during this time

    Is there any need for SERVICE ROOM(I/O),assuming once inside the MAIN DOOR(I/O), Santosh is doing some useful work??

    There can be a problem with data because of I have generated it randomly for experimental purpose... sorry, I didn't tell you before....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •