[SOLVED] Conceptual DB Construction

[Wildbug]

Glad I could help.  Good luck with your project.

[karatekid36]

In thinking more about this project, I wanted to add more complexity to the db design and functionality.  Right now there is only present or absent for the attendance status.  I would really like to change it so that there were more choices such as Present, Excused, Unexcused, and Late.  If there were four events, all mandatory, and a person was present, excused, unexcused, and late, one for each meeting, their attendance record would be 66.66%  They were there for the event where they were present. They were there for the event that they were late too, but it needs to be on record that they were late, the excused event for does not for them, and the event that is unexcused counts as an absence.  Basically, they went to 2 out of 3 events that they were eligible for which leads us to the 66.66%.  Any ideas for how we can go about this.  I thought of this last night and I am at work right now, but I figured I would get the ball rolling online to see if I could help get some ideas before I head home and attack this.  Thanks

[Wildbug]

With the query we built before, the attendance is binary: there is an entry in the attendance table if the member attended, and no entry if they did not attend.  To have a more fine-grained approach to attendance, you'll need to define another column on your attendance table.

 

ALTER TABLE attendance ADD COLUMN colname ENUM('Present', 'Excused', 'Unexcused', 'Late', 'Absent');

 

We'll still use the absence of an entry to signify "absent," but I included an explicit "Absent" condition, just in case.  You'll need to include in the query those conditions you want to use as "present."

[karatekid36]

I was definitely think that another column was needed, but now when it comes to the calculation part it is a tad more difficult.  Before the calculation was based on the total amount of events attended divided by the amount of mandatory events.  Now it is going to be the total number of events attended divided by the amount of event that the person qualifies for.  If they are excused from an event, it will not count against them.  They can still have a 100% attendance record even if they miss an event.  The attendance calculating sql statement will now need to find the total amount of events that person attended and then divide that by the amount of events they were able to attend.  It is not so straight forward any more and it will be different for each person.  I know that we can construct a statement that will be universal.  Now I just have to begin to put it together.

[karatekid36]

This following query is based on the "binary" system that was in place before hand.

$query = "SELECT brothers.user_id, CONCAT(brothers.last_name, \", \",  brothers.first_name
) AS BrotherName, brothers.status, 100 * SUM(attendance.mid IS NOT NULL ) / (
SELECT COUNT( * )
FROM events WHERE TYPE = \"Mandatory\"
AND date
BETWEEN \"2007-06-01\"
AND \"2007-06-31\" 
) AS Attendance
FROM brothers
JOIN events
LEFT JOIN attendance ON (events.event_id = attendance.eid
AND brothers.user_id = attendance.mid )
WHERE events.type = \"Mandatory\"
AND events.date
BETWEEN \"2007-06-01\"
AND \"2007-06-31\"
AND brothers.status = \"Undergraduate\"
GROUP BY brothers.last_name ";

 

The new sql should be something like this right?

count all presents for each member over the given time and divide that by all events where the the member does not have unexcused for an attendance record for the event.  Does this make sense for the new sql statement?

[karatekid36]

I meant excused in my previous post.

[Wildbug]

(I'd suggest GROUPing BY brothers.user_id since that's the only truly unique value in the database; people can have the same surname [and first name].)

 

I think the only thing you'll need to change is to extend the statment that counts attendance.

 

100 * SUM(attendance.type IS NOT NULL AND attendance.type IN ('Present','Late')) / ((SELECT COUNT(*) FROM events WHERE TYPE = 'Mandatory' AND date BETWEEN '2007-06-01' AND '2007-06-31') - SUM(attendance.record = 'Excused')) AS Attendance

[karatekid36]

I need help thinking this out.  Since switching to a non binary attendance system, I have to had to make some adjustments to code, and right now I have seen this code about a million times.  My problem now lies in the calculation of Attendance.  Right now there is three options for a members attendance.  There is Present, Excused, and Unexcused.  If there were only two meetings in the time period we were concerned about and member x was present for meeting one and excused for meeting two, this member would have a 100% attendance record.  If another member were present for meeting one and Unexcused for meeting two, they have a 50% attendance record.  Okay, with that being said, I need a little help with the sql statement that will make this a reality.  As some added complication, this attendance stuff only matters for events/meeting that have a mandatory "status".  My code is posted below, but I am running into some road blocks because if there is only one event during the time period and the person was excused for the one meeting, the math produces the answer of infinity and then will post nothing in the proper column, but in reality their attendance record is 100% because they were excused for only meeting of the time period, which does not count against them.  Also, the attendance measurement in general is not calculating properly when excused and unexcused get tossed into the mix.  I just need a little help thinking this out and ANY assistance would be greatly appreciated.  If you need more clarification feel free to ask. 

"SELECT brothers.user_id, CONCAT(brothers.last_name, \", \",  brothers.first_name
) AS BrotherName, brothers.status, 100 * (SUM(attendance.attend_status IS NOT NULL AND attendance.attend_status IN ('Present','Late','Unexcused') AND TYPE = 'Mandatory')) / ((SELECT COUNT(*) FROM events WHERE TYPE = 'Mandatory' AND date BETWEEN '2007-06-11' AND '2007-06-12') - SUM(attendance.attend_status = 'Excused')) AS Attendance
FROM brothers
JOIN events
LEFT JOIN attendance ON (events.event_id = attendance.eid
AND brothers.user_id = attendance.mid )
WHERE events.type = \"Mandatory\"
AND events.date
BETWEEN \"2007-06-11\"
AND \"2007-06-12\"
AND brothers.status = \"Undergraduate\"
GROUP BY brothers.user_id ORDER BY brothers.last_name";

[Wildbug]

'SELECT
brothers.user_id,
CONCAT(brothers.last_name, ", ",  brothers.first_name) AS BrotherName,
brothers.status,
100 * (SUM(attendance.attend_status IN ("Present","Late","Excused") AND type = "Mandatory")) / (SELECT COUNT(*) FROM events WHERE type = "Mandatory" AND date BETWEEN "2007-06-11" AND "2007-06-12") AS Attendance
FROM brothers
JOIN events
LEFT JOIN attendance ON (events.event_id = attendance.eid AND brothers.user_id = attendance.mid )
WHERE
events.type = "Mandatory"
AND events.date BETWEEN "2007-06-11" AND "2007-06-12"
AND brothers.status = "Undergraduate"
GROUP BY brothers.user_id
ORDER BY brothers.last_name'

 

That should work.

[karatekid36]

Thank you.