I often get requests from fellow staff members asking for a report about their “Regular” attendees. After I explain to them that SQL doesn’t have a “Regular” key word, and that I need to know exactly what they mean by “regular” they usually go away to think about it and never end up coming back 
.
Unfortunately they’ve begun to figure this out. My latest report request was for a list of the parents of all 2nd –6th graders who have attended services at least twice a month for the previous 6 months, and if and where the parent might be serving. Ok, that’s specific. Now how do I get the list?
Writing the SQL proved to be more challenging than I expected. After a couple iterations, I finally just created a “utility” stored procedure that would accept a list of attendance types, a minimum monthly attendance rate, and the number of months to check the attendance. It then returns a list of people who met that criteria.
Since it might come in handy for others, I share it here…
CREATE PROC [dbo].[cust_ccv_regular_attenders] @OccurrenceTypeIDs varchar(200), @MonthsBack int, @MinMonthlyAttendance int AS /******************************************************************** * cust_ccv_regular_attenders * * Procedure to return list of people who have attended specific * occurrence type(s) a specific number of times each month for a * specific number of months back. * * Parameters: * @OccurrenceTypeIDs - Comma delimeted list of occurrence type * ID's to check * @MonthsBack - Number of months back that minimum attendance * has to have occurred * @MinMonthlyAttendance - The number of times each month that * person needs to have attended * *********************************************************************/ BEGIN DECLARE @Dates varchar(max) SET @Dates = '' DECLARE @Where varchar(max) SET @Where = '' DECLARE @Date datetime SET @Date = CAST(MONTH(GETDATE()) AS varchar) + '/1/' + CAST(YEAR(GETDATE()) AS varchar) SET @Date = DATEADD(month, -1, @Date) DECLARE @Month int SET @Month = -1 WHILE @Date > DATEADD(month, (0 - (@MonthsBack + 1)), GETDATE()) BEGIN IF @Dates <> '' SET @Dates = @Dates + ',' SET @Dates = @Dates + '[' + CAST(MONTH(@Date) AS varchar) + '/1/' + CAST(YEAR(@Date) AS varchar) + ']' IF @Where = '' SET @Where = ' WHERE ' ELSE SET @Where = @Where + ' AND ' SET @Where = @Where + '[' + CAST(MONTH(@Date) AS varchar) + '/1/' + CAST(YEAR(@Date) AS varchar) + '] >= ' + CAST(@MinMonthlyAttendance AS varchar) SET @Date = DATEADD(month, -1, @Date) END DECLARE @Sql nvarchar(max) SET @Sql = 'WITH CTE AS ( SELECT * FROM ( SELECT OA.person_id, CAST(MONTH(service_time) AS varchar) + ''/1/'' + CAST(YEAR(service_time) AS varchar) as service_month FROM core_occurrence_attendance OA INNER JOIN core_v_occurrence_service O ON O.occurrence_id = OA.occurrence_id WHERE O.occurrence_type_id in (' + @OccurrenceTypeIDs + ') AND OA.attended = 1 AND OA.person_id IS NOT NULL ) MS PIVOT ( COUNT(service_month) FOR service_month IN (' + @Dates + ') ) AS pvt ) SELECT person_id FROM CTE ' + @Where --SELECT @Sql EXEC sp_executesql @Sql; END
Since that is now just a utility procedure, I needed another procedure to specify the elementary age occurrence types, join to the parents and find their serving areas…
CREATE PROC cust_ccv_sp_elementary_reg_attender_parents AS DECLARE @RegularAttenders table ( person_id int primary key not null ) INSERT INTO @RegularAttenders EXEC [cust_ccv_regular_attenders] '40,41,43,68,69', 6, 2 SELECT DISTINCT P.*, dbo.core_funct_profile_name_list(P.person_id, 2) AS Serving FROM @RegularAttenders RA INNER JOIN core_family_member C ON C.person_id = RA.person_id AND C.role_luid = 31 INNER JOIN core_family_member A ON A.family_id = C.family_id AND A.role_luid = 29 INNER JOIN core_v_merge_fields P ON P.person_id = A.person_id ORDER BY P.last_name, P.nick_name
I created a new page in Arena that used the ReportGridFromQuery module, pointed it to this procedure, and now the elementary kids staff member can track down all the parents of their “regular” attendees who are not already serving and hit them up to serve in their area.
