David Turner

Assignment Types Performance

David Turner — Tue, 12 Oct 2010 23:13:06 +0000

I mentioned improving performance in Arena Assignment Types during my RefreshCache presentation. It was actually just a stored procedure change. Here’s the change if you’d like to see if it helps your performance…

ALTER PROC [dbo].[asgn_sp_get_assignmentTypeList] 
( 
    @ParentAssignmentTypeID int 
    , @PersonID int 
) 
AS 
 
DECLARE @hierarchy TABLE 
( 
      assignment_type_id int 
    , root_assignment_type_id int 
    , total_types int 
    , total_assignments int 
    , active_assignments int 
    , pastdue_assignments int 
); 
 
with AssignmentTypeHierarchy 
as 
( 
    -- root 
    select 
          assignment_type_id 
        , root_assignment_type_id = NULL 
    from asgn_assignment_type t WITH(NOLOCK) 
    where isnull(t.parent_assignment_type_id, -1) = @ParentAssignmentTypeID 
 
    union all 
 
    -- recursive part 
    select 
          t.assignment_type_id 
        , root_assignment_type_id = 
            case 
                when ath.root_assignment_type_id is null then ath.assignment_type_id 
                else ath.root_assignment_type_id 
            end 
    from asgn_assignment_type t WITH(NOLOCK) 
    inner join AssignmentTypeHierarchy ath on t.parent_assignment_type_id = ath.assignment_type_id 
) 
 
INSERT INTO @hierarchy 
( 
      assignment_type_id 
    , root_assignment_type_id 
    , total_types 
    , total_assignments 
    , active_assignments 
    , pastdue_assignments 
) 
SELECT 
      assignment_type_id = ath.assignment_type_id 
    , root_assignment_type_id = ath.root_assignment_type_id 
    , total_types = 
        ( 
            SELECT COUNT(*) 
            FROM asgn_assignment_type t2 WITH(NOLOCK) 
            WHERE t2.parent_assignment_type_id = ath.assignment_type_id 
        ) 
    , total_assignments = 
        ( 
            SELECT COUNT(*) 
            FROM asgn_assignment a WITH(NOLOCK) 
            WHERE a.assignment_type_id = ath.assignment_type_id 
        ) 
    , active_assignments = 
        ( 
            SELECT COUNT(*) 
            FROM asgn_assignment a WITH(NOLOCK) 
            WHERE a.assignment_type_id = ath.assignment_type_id 
            AND a.active = 1 
        ) 
    , pastdue_assignments = 
        ( 
            SELECT COUNT(*) 
            FROM asgn_assignment a WITH(NOLOCK) 
            WHERE a.assignment_type_id = ath.assignment_type_id 
            AND a.active = 1 
            AND a.due_date <> '1/1/1900' 
            AND DATEDIFF(d, a.due_date, GETDATE()) > 0 
        ) 
FROM AssignmentTypeHierarchy ath 
 
SELECT 
      t.* 
    , assignment_type_count = 
        ( 
            ISNULL((SELECT SUM(total_types) 
            FROM @hierarchy h 
            WHERE (h.root_assignment_type_id = t.assignment_type_id 
                OR h.assignment_type_id = t.assignment_type_id )), 0) 
        ) 
    , disable_delete = 
        CASE WHEN ( 
                ISNULL((SELECT SUM(total_assignments) 
                FROM @hierarchy h 
                WHERE (h.root_assignment_type_id = t.assignment_type_id 
                    OR h.assignment_type_id = t.assignment_type_id )), 0) 
                ) > 0 
            THEN convert(bit, 1) 
            ELSE convert(bit, 0) END 
    , assignment_count = 
        ( 
            ISNULL((SELECT SUM(total_assignments) 
            FROM @hierarchy h 
            WHERE (h.root_assignment_type_id = t.assignment_type_id 
                OR h.assignment_type_id = t.assignment_type_id )), 0) 
        ) 
    , assignment_active_count = 
        ( 
            ISNULL((SELECT SUM(active_assignments) 
            FROM @hierarchy h 
            WHERE (h.root_assignment_type_id = t.assignment_type_id 
                OR h.assignment_type_id = t.assignment_type_id )), 0) 
        ) 
    , assignment_pastdue_count = 
        ( 
            ISNULL((SELECT SUM(pastdue_assignments) 
            FROM @hierarchy h 
            WHERE (h.root_assignment_type_id = t.assignment_type_id 
                OR h.assignment_type_id = t.assignment_type_id )), 0) 
        ) 
    , subscribed = 
        case when s.assignment_type_id is not null 
            then convert(bit, 1) 
            else convert(bit, 0) 
        end 
    , owner_name = p.last_name + ', ' + CASE WHEN LEN(p.nick_name) = 0 THEN p.first_name ELSE p.nick_name END 
    , owner_guid = p.guid 
    , owner_blob_id = p.blob_id 
from asgn_assignment_type t WITH(NOLOCK) 
left outer join core_person p WITH(NOLOCK) 
    on t.owner_person_id = p.person_id 
left outer join asgn_assignment_type_subscribe s WITH(NOLOCK) 
    on s.assignment_type_id = t.assignment_type_id 
    and s.person_id = @PersonID 
where isnull(t.parent_assignment_type_id, -1) = @ParentAssignmentTypeID 
order by t.assignment_order 

DISCLAIMER: Install at your own risk, and keep in mind that it will be overwritten on an Arena upgrade.

Alerts

David Turner — Thu, 11 Mar 2010 15:57:25 +0000

As Arena Administrators, we like to know of problems before our users do. To that end we created an “Alerts” module. We’ve placed this module on our Arena home page and secured it so that only Arena Administrators can see it. It tests for various conditions and when problems occur, it will display an “alert.” In this example, you can see that we have a large number of communications that have not gone out.

This module uses a custom table to store the alert messages and a few custom stored procedures. We store the alert messages so that they don’t need to be calculated on every view of the module. The module has a configurable cache duration and alerts are only processed if that cache duration has expired (or user clicks the “refresh” link).

Currently our procedure to process alerts is only checking for queued emails and for serving reminders being created, but it could be modified to check for anything that is important to you as an administrator.

You can download the user control and SQL script here.

eRA

David Turner — Thu, 08 Oct 2009 18:21:08 +0000

During RefreshCache, Jon showed a lot of the stuff we’ve been doing with eRA (estimated regular attendees). Here is how we capture that data…

dtmt_estimated_regular_attendees

This is the table that stores all the data. To track the eRA wins and losses, we need to know at any point in time, whether a family is considered an eRA.

CREATE TABLE [dbo].[dtmt_estimated_regular_attendees](
    [weekend_date] [datetime] NOT NULL,
    [family_id] [int] NOT NULL,
    [times_attended_last_16_weeks] [int] NULL,
    [first_attended] [datetime] NULL,
    [last_attended] [datetime] NULL,
    [times_gave_last_6_weeks] [int] NULL,
    [times_gave_total] [int] NULL,
    [last_gave] [datetime] NULL,
    [regular_attendee] [bit] NOT NULL,
    [regular_attendee_c] [bit] NOT NULL,
    [regular_attendee_g] [bit] NOT NULL,
 CONSTRAINT [PK_dtmt_estimated_regular_attendees] PRIMARY KEY CLUSTERED 
(
    [weekend_date] ASC,
    [family_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[dtmt_estimated_regular_attendees] ADD  CONSTRAINT [DF_cust_ccv_estimated_regular_attendees_regular_attendee]  DEFAULT ((0)) FOR [regular_attendee]
GO
 
ALTER TABLE [dbo].[dtmt_estimated_regular_attendees] ADD  CONSTRAINT [DF_dtmt_estimated_regular_attendees_regular_attendee_c]  DEFAULT ((0)) FOR [regular_attendee_c]
GO
 
ALTER TABLE [dbo].[dtmt_estimated_regular_attendees] ADD  CONSTRAINT [DF_dtmt_estimated_regular_attendees_regular_attendee_g]  DEFAULT ((0)) FOR [regular_attendee_g]
GO
 
 

cust_ccv_sp_dtmt_loadEstimatedRegularAttendees

Every day we run this SQL proc (configured as an agent job) to load the most recent weekends numbers. When looking at attendance, we include attendance from children’s check-in that occurs at the same time that an adult service takes place (which is why we exclude high school attendance).

ALTER PROC [dbo].[cust_ccv_sp_dtmt_loadEstimatedRegularAttendees] 
@CurrentDate datetime
 
AS
 
BEGIN
 
--DECLARE @CurrentDate datetime
--SET @CurrentDate = GETDATE()
 
    DECLARE @SaturdayDate DATETIME
    SET @SaturdayDate = dbo.util_funct_saturday(@CurrentDate)
 
    DELETE dtmt_estimated_regular_attendees
    WHERE weekend_date = @SaturdayDate;
 
    ----/****************************************************************************
        
    ----    ATTENDANCE      
    ----    Calculate the number of times each family has attended in the last 16
    ----    weeks and when they last attended based on children's attendance records.
        
    ----*****************************************************************************/
    INSERT INTO dtmt_estimated_regular_attendees (
        weekend_date,
        family_id,
        times_attended_last_16_weeks)
    SELECT 
        @SaturdayDate,
        fm.family_id,
        COUNT(DISTINCT O.service_time)
    FROM core_occurrence_attendance OA
    INNER JOIN core_v_occurrence_service O ON O.occurrence_id = OA.occurrence_id
    INNER JOIN core_family_member FM ON FM.person_id = OA.person_id
    WHERE O.is_service = 1
    AND OA.attended = 1
    AND O.occurrence_type_id <> 6 -- High School
    AND service_time <= @SaturdayDate
    AND service_time >= DATEADD(week, -15, @SaturdayDate)
    GROUP BY fm.family_id
 
    /****************************************************************************
        
        GIVING      
        Calculate the number of times each family has given in the last 6 weeks,
        total times they've given and when the last time they gave was
        
    *****************************************************************************/
    DECLARE @FamilyContributionCount TABLE (
        family_id [int] not null,
        total_count [int] null,
        recent_count [int] null,
        last_gave [datetime] null
    );
 
    with cte
    as
    (
        SELECT 
            weekend_date,
            family_id,
            case when weekend_date >= DATEADD(week, -5, @SaturdayDate) 
                then 1 else 0 end as recent
        FROM cust_ccv_v_family_giving_weekend
        WHERE weekend_date <= @SaturdayDate
        AND weekend_date > '1/1/2005'
    )
    
    -- Insert Total Count into temporary table    
    INSERT INTO @FamilyContributionCount 
    SELECT 
        family_id,
        COUNT(*),
        SUM(recent),
        MAX(weekend_date)
    FROM cte
    GROUP BY family_id
 
    --Update Existing Records
    UPDATE RA
    SET 
        times_gave_last_6_weeks = FCC.recent_count,
        times_gave_total = FCC.total_count,
        last_gave = FCC.last_gave
    FROM @FamilyContributionCount FCC
    INNER JOIN dtmt_estimated_regular_attendees RA
        ON RA.family_id = FCC.family_id
        AND RA.weekend_date = @SaturdayDate
    
    -- Create New Records
    INSERT INTO dtmt_estimated_regular_attendees (
        weekend_date,
        family_id,
        times_gave_last_6_weeks,
        times_gave_total,
        last_gave)
    SELECT 
        @SaturdayDate,
        FCC.family_id,
        FCC.recent_count,
        FCC.total_count,
        FCC.last_gave
    FROM @FamilyContributionCount FCC
    LEFT OUTER JOIN dtmt_estimated_regular_attendees RA
        ON RA.family_id = FCC.family_id
        AND RA.weekend_date = @SaturdayDate
    WHERE RA.family_id IS NULL
    
    -- Calculate Attendance
    UPDATE CW
    SET 
        CW.first_attended = dbo.cust_ccv_funct_family_first_attended (CW.family_id),
        CW.last_attended = dbo.cust_ccv_funct_family_last_attended (CW.family_id, CW.weekend_date),
        CW.regular_attendee = 
            CASE WHEN LW.regular_attendee IS NULL OR LW.regular_attendee = 0
            THEN
                -- In ?
                CASE WHEN (CW.times_gave_total >= 3 AND CW.times_gave_last_6_weeks >= 1)
                OR CW.times_attended_last_16_weeks >= 8 
                THEN 1 ELSE 0 END
            ELSE
                -- Out ?
                CASE WHEN DATEADD(week, 8, CW.last_gave) < CW.weekend_date
                AND DATEADD(week, 4, CW.last_attended) < CW.weekend_date
                AND CW.times_attended_last_16_weeks < 8
                THEN 0 ELSE 1 END
            END
    FROM dtmt_estimated_regular_attendees CW
    LEFT OUTER JOIN dtmt_estimated_regular_attendees LW
        ON CW.family_id = LW.family_id
        AND CW.weekend_date = DATEADD(week, 1, LW.weekend_date)
    where CW.weekend_date = @SaturdayDate
 
    -- Calculate Giving/Checking component of regular attendees
    UPDATE dtmt_estimated_regular_attendees
    SET 
        regular_attendee_g = 
            CASE WHEN DATEADD(week, 8, last_gave) >= weekend_date
            THEN 1 ELSE 0 END,
        regular_attendee_c = 
            CASE WHEN DATEADD(week, 4, last_attended) >= weekend_date 
                OR times_attended_last_16_weeks >= 8
            THEN 1 ELSE 0 END
    WHERE weekend_date = @SaturdayDate
    AND regular_attendee = 1
            
END

Initial Load

To initially load this table with past data we ran this script (and let it run for a while).

DECLARE @WeekendDate datetime
SET @WeekendDate = dbo.util_funct_saturday('01/01/2006')
 
WHILE @WeekendDate < GETDATE()
BEGIN
 
    EXEC [cust_ccv_sp_dtmt_loadEstimatedRegularAttendees] @WeekendDate
    SET @WeekendDate = DATEADD(week, 1, @WeekendDate)
    
END
 

Loss Report

Jon mentioned the report that shows us each week who the eRA losses were. This proc will list families that became an eRA loss during any two dates.

CREATE PROC [dbo].[cust_ccv_sp_dtmt_loss_report]
@FromDate datetime,
@ToDate datetime
 
AS
 
SET @FromDate = dbo.util_funct_saturday(@FromDate)
SET @ToDate = dbo.util_funct_saturday(@ToDate)
 
select p.*,
    tw.last_attended as [Last Attended],
    tw.last_gave as [Last Gave]
from dtmt_estimated_regular_attendees tw
inner join core_v_merge_fields p on p.person_id = dbo.core_funct_familyHead2(tw.family_id)
left outer join dtmt_estimated_regular_attendees lw 
    on tw.family_id = lw.family_id
    and lw.weekend_date = @FromDate
where tw.weekend_date = @ToDate
and lw.regular_attendee = 1
and tw.regular_attendee = 0

Trending View

This is a view that feeds some of our charts. It groups totals by weekend and shows the difference between the current weekend and the weekend 4 weeks prior. (four week rolling average).

CREATE VIEW [dbo].[cust_ccv_v_dtmt_era_trend]
 
AS
 
with cte
as
(
    select 
        tw.weekend_date,
        tw.family_id,
 
        case when lw.regular_attendee = 1 then 1 else 0 end as eRA_1,
        case when tw.regular_attendee = 1 then 1 else 0 end as eRA_2,
        case when tw.regular_attendee = 1 and (lw.regular_attendee is null or lw.regular_attendee = 0)
            then 1 else 0 end as eRA_add,
        case when lw.regular_attendee = 1 and (tw.regular_attendee is null or tw.regular_attendee = 0)
            then 1 else 0 end as eRA_loss,
 
        case when lw.regular_attendee_g = 1 then 1 else 0 end as eRAg_1,
        case when tw.regular_attendee_g = 1 then 1 else 0 end as eRAg_2,
        case when tw.regular_attendee_g = 1 and (lw.regular_attendee_g is null or lw.regular_attendee_g = 0)
            then 1 else 0 end as eRAg_add,
        case when lw.regular_attendee_g = 1 and (tw.regular_attendee_g is null or tw.regular_attendee_g = 0)
            then 1 else 0 end as eRAg_loss,
 
        case when lw.regular_attendee_c = 1 then 1 else 0 end as eRAc_1,
        case when tw.regular_attendee_c = 1 then 1 else 0 end as eRAc_2,
        case when tw.regular_attendee_c = 1 and (lw.regular_attendee_c is null or lw.regular_attendee_c = 0)
            then 1 else 0 end as eRAc_add,
        case when lw.regular_attendee_c = 1 and (tw.regular_attendee_c is null or tw.regular_attendee_c = 0)
            then 1 else 0 end as eRAc_loss,
 
        case when tw.times_attended_last_16_weeks is not null
            then 1 else 0 end as attended,
        case when tw.times_attended_last_16_weeks >= 13 and tw.times_attended_last_16_weeks <= 16 
            then 1 else 0 end as attendance_grade_a,
        case when tw.times_attended_last_16_weeks >= 9 and tw.times_attended_last_16_weeks <= 12
            then 1 else 0 end as attendance_grade_b,
        case when tw.times_attended_last_16_weeks >= 5 and tw.times_attended_last_16_weeks <= 8
            then 1 else 0 end as attendance_grade_c,
        case when tw.times_attended_last_16_weeks >= 1 and tw.times_attended_last_16_weeks <= 4 
            then 1 else 0 end as attendance_grade_d
                    
    from dtmt_estimated_regular_attendees tw
    left outer join dtmt_estimated_regular_attendees lw 
        on tw.family_id = lw.family_id
        and tw.weekend_date = DATEADD(week, 4, lw.weekend_date)
    WHERE DATEADD(week, -4, tw.weekend_date) IN (
        SELECT DISTINCT weekend_date 
        FROM dtmt_estimated_regular_attendees
        WHERE regular_attendee = 1 )
)
 
select 
    weekend_date,
 
    sum(eRA_1) as eRA_1,
    sum(eRA_2) as eRA_2,
    sum(eRA_2) - sum(eRA_1) as eRA_gain,
    cast((sum(eRA_2) - sum(eRA_1)) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRA_gain_percent,    
    sum(eRA_add) as eRA_add,
    cast(sum(eRA_add) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRA_add_percent,    
    sum(eRA_loss) as eRA_loss,
    cast(sum(eRA_loss) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRA_loss_percent,
 
    sum(eRAg_1) as eRAg_1,
    sum(eRAg_2) as eRAg_2,
    sum(eRAg_2) - sum(eRAg_1) as eRAg_gain,
    cast((sum(eRAg_2) - sum(eRAg_1)) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAg_gain_percent,    
    sum(eRAg_add) as eRAg_add,
    cast(sum(eRAg_add) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAg_add_percent,    
    sum(eRAg_loss) as eRAg_loss,
    cast(sum(eRAg_loss) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAg_loss_percent,
 
    sum(eRAc_1) as eRAc_1,
    sum(eRAc_2) as eRAc_2,
    sum(eRAc_2) - sum(eRAc_1) as eRAc_gain,
    cast((sum(eRAc_2) - sum(eRAc_1)) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAc_gain_percent,    
    sum(eRAc_add) as eRAc_add,
    cast(sum(eRAc_add) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAc_add_percent,    
    sum(eRAc_loss) as eRAc_loss,
    cast(sum(eRAc_loss) as decimal(9,2))/cast(sum(eRA_1) as decimal(9,2)) as eRAc_loss_percent,
 
    sum(attended) as attended,
    sum(attendance_grade_a) as attendance_grade_a,
    cast(sum(attendance_grade_a) as decimal(9,2))/cast(sum(attended) as decimal(9,2)) as attendance_grade_a_percent,
    sum(attendance_grade_b) as attendance_grade_b,
    cast(sum(attendance_grade_b) as decimal(9,2))/cast(sum(attended) as decimal(9,2)) as attendance_grade_b_percent,
    sum(attendance_grade_c) as attendance_grade_c,
    cast(sum(attendance_grade_c) as decimal(9,2))/cast(sum(attended) as decimal(9,2)) as attendance_grade_c_percent,
    sum(attendance_grade_d) as attendance_grade_d,
    cast(sum(attendance_grade_d) as decimal(9,2))/cast(sum(attended) as decimal(9,2)) as attendance_grade_d_percent
 
from cte
group by weekend_date
--order by weekend_date desc
 
 
GO
 
 

First/Last Attended

These functions for determining when a family first and last attended are used by the proc above.

CREATE function [dbo].[cust_ccv_funct_family_first_attended] (@FamilyID int)
returns datetime
 
as
begin
 
    DECLARE @FirstAttended datetime 
    
    SET @FirstAttended = (
        SELECT MIN(O.occurrence_start_time) AS first_attended
        FROM core_family_member FM
        INNER JOIN core_occurrence_attendance OA  ON OA.person_id = FM.person_id
        INNER JOIN core_occurrence O ON O.occurrence_id = OA.occurrence_id
        INNER JOIN core_occurrence_type OT ON OT.occurrence_type_id = O.occurrence_type
        WHERE FM.family_id = @FamilyID
        AND OA.attended = 1
        AND OT.is_service = 1
        AND OT.occurrence_type_id <> 6 ) -- High School
 
    IF @FirstAttended IS NOT NULL
        SET @FirstAttended = dbo.util_funct_saturday(@FirstAttended)
        
    RETURN @FirstAttended
 
end
 
 
CREATE function [dbo].[cust_ccv_funct_family_last_attended] (@FamilyID int, @WeekendDate datetime)
returns datetime
 
as
begin
 
    DECLARE @LastAttended datetime 
    
    SET @LastAttended = (
        SELECT MAX(O.occurrence_start_time) AS last_attended
        FROM core_family_member FM
        INNER JOIN core_occurrence_attendance OA  ON OA.person_id = FM.person_id
        INNER JOIN core_occurrence O ON O.occurrence_id = OA.occurrence_id
        INNER JOIN core_occurrence_type OT ON OT.occurrence_type_id = O.occurrence_type
        WHERE FM.family_id = @FamilyID
        AND dbo.util_funct_mergedDate(O.occurrence_start_time,6) <= @WeekendDate
        AND OA.attended = 1
        AND OT.is_service = 1
        AND OT.occurrence_type_id <> 6 ) -- High School
        
    RETURN @LastAttended
 
end

Aggregate Type

David Turner — Tue, 01 Sep 2009 20:14:44 +0000

As you begin to use metrics more, you may come across the need to summarize some of the data that gets collected in metrics. Fortunately, there is a little hidden attribute on each metric called Aggregate Type. While aggregate type isn’t really used by the Arena UI, it can be useful in various reporting scenarios.

Say you want to create a monthly summary report that displays metric data summarized for the last 12 months. If you have a metric for weekend attendance, and another metric for baptisms, you’d probably want to summarize those numbers differently when reporting monthly numbers. For example, for attendance, you’d want to report the maximum number (weekend with the greatest attendance), but for baptisms, you’d probably want the total number (sum) during the month. That’s where aggregate type comes in.

By default, you will not see an Aggregate field when editing metrics. You’ll need to turn on the “Show Aggregate Field” module setting on the Metric Chart module. Once you’ve turned this setting on, you’ll be able to set aggregate types for each metric. Possible values are Average, Total, Maximum, and Minimum.

Once you’re using aggregate types, you can then take advantage of a couple SQL functions when writing custom reports…

mtrc_get_aggregate_value
When you pass this function a metric id and start and end date, it will return a value for that metric based on the aggregate type. For example, if the aggregate type is Maximum, then the largest metric value between the start and end dates is returned. If the aggregate type is Average, then the average of all metric values between the dates is returned. etc. etc.

mtrc_get_monthly_values
When you pass this function a parent metric id and a start year, and month, it will return a table of all the child metrics and their respective aggregate values for the following 12 months. This function uses the previous function to determine the aggregate values for each metric and month.

We use both of these functions to generate the type of report that Jon blogged about here.

“Regular” Attendees

David Turner — Mon, 31 Aug 2009 17:58:30 +0000

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.

A New Blog

David Turner — Thu, 16 Jul 2009 14:46:17 +0000

I’ve decided to create a new blog for the family/personal side of things. Mixing work (Arena, CCV) and family stuff on the same blog just hasn’t been working for me. They say you need to know your audience, and I really have two completely different audiences reading this blog, so I find myself not posting at all because anything I’d post has no relevance to half the people reading.

So, for all of you that really have no interest in the latest Arena tip, or module, or what I’m working on at CCV… stop reading this blog, and head over to www.azturners.com. We’re getting close to Hannah’s brain surgery, and I will probably be posting more on the azturners site over the next several weeks. I’ve also moved all my family related posts (and comments) from this site over to the azturners site.

So stay tuned here if your interested in my work with Arena, CCV, etc.

Arena as a CMS

David Turner — Mon, 15 Jun 2009 23:34:14 +0000

Earlier today, I received an email asking what functionality Arena provides when used to drive a church’s public website. I thought I’d share my response…

Content Management System (CMS)

Arena is a CMS. It may not be the fanciest and easiest to use CMS out there, but it provides all the functionality for configuring portals, templates, pages, modules, security, content, etc. And by using templates, you can create an Arena driven site to look anyway you’d like. The look and functionality of your site is only limited by the creativity of your designer. You can be as “Web 2.0” and as you’d like using Arena templates.

The biggest advantage of using Arena as your CMS, however, is it’s integration with all the information you already have in the Arena membership system and the modules that have been created to take advantage of that information.

Promotions

The most powerful area of integration with Arena is promotions. Church staff can add promotions in Arena that are categorized, prioritized and approved and will then automatically be displayed on your public website.

For example Canyon Ridge has configured a promotion display module to display the top three “All Church” promotions at the bottom of their home page…

There are several types of promotion display modules, each of which display your promotions in different ways. A Flash module is even provided for animating Arena promotions. An example of the Flash module can be seen on this CCV Stars page.

With promotions, the “web guy” doesn’t have to worry about adding or removing content when appropriate. As soon as a promotion’s date has passed, it is automatically removed from the site. Promotions can also be configured for different ministry areas (Men’s, Women’s Singles, etc.) and the website would show just promotions specific to those areas on their respective web pages.

But There’s More…

In addition to promotions, Arena can also drive the following functionality on your public site:

Small Group Leader Toolbox (lay leaders can manage groups)
Group Finder (several options including geographically based, affinity based, etc.)
Online Giving
Event Calendar
Event Registration
Prayer Requests
Personalized Subscriptions to promotions (through RSS or Email)
Online Folder/File browser for securing and sharing digital content
Podcasting/Blogging
Classifieds (One of the most visited areas of CCV’s site)
ECards
Newsletters
Polls
Serving Opportunities
Sports Team information (Team info, standings, etc)
Photo Albums
Staff Pages
Employment Opportunities

So, if you’re using Arena as your ChMS and not as your CMS, your going to miss out on a lot of built-in integration.

Staff Anniversaries

David Turner — Thu, 28 May 2009 11:40:18 +0000

For a long time, we’ve been putting our staff anniversaries on our Arena home page each month…

I’m ashamed to say that until now, our department admin has been doing this manually each month. I also noticed that she recently started changing the highlighting each day to indicate which anniversaries in the month had already passed. Obviously a lot of manual work for what can easily be automated.

Since we’re tracking all this information in Arena, this was a perfect scenario for the HTML from Stored Procedure module.

First a stored procedure is needed to generate the HTML…

CREATE PROC [dbo].[cust_ccv_sp_staff_anniversaries]
@HireDateAttrID int,
@PositionAttrID int
AS

DECLARE @MonthName varchar(12)
DECLARE @HtmlMsgPtr binary(16)
DECLARE @TempMessage varchar(8000)

DECLARE @Today datetime
SET @Today = GETDATE()

CREATE TABLE ##StaffHtmlTable ([html] text)

INSERT INTO ##StaffHtmlTable VALUES ('
')
SELECT @HtmlMsgPtr = TEXTPTR([html])
FROM ##StaffHtmlTable

SET @TempMessage = '' +
    '' +
    '' +
    '' +
    '' + DATENAME(month, @Today) + ' Birthdays ' +
    '' +
    '' + DATENAME(month, @Today) +
    ' Staff Years of Service
'
UPDATETEXT ##StaffHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage

DECLARE @StaffName varchar(100)
DECLARE @AnnivDate datetime
DECLARE @Position varchar(250)

DECLARE BirthDateCursor CURSOR READ_ONLY FOR
SELECT
    nick_name + ' ' + last_name,
    birth_date
FROM core_person
WHERE staff_member = 1
AND birth_date <> '1/1/1900'
AND MONTH(birth_date) = MONTH(@Today)
ORDER BY DAY(birth_Date), last_name, nick_name

OPEN BirthDateCursor

FETCH NEXT FROM BirthDateCursor
INTO    @StaffName,
        @AnnivDate

WHILE (@@FETCH_STATUS <> -1)
BEGIN

    IF (@@FETCH_STATUS = 0)
    BEGIN

        -- Date
        IF DAY(@AnnivDate) < DAY(@Today)
            SET @TempMessage = ''
        ELSE
            SET @TempMessage = ''
        SET @TempMessage = @TempMessage + CAST(MONTH(@AnnivDate) as varchar) + '/' + CAST(DAY(@AnnivDate) as varchar)
        IF DAY(@AnnivDate) < 10
            SET @TempMessage = @TempMessage + '  '
        ELSE
            SET @TempMessage = @TempMessage + ' '

        SET @TempMessage = @TempMessage + '' + @StaffName + '
'
        UPDATETEXT ##StaffHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage

    END

    FETCH NEXT FROM BirthDateCursor
    INTO    @StaffName,
            @AnnivDate

END

CLOSE BirthDateCursor
DEALLOCATE BirthDateCursor

SET @TempMessage = ' '
UPDATETEXT ##StaffHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage

DECLARE HireDateCursor CURSOR READ_ONLY FOR
SELECT
    nick_name + ' ' + last_name,
    HD.datetime_value,
    POS.varchar_value
FROM core_person P
INNER JOIN core_person_attribute HD
    ON HD.person_id = P.person_id
    AND HD.attribute_id = @HireDateAttrID
LEFT OUTER JOIN core_person_attribute POS
    ON POS.person_id = P.person_id
    AND POS.attribute_id = @PositionAttrID
WHERE P.staff_member = 1
AND ISNULL(MONTH(HD.datetime_value),0) = MONTH(@Today)
AND ISNULL(YEAR(HD.datetime_value),0) < YEAR(@Today)
ORDER BY ISNULL(DAY(HD.datetime_value),0), last_name, nick_name

OPEN HireDateCursor

FETCH NEXT FROM HireDateCursor
INTO    @StaffName,
        @AnnivDate,
        @Position

WHILE (@@FETCH_STATUS <> -1)
BEGIN

    IF (@@FETCH_STATUS = 0)
    BEGIN

        DECLARE @Years int
        SET @Years = YEAR(@Today) - YEAR(@AnnivDate)

        -- Date
        IF DAY(@AnnivDate) < DAY(@Today)
            SET @TempMessage = ''
        ELSE
            SET @TempMessage = ''
        SET @TempMessage = @TempMessage + CAST(MONTH(@AnnivDate) as varchar) + '/' + CAST(DAY(@AnnivDate) as varchar)
        IF DAY(@AnnivDate) < 10
            SET @TempMessage = @TempMessage + '  '
        ELSE
            SET @TempMessage = @TempMessage + ' '

        SET @TempMessage = @TempMessage + '' + @StaffName + ', ' + @Position + ' - ' + CAST(@Years as varchar)
        IF @Years > 1
        BEGIN
            IF @Years >= 5
                SET @TempMessage = @TempMessage + ' Years!'
            ELSE
                SET @TempMessage = @TempMessage + ' Years'
        END
        ELSE
            SET @TempMessage = @TempMessage + ' Year'

        SET @TempMessage = @TempMessage + '
'
        UPDATETEXT ##StaffHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage

    END

    FETCH NEXT FROM HireDateCursor
    INTO    @StaffName,
            @AnnivDate,
            @Position

END

CLOSE HireDateCursor
DEALLOCATE HireDateCursor

SET @TempMessage = '
'
UPDATETEXT ##StaffHtmlTable.[html] @HtmlMsgPtr null 0 @TempMessage

SELECT [html] FROM ##StaffHtmlTable

DROP TABLE ##StaffHtmlTable

This stored procedure expects a couple person attribute IDs for the Hire Date and Position…

And then a new HTML From Stored Procedure module is added to our Arena homepage. Notice the two person attribute ID values that are passed to the stored procedure in the Parameters setting.

I love doing these quick little things that help someone save a lot of time.

(if you want to use the same stored procedure, here’s the two images we use…

)

Can You See Me Now?

David Turner — Sat, 23 May 2009 19:28:56 +0000

Not sure anyone is going to see this post, since I moved and didn’t tell anyone.

Why?

I wanted a new blog. I wasn’t thrilled by how dated my old blog looked, and I think I’m also going to try and write more often (let’s say… oh… at least once a week). Figured I’d clean it up first, so I ditched my old BlogEngine.NET blog hosted at GoDaddy, and went with a WordPress blog hosted at Dreamhost (all my friends we’re doing it).

I did update the nameservers for my domain, but because of differences between the WordPress feed and the old feed, and because of some strange things happening in my FeedBurner account, I’m pretty sure I’m writing this post only for me. But… If I’m wrong, and you see me, say hello.

What’s Different?

I was challenged by my friend ~~and fellow blogger~~ Erik, to include more posts about me (you know real-world stuff) and not just stuff about Arena. So while I will keep posting things about Arena (and other work stuff), I’ll also try and add some things about what captivates me when I’m not at my day job.

And That Is?

So what captivates me when I’m not at work. Well… I have a 16 year old son who hasn’t quite got his license yet, but works a lot as a Lifeguard and is big into dirt bikes, so I spend a great deal of my time driving him around and hanging out with him at the dirt track. My 10 year old daughter has Refractory Epilepsy so we’re trying to manage her 10+ seizures a day and considering the alternatives for her treatment (surgery). She still is my best movie partner as we seem to be the only two in the family who really enjoy intense action movies. My 7 year old son is autistic (high functioning), and is my biggest helper. Him and I are the two morning people and are often long at work in the yard together before anyone else is even up. And of course my wife. She helps me to survive it all.

Wow… that kinda felt like I was filling out an online dating ad (good thing no one is reading it anyway). Next post is going to be about Arena.

Podcast Setup

David Turner — Tue, 24 Feb 2009 18:14:43 +0000

In the interest of possibly helping anyone else set up their podcast, here’s how Arena podcasting is configured in our environment (You can click on any of these images to see them full size).

Feed Formats

Before setting up any channels, you’ll need to setup your available feed formats in the Arena Administration area.

XSLT

Arena uses an rss.aspx page to convert an XML version of your feed into a standardized RSS feed format. This gives you control over fine-tuning how the RSS content is generated (You can view both the XML and the XSLT from the Channel overview page). Here’s a copy of our xslt/rss/default.xslt file. This XSLT file creates an RSS feed that is also compliant with iTunes requirements…

xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:itunes="http://www.itunes.com/dtds/podcast-1.0.dtd">

    <xsl:output method="xml" version="1.0" encoding="utf-8"/>

    <xsl:template match="/">
        <rss xmlns:itunes="http://www.itunes.com/dtds/podcast-1.0.dtd" version="2.0">
            <xsl:for-each select="/*">
                <channel>
                    <title>
                        <xsl:value-of select="@author"/>: <xsl:value-of select="@title"/> (<xsl:value-of select="format/@title"/>)
                    title>
                    <link>
                        <xsl:value-of select="@link"/>
                    link>
                    <language>
                        <xsl:value-of select="@language"/>
                    language>
                    <copyright>
                        <xsl:value-of select="@copyright"/>
                    copyright>
                    <description>
                        <xsl:value-of select="description"/>
                    description>
                    <itunes:subtitle>
                        <xsl:value-of select="@subtitle"/>
                    itunes:subtitle>
                    <itunes:author>
                        <xsl:value-of select="@author"/>
                    itunes:author>
                    <itunes:summary>
                        <xsl:value-of select="description"/>
                    itunes:summary>
                    <itunes:owner>
                        <itunes:name>
                            <xsl:value-of select="@contactname"/>
                        itunes:name>
                        <itunes:email>
                            <xsl:value-of select="@contactemail"/>
                        itunes:email>
                    itunes:owner>
                    <itunes:image>
                        <xsl:attribute name="href">
                            <xsl:value-of select="@imageurl"/>
                        xsl:attribute>
                    itunes:image>
                    <xsl:for-each select="category">
                        <xsl:call-template name="categorynode">xsl:call-template>
                    xsl:for-each>
                    <xsl:for-each select="items/item">
                        <xsl:call-template name="itemnode">xsl:call-template>
                    xsl:for-each>
                channel>
            xsl:for-each>
        rss>
    xsl:template>

    <xsl:template name="categorynode">
        <itunes:category>
            <xsl:attribute name="text">
                <xsl:value-of select="@text"/>
            xsl:attribute>
            <xsl:for-each select="category">
                <xsl:call-template name="categorynode">xsl:call-template>
            xsl:for-each>
        itunes:category>
    xsl:template>

    <xsl:template name="itemnode">
        <item>
            <title>
                <xsl:choose>
                    <xsl:when test="topic">
                        <xsl:choose>
                            <xsl:when test="topic/@title = @title">
                                <xsl:value-of select="@title"/>
                            xsl:when>
                            <xsl:otherwise>
                                <xsl:value-of select="topic/@title"/>: <xsl:value-of select="@title"/>
                            xsl:otherwise>
                        xsl:choose>
                    xsl:when>
                    <xsl:otherwise>
                        <xsl:value-of select="@title"/>
                    xsl:otherwise>
                xsl:choose>
            title>
            <pubDate>
                <xsl:value-of select="@publish"/>
            pubDate>
            <itunes:author>
                <xsl:value-of select="/channel/@author"/>
            itunes:author>
            <xsl:if test="@imageurl">
                <itunes:image>
                    <xsl:attribute name="href">
                        <xsl:value-of select="@imageurl"/>
                    xsl:attribute>
                itunes:image>
            xsl:if>
            <itunes:summary>
                <xsl:value-of select="description"/>
            itunes:summary>
            <description>
                <xsl:value-of select="description"/>
            description>

            <link>
                <xsl:value-of select="@link"/>
            link>
            <enclosure>
                <xsl:attribute name="url">
                    <xsl:value-of select="enclosure/@url"/>
                xsl:attribute>
                <xsl:attribute name="length">
                    <xsl:value-of select="enclosure/@duration"/>
                xsl:attribute>
                <xsl:attribute name="type">
                    <xsl:value-of select="enclosure/@mimetype"/>
                xsl:attribute>
            enclosure>
            <itunes:keywords>
                <xsl:value-of select="keywords"/>
            itunes:keywords>
            <xsl:if test="enclosure/@time">
                <itunes:duration>
                    <xsl:value-of select="enclosure/@time"/>
                itunes:duration>
            xsl:if>
        item>
    xsl:template>

xsl:stylesheet>

Arena Page Setup

Here’s how our Podcasting administrative pages are setup in Arena

The first “Podcast Channels” page simply redirects to the second (not sure why, but that’s how it is).

The second “Podcast Channels” page has the ChannelList module.

The “Channel Details” page has the ChannelDetail and ChannelTabs (Show Filter setting is set to True) modules.

The “Item Details” page has the ItemDetail module.
Note: I think there’s a bug with the smart page setting on this module. It’s looking for a page above this one with the ItemList module which does not exist. Our setting points to the Channel Detail page above it.

The “Topic Details” page has the TopicDetail module.

Note: Similar bug here. It has a smart page setting that looks for a page above this one with the TopicList module. Our setting again points to the Channel Detail page above it.

The “Topic Item Detail” page has the ItemDetail module.

Note: Same bug. Our Item List Page setting points to the Topic Details page above this page.

Public Pages

Here’s how the public pages on our website are configured…

The “Online Message” page in addition to several html modules has the ChannelView module.

Settings Used: Heading = blank, Image Size = 452, Item View Page = “Message” page below this one, SubHeading = blank, Topic View Page = “Series” page below this one, Use Topic View = True.

The “Series” page has the TopicView module.

Settings Used: Heading = blank, Image Size = 452, Item View Detail Page = “Message” page below this one, SubHeading = blank.

The “Message” page has the ItemFlashViewer module.

Settings Used: Email Message = blank, Flash Format = Flash Video, Image Size = blank, Object Height = 260, Object Width = 320.

Channel Overview…

Here’s an example of how our contemporary service podcast is setup.

You’ll notice that we use FeedBurner as our external feed so that when people subscribe to the feed they’ll see the FeedBurner url instead of our internal url. We’ve then configured the Feedburner acount to point back to the actual arena url(http://arena.ccvonline.com/Arena/rss.aspx?c=1&f=1).

Details…

Enclosure Types…

Topic…

Item Overview…

Item Details…

You’ll notice that we use various third-party hosting providers to store our actual video and audio content to cut down on bandwidth to our internal servers. podcasts.ccvonline.com actually points to an Amazon S3 server.

Hope that helps, comment for any questions.