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