How to count how many days late?

[raffiq_eddy]

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 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

[egarlock]

select ifnull(mod(time_to_sec(Day1),1)+1,0) + ifnull(mod(time_to_sec(Day2),1)+1,0) + from TblAttndn;

mod(x,1) will return 0 if x is not null, or null if x is null.

Erin Garlock

[egarlock]

PS: For question #2, you'd need to do a pivot table and then compute your dates. Google will give you lots of samples for "mysql pivot table".