How to count how many days late?

raffiq_eddy · #1 (**permalink**) 01-14-09, 21:00

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

r937 · #2 (**permalink**) 01-14-09, 22:20

anyone interested in replying to this thread should really read this thread for some background on what's been suggested already

Frunkie · #3 (**permalink**) 01-15-09, 00:53

Nice catch Rudy!

raffiq_eddy · #4 (**permalink**) 01-15-09, 00:53

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!

r937 · #5 (**permalink**) 01-15-09, 01:14

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

guelphdad · #6 (**permalink**) 01-15-09, 11:05

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech