# Thread: How to count how many days late?

1. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
anyone interested in replying to this thread should really read this thread for some background on what's been suggested already

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

4. Registered User
Join Date
Apr 2005
Posts
5
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. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
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

6. Registered User
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
•