-- In-order for this to work your members must have been checking into class and the Attend flag get set.
-- if they have not been setting the attended flag let me know, we will have to change query to just look at group_member and core_occrrence.
WITH
group_occurrence AS
(SELECT DISTINCT
gm.person_id
FROM dbo.smgp_member gm
LEFT OUTER JOIN dbo.smgp_group_occurrence sgo ON gm.group_id = sgo.group_id
LEFT OUTER JOIN dbo.core_occurrence co ON sgo.occurrence_id = co.occurrence_id
LEFT OUTER JOIN dbo.core_occurrence_attendance coa ON co.occurrence_id = coa.occurrence_id AND gm.person_id = coa.person_id
WHERE co.occurrence_start_time < '1/1/2017'
AND coa.attended = 1
)
-- Note the power of left outer join... if cp.person id is not in goc (goc.person_id is null) it has not had an occurrence associated with it.
-- the with is needed because you combine the two and the where clause for occrrence would kill the affects of the left outer join.
SELECT
cp.*
FROM dbo.core_person cp
LEFT OUTER JOIN group_occurrence goc ON cp.person_id = goc.person_id
WHERE goc.person_id IS NOT NULL
--AND cp.member_status = --lookup_id for lookup member status
--AND cp.record_status = -- 0 or 1 etc...