Skip to Content
0
Former Member
Sep 22, 2009 at 11:08 AM

MySQL Select Statement Help Required

24 Views

I am trying to generate a report in VS 2008 (C#) using a mysql select statement but cannot get it right.

I have groups that meet on a weekly basis on different days. I want to generate a report that shows me all the members that have not attended their group where they have missed 3 meetings in a row.

Below is the select statement I have tried but it does not give me the results I am looking for. I have tried to look at all the meetings in a 4 week period but would prefer to look at the last 3 meetings that are recorded. Some groups might not record a meeting every week. So I want to look at the last 3 recorded meetings and count each members attendance and only report on the members with more than 3 meetings missed.

SELECT COUNT(`groupattendance`.`Attended`) AS Attendance, `smallgroupform`.`MeetingDate`, `userinfo`.`FirstName`, `userinfo`.`Surname`, `smallgroup`.`GroupName`, `groupattendance`.`Attended`, `groupattendance`.`UserID`, `groupattendance`.`GroupID`

FROM ((`anatomy`.`groupattendance` `groupattendance`

INNER JOIN `anatomy`.`smallgroupform` `smallgroupform` ON `groupattendance`.`FormID` = `smallgroupform`.`FormID`)

INNER JOIN `anatomy`.`userinfo` `userinfo` ON `groupattendance`.`UserID` = `userinfo`.`UserID`)

INNER JOIN `anatomy`.`smallgroup` `smallgroup` ON `groupattendance`.`GroupID` = `smallgroup`.`GroupID`

WHERE (`smallgroupform`.`MeetingDate` >= DATE_SUB(CURDATE(),INTERVAL 4 WEEK) AND `smallgroupform`.`MeetingDate` <= CURDATE()) AND `groupattendance`.`Attended` = 'False'

GROUP BY `userinfo`.`UserID`

HAVING Attendance >= 3

Thanks,

Garth.