Question

Photo of Jackie Powers

0

Weekly Attendance Names

2

I send out a weekly e-mail to people who attended our service. I do not want to send it to people who have not attended less than once in the last 3 weeks. Is there a way to create a data view that will only display those who were here the last Sunday. I have tried using the recent attendance filter but it won't display anything less than the last 16 weeks. 

  • Photo of Rock RMS

    1

    The 'Recent Attendance' filter does appear to have an issue with persisting the number of weeks and has been reported here. 

    Here's a query that will report an attendance summary for the last 16 weeks, however you can change the @WeeksBack to any number of weeks ( e.g. 3 )...

    DECLARE @WeeksBack int = 16;
DECLARE @LastSunday datetime = dbo.ufnUtility_GetPreviousSundayDate()
;WITH 
    CT1 AS
        (
            -- The weekends each person attended
            SELECT DISTINCT 
                PA.[PersonId],
                dbo.ufnUtility_GetSundayDate(A.[StartDateTime]) AS [Weekend]
            FROM [Attendance] A
            INNER JOIN [PersonAlias] PA
                ON PA.[Id] = A.[PersonAliasId]
            WHERE A.[DidAttend] = 1
        ),
    CT2 AS
        (
            -- Attendance summary for each person during the selected number of weeks back
            SELECT 
                [PersonId],
                COUNT([Weekend]) AS [Times Attended],
                MAX([Weekend]) AS [Last Attended]
            FROM CT1
            WHERE [Weekend] >= DATEADD( WEEK, ( 0 - @WeeksBack ), @LastSunday )
            GROUP BY [PersonId]
        )


SELECT
    P.[Id],
    P.[NickName] + ' ' + [LastName] AS [Person],
    CT2.[Times Attended],
    CT2.[Last Attended],
    CASE WHEN CT2.[Last Attended] = @LastSunday
        THEN CAST( 1 as bit )
        ELSE CAST( 0 as bit )
    END AS [Attended Last Weekend]
FROM CT2
INNER JOIN [Person] P
    ON P.[Id] = CT2.[PersonId]
ORDER BY P.[LastName], P.[NickName]
  • Photo of Michael Garrison

    0

    Interesting- I see what you're seeing- specifically if I set the "Recent Attendance" to, say, "1" time in the last "1" weeks, as soon as I hit "Preview" or "Save" it reverts back to "16" weeks.

    I WAS able to get - kind of - the list you're looking for by setting the Data View to "Apply To" "Attendance" rather than "Person"- then I can set up filters setting "Greater than or Equal To" Saturday, and "Did attend" to "True" - that gives me a list of everyone who attended this weekend. HOWEVER it doesn't give me their names, only their IDs. I don't see a way in either the data view or in a report based on that data view to transform that into a more "friendly" list of people.

    @ROCK: is this "must be 16 weeks" thing a bug?

    In the meantime, you can create a "Dynamic Data View" block on any page you wish (I recommend creating a dedicated new "Full Width" type page under one of the sidebar sections) using the following SQL code- this creates a list of everyone who has checked in on or after January 17th of this year (Saturday), which for the moment is "this weekend". Obviously you'll have to edit that date each week. Or if you want to get more advanced, you could allow this to be set using a drop-down or presumably a date selector control...

    SELECT
    P.[Id] AS [Id],
    P.[NickName] + ' ' + P.[LastName] AS [Name]
        FROM
            [Attendance] A
        INNER JOIN
            [Person] P ON P.[Id] = A.[PersonAliasId]
        WHERE
            A.[DidAttend]=1
        AND
            A.[CreatedDateTime]>='20150117'
    • Michael Garrison

      Michael Garrison

      I should have added, you can use the "Hide Column" option to hide the "Id" column. And make sure "Person Report" is checked, in case you want to click on their name and get taken to their profile. "Dynamic Data View" blocks like this work pretty much like a Data View, in that it has export, bulk update, merge and (most importantly for you) communicate buttons at the bottom of the list.