Query help

HI,Can someone tell me what is wrong with this query, please?  It should be simple, but I have something wrong. For example, it is bringing up attendance for JoeSmith in the small group, 10th Grade Boys.  However, he is now in 12th Grade not 10th.   ??

SELECT COUNT(core_occurrence_attendance.person_id) AS [Number Attended], core_person.nick_name, core_person.last_name, smgp_group.group_name,
                  MAX(core_occurrence.occurrence_start_time) AS [Date Last Attended], smgp_group_occurrence.group_id
FROM     core_occurrence_attendance INNER JOIN
                  core_person ON core_occurrence_attendance.person_id = core_person.person_id INNER JOIN
                  smgp_group_occurrence ON core_occurrence_attendance.occurrence_id = smgp_group_occurrence.occurrence_id INNER JOIN
                  smgp_group ON smgp_group_occurrence.group_id = smgp_group.group_id INNER JOIN
                  core_occurrence ON smgp_group_occurrence.occurrence_id = core_occurrence.occurrence_id
GROUP BY core_person.nick_name, core_person.last_name, smgp_group.group_name, smgp_group.active, smgp_group_occurrence.group_id
HAVING (smgp_group.active = 1)
ORDER BY smgp_group.group_name 

  • Kim,

    If he was in 10th and never removed he would still show up in 10th, as well as 12th based on your query.
    If he is inactive in previous grades, you could add small group member to your query to limit the results to groups where the person is active in the group.

Reply Children
No Data
More Content