Hello all,
I get so many of my questions answered here.
What I have this time is a need to find a persons attendance record based on a 2 month time span. I have a drill table that has
drill_id
drill_dt
I also have a drill_lkup table that has
drill_id
uniqueid
status
The drill lkup is so I don't have a many to many relationship. It connects my members table to the drills table with uniqueid of all my soldiers in the clan. We have drills twice a week, on Wednesday and Saturday. In two months there is possible 16 drills, but there could be more. The status is either 'Present', 'Excused', or 'Absent'.
What we want to do is find out who are our more active troops, and this is the best way we figured out how to do this. If a person falls below 10% for one month they are on probation. If they maintain a 60% attendance they get a medal. 😃
What I need I guess is a function to find a persons attendance record. I could make a field called drill_cnt in the lkup table and increment this to one for everybody who is 'Present' at a drill. Do this for every drill_id/uniqueid/drill_dt combination and get the SUM for total_drill_cnt for any member.
So would I need to do something like--
SELECT drill_cnt FROM drill_lkup
WHERE DATE_SUB(CURDATE(),INTERVAL 60 DAY) <= total_drill_cnt
I looked this up at http://dev.mysql.com
Would that give me the total drill count for a member for the last 2 months? Once I have total_drill_cnt I need to devide that by number of drills in any 2 month period. We may have had more than 16 but probably not less than.
If drill_id is autoincrement then I could find the difference from max(drill_id) - min(drill_id) or would a datediff function work better here?
As always thanks for any help.
-Donovan
