Large mySQL Query

Discussion in 'OT Technology' started by Slid., Dec 13, 2004.

  1. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    So I've been creating this timecard system to track punch in/out for employees and everything has been going along just fine but there are some things that I want to speed up but I'm not sure if it can be done.

    I have one table called punchlog, each time someone punches in or out this table gets a new row. I have the following fields:

    -punchid
    -employeeid
    -systemtime
    -systemdate
    -punchtime
    -punchdate
    -inout

    The "inout" field is the only one that isn't really needed.

    Anyways, I want two queries, one to get the total time punched IN for a day and one to get the total time punched in for a given week. I've been using the mysql functions TIMEDIFF to get the hours worked and I've been using DAYOFWEEK for calculating all the days in a week.

    I just can't figure out how to compare the values from two different rows let alone do it several times for a single day (punch outs for breaks, lunch, etc).

    In PHP I just calculate the last day of the week for any given date then step back 6 days and on each day I run a few loops to get the daily hours, etc.

    Is there any way to port this more to mySQL or am I hindered because I need so many ifs and loops? :hsd:
     
  2. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    psuedo-code:

    select * from punchlog where systemdate > start_of_week and systemdate < end_of_week and employeeid=user_id;

    that'll give you a recordset of all punches within a week for a given user. You can then work your way through the recordset using:

    while ($rs)
    {
    }

    And then depending on how you distinguish between clock-in and clock-out, you can determine the time between a clock-in and clock-out, and add that to a variable of type either integer for minutes, or double for hours. At the end you have the total time in either integer (min) or double (hours). This also easily facilitates overtime calculations. Simply determine the time worked in a given day, then if it's over 8 hours (as an example) bill OT for up to 12, then DOT for over 12. etc.
     
  3. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    There might be an easier way but I need to know:

    How many punches can they do a day? 0 and 2? Or could you have a guy that punched in 3 times (say 8:00, 17:00 and 17:01). What happens then?
     
  4. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    An even number, I figure I'll support up to 5 sets of in/out punches (10 punches total). I do have to account for the occassional guy that forgets to punch out for the day however. Relying on an even number would get me into trouble :hs:
     
  5. P07r0457

    P07r0457 New Member

    Joined:
    Sep 20, 2004
    Messages:
    28,491
    Likes Received:
    0
    Location:
    Southern Oregon
    On the punch-system I've made in the past, anyone that did not punch out on a given day would be locked out, and unable to punch in the following day without an admin-override (meaning we'd have to manually enter a record that said he clocked out)

    It was good to keep people from abusing the system, but not to punish for the true accidents.
     
  6. Slid.

    Slid. I'm a guy.

    Joined:
    Oct 25, 2001
    Messages:
    1,928
    Likes Received:
    0
    Location:
    NH
    I'm having it setup so that anyone can write a "past punch" for logging in or out, it gives you the option whenever you punch to record the time for now or for another time.

    If someone writes in their own time then the system will take it but it will be pending approval from their manager -- it will work itself out naturally when someones manager is sick of approving a write-in every other day.
     

Share This Page