Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2005
    Posts
    5

    Unanswered: How to count how many days late?

    Hi,

    I've table TblAttndn with data like this

    Code:
    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:
    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 e.g.
    day1=not empty (late)
    day2=empty (not late)
    day3=not empty (late)
    ..
    ..
    ..
    day31=not empty (late)

    so the result give 3 days late
    2) count by 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 OR mysql function

    pls help me!

    TIA

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    anyone interested in replying to this thread should really read this thread for some background on what's been suggested already
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Nice catch Rudy!

  4. #4
    Join Date
    Apr 2005
    Posts
    5
    Quote Originally Posted by r937
    anyone interested in replying to this thread should really read this thread for some background on what's been suggested already
    I hope to find solution from difference forums -- today, I come to know, all the members are the same everywhere, every forum!

    I hope, I'm NOT doing anything wrong or against any rules with my asking-around strategies.

    BTW, r937, I still remember you & your kind help with encryption solution in year 2003 -- god blessed you!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by raffiq_eddy
    I hope, I'm NOT doing anything wrong or against any rules with my asking-around strategies.
    not at all, it is perfectly okay to keep looking for an answer, on multiple forums

    i just thought that i would save people on this forum the trouble of going through the same responses -- you know, like "gee, maybe you should normalize your data" -- which you would most assuredly have gotten

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    you aren't doing anything against the rules. it would help though if you included more information in your post since you've been given more information.

    Tell people that you can't normalize the table, show what other suggestions have been made and ask if there are better or faster solutions.

    Do that rather than ask the same question in a different forum and pretend it hasn't been asked 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
  •