SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to count how many days late?

    Hi,

    I've table TblAttndn with data like this

    Code:
    StaffNo | YrMth  | Day1Late | Day2Late | Day3Late ... Day31Late
    1       | 200901 | 00:10    | 00:20    | 01:30    ... 00:00
    I want to calculate the total hours late, I've tried the following:
    Code:
    Select StaffNo,
           YearMonth,
           sec_to_time(time_to_sec(Day1) +
                       time_to_sec(Day2) +
                       time_to_sec(Day3) +
                       ..
                       ..
                       time_to_sec(Day31)) as TotHrsLate
    From TblAttndn
    I got the result for hours, but how do I...

    1) count how many late days
    2) count specific field e.g. day1 until day15 only, from given parameter such as:
    date from: 01/01/2009
    date to: 01/15/2009

    using only sql statement?

    pls help me

    TIA

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by raffiq_eddy View Post
    1) count how many late days
    Divide the hours by 24?
    2) count specific field e.g. day1 until day15 only, from given parameter such as:
    date from: 01/01/2009
    date to: 01/15/2009

    using only sql statement?
    You'll have to construct the query using PHP (or whatever language you're using).

  3. #3
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Divide the hours by 24?
    I mean, if...
    day1=not empty (late)
    day2=empty (not late)
    day3=not empty (late)
    ..
    ..
    ..
    day31=not empty (late)

    so the result give 3 days late

    You'll have to construct the query using PHP (or whatever language you're using).
    hmm.. Is that so?
    Last edited by raffiq_eddy; Jan 14, 2009 at 03:26.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    It would have so much easier if you had designed your table as follows:

    Staffno
    DayOfMonth
    Late

    Where DayOfMonth is a date field, and Late is the same type you defined your Day1Late etc. fields.

    This table would contain a row for each day a staff member is late, making extractions and calculations much easier.

  5. #5
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes! I agree -- but I'm not the one who design the database structure

    but it's ok, new year, new challenge, new knowledge!

    I just need to know how to do the calculation difference way

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by raffiq_eddy View Post
    yes! I agree -- but I'm not the one who design the database structure
    You can tell your Db designer that I think he ain't doing a very good job
    but it's ok, new year, new challenge, new knowledge!

    I just need to know how to do the calculation difference way
    I might be wrong (there might be a nice, clean, easy way to do it with SQL), but I would just extract the line you're interested in and do all the calculating with PHP.

    Quote Originally Posted by raffiq_eddy View Post
    hmm.. Is that so?
    I think so. How would you do it?

  7. #7
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can tell your Db designer that I think he ain't doing a very good job
    I'll keep that in my mind, and my things-to-do list hehehehhe

    I might be wrong (there might be a nice, clean, easy way to do it with SQL), but I would just extract the line you're interested in and do all the calculating with PHP.
    I really hoping there are some way to do it in MySQL!

  8. #8
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by raffiq_eddy
    yes! I agree -- but I'm not the one who design the database structure



    Quote Originally Posted by guido2004 View Post
    You can tell your Db designer that I think he ain't doing a very good job
    Unfortunately management doesn't mind hiring those types.

  9. #9
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by raffiq_eddy View Post
    I mean, if...
    day1=not empty (late)
    day2=empty (not late)
    day3=not empty (late)
    ..
    ..
    ..
    day31=not empty (late)

    so the result give 3 days late



    hmm.. Is that so?
    Be ready for a insanely nasty SQL statement:
    Code:
    ( IF(HOURS(Day1Late)>0,1,0) +
      IF(HOURS(Day2Late)>0,1,0) +
      IF(HOURS(Day3Late)>0,1,0) ...
      IF(HOURS(Day31Late)>0,1,0) ) AS days_late
    The employee is only counted as being late that day if they are over an hour late?
    MySQL v5.1.58
    PHP v5.3.6

  10. #10
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by BrandonK View Post
    Be ready for a insanely nasty SQL statement:
    Code:
    ( IF(HOURS(Day1Late)>0,1,0) +
      IF(HOURS(Day2Late)>0,1,0) +
      IF(HOURS(Day3Late)>0,1,0) ...
      IF(HOURS(Day31Late)>0,1,0) ) AS days_late
    great, I'll try it!

    Quote Originally Posted by BrandonK View Post
    The employee is only counted as being late that day if they are over an hour late?
    not really, when the fields is NOT EMPTY then he is LATE


    Just curiosity, maybe better solution using function which loop checking each field, with parameter?

  11. #11
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by raffiq_eddy View Post
    Just curiosity, maybe better solution using function which loop checking each field, with parameter?
    the best solution is using a normalized table

  12. #12
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    982
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by raffiq_eddy View Post
    not really, when the fields is NOT EMPTY then he is LATE


    Just curiosity, maybe better solution using function which loop checking each field, with parameter?
    If that's the case, drop the HOURS function from the code I gave you. I would assume that 00:01 is greater than 0, but Im' not sure how MySQL will handle the typecasting
    MySQL v5.1.58
    PHP v5.3.6

  13. #13
    SitePoint Member
    Join Date
    Jan 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by BrandonK View Post
    If that's the case, drop the HOURS function from the code I gave you. I would assume that 00:01 is greater than 0, but Im' not sure how MySQL will handle the typecasting
    It works!

    Taken from your 1st solution, I've changed it like this:

    time_to_sec(Day1))>0,1,0)

    Thanks BrandonK for the solution, plus to all who replied!


Bookmarks

Posting Permissions

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