Query Help, Please

Hello, could someone help me think through a report need?  My bosses want to know which of our small group members (Sunday School) who were attending in 2016 are still attending now, and who are not.  As I was starting to work on it, this question came to my mind.  How can I capture people who are still in Arena as members but who don't have a group assigned now, because I removed them from a group when they stopped attending?  They won't show up in a group now to compare to 2016, and they won't show up if I include inactive people because their overall record status is active because they are still church members.    

  • Unless there has been a change in code that I am not aware of, you can tie Person_ID from [dbo].[core_occurrence_attendance] to [dbo].[core_person] IF the person was ever marked Present in that Occurrence. Again this will only show records that were marked Present, otherwise they will not be tied to the occurrence. 

  • -- 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...

More Content