cust_CCIW_v_involvment

CREATE VIEW [dbo].[cust_CCIW_v_involvment]
AS

WITH LastAttendance AS
(
    SELECT  person_id, MAX(datetime_value) AS last_attendance
    FROM        dbo.cust_CCIW_v_attendance
    GROUP BY person_id
),

LastWebActivity AS
(
    SELECT person_id, last_web_activity = MAX(H.date_created)
    FROM core_person_history H
    GROUP BY person_id,history_type_luid
    HAVING  H.history_type_luid = 108 --108 = Web Activity
),

LastNote AS
(
    SELECT person_id, last_note = MAX(H.date_created)
    FROM core_person_history H
    GROUP BY person_id,history_type_luid
    HAVING  H.history_type_luid = 355 --355 = User Note
),

LastPersonUpdate AS
(
    SELECT person_id, last_person_update = MAX(H.date_created)
    FROM core_person_history H
    GROUP BY person_id,history_type_luid
    HAVING  H.history_type_luid = 366 --366 = Person Update
),

LastTag AS
(
    SELECT person_id, last_tag_updated = MAX(PM.date_modified)
    FROM core_profile_member PM
    GROUP BY PM.person_id
),

LastViewed AS
(
SELECT [target_person_id], last_viewed = MAX([view_datetime])
  FROM [core_person_viewed_by]
  GROUP BY target_person_id
),

LastMemberStatusChange
AS
(
SELECT person_id,
    last_status_update = MAX(date_created)
FROM core_person_history H WHERE H.history like 'Member Status changed%'
GROUP BY person_id
),

DateValues AS
(
SELECT TodayMinus1Year = DATEADD(year, -1, GETDATE()), TodayMinus2Years = DATEADD(year, -2, GETDATE())
),

sum_giving AS
(
SELECT
    giving_unit_id,
    total_giving_last_12_months = sum(currency_amount)
    -- Funds are from online giving page
FROM cust_CCIW_v_ctrb_basic_filtered(Dateadd(Month, Datediff(Month, 0, DATEADD(m, -12,
current_timestamp)), 0),GETDATE(),'-1','-1','-1')
GROUP BY giving_unit_id
),

giving_unit_count_gifts AS
(
SELECT
    giving_unit_id,
    count_gift_last_12_months = count(contribution_id)
FROM cust_CCIW_v_ctrb_basic_filtered(Dateadd(Month, Datediff(Month, 0, DATEADD(m, -12,
current_timestamp)), 0),GETDATE(),'-1','-1','-1')
GROUP BY giving_unit_id
--Not sure why this line was added perhaps I was looking at people with more than 2 gifts at some point
--HAVING count(contribution_id) >= 3
),

contribution_summary AS

(
SELECT giving_unit_id,
MAX(b.contribution_date) AS last_giving_unit_contribution
FROM
cust_CCIW_v_ctrb_basic b
GROUP BY giving_unit_id
),

Involvement AS
(
SELECT
   P.person_id,
   P.first_name,
   P.last_name,
   age = dbo.fn_Age(p.birth_date,GETDATE()),
   --P.last_attended, --this field is not currently in use
   P.date_modified,
   M.family_id,
   family_role = L2.lookup_value, -- Family Role
   member_status_id = P.member_status,
   member_status_name = L.lookup_value,
   P.record_status,
   record_status_text =
   CASE P.record_status WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' WHEN 2 THEN 'Pending' END,
   [Meets Regular Attender Criteria] =
   CASE WHEN (AC.Last_26_Weeks_Count >= 6) THEN 1 ELSE 0 END,
   [Meets Occasional Attender Criteria] =
   CASE WHEN ((AC.Last_52_Weeks_Count >= 1) AND (AC.Last_26_Weeks_Count < 6))
      AND (AC.Last_52_Weeks_Count >= 6) THEN 1 ELSE 0 END,
   [Meets Visitor Criteria] =
   CASE WHEN  AC.Last_52_Weeks_Count >= 1 AND AC.Last_52_Weeks_Count < 6 THEN 1 ELSE 0 END,
   dbo.cust_CCIW_funct_is_in_SmallGroup(P.person_id, 1) AS SmallGroup,
   dbo.cust_CCIW_funct_is_Serving(P.person_id, 1) AS Serving,
   LA.last_attendance, -- Any attendance including non-worship service
   CS.last_giving_unit_contribution,
   LW.last_web_activity,
   LT.last_tag_updated,
   LV.last_viewed,
   LN.last_note,
   LPU.last_person_update,
   -- last x week attendance counts only look at worship related attendance (occurence attendance, , 1st/2nd/3rd Visit Attributes, Weekend Related Giving)
   SC.last_status_update,
   AC.Last_52_Weeks_Count,
   AC.Last_52_Weeks_Family_Count,
   AC.Last_26_Weeks_Count,
   AC.Last_26_Weeks_Family_Count,
   AC.Last_13_Weeks_Count,
   AC.Last_13_Weeks_Family_Count,
   AC.Last_4_Weeks_Count,
   AC.Last_4_Weeks_Family_Count,
   former_status = L.lookup_qualifier4,
   CG.count_gift_last_12_months,
   SG.total_giving_last_12_months,
   DateValues.*
FROM
   core_person AS P INNER JOIN
   --cust_CCIW_v_person_basic as B on P.person_id = B.person_id INNER JOIN
   core_lookup AS L ON L.lookup_id = P.member_status INNER JOIN
   core_family_member M ON M.person_id = P.person_id LEFT JOIN
   core_lookup L2 on L2.lookup_id = M.role_luid LEFT JOIN
   LastAttendance LA on LA.person_id = P.person_id LEFT JOIN
   --contribution summary groups on giving unit id vs person_id
   contribution_summary AS CS ON CS.giving_unit_id = P.giving_unit_id LEFT JOIN
   cust_CCIW_v_WS_attendance_counts AS AC ON AC.person_id = P.person_id LEFT JOIN
   LastWebActivity LW on LW.person_id = P.person_id LEFT JOIN
   LastTag LT on LT.person_id = P.person_id LEFT JOIN
   LastViewed LV on LV.target_person_id = P.person_id LEFT JOIN
   LastNote LN on LN.person_id = P.person_id LEFT JOIN
   LastMemberStatusChange SC on SC.person_id = P.person_id LEFT JOIN
   LastPersonUpdate LPU on LPU.person_id = P.person_id LEFT JOIN
   sum_giving SG on SG.giving_unit_id = P.giving_unit_id LEFT JOIN
   giving_unit_count_gifts CG on CG.giving_unit_id = P.giving_unit_id INNER JOIN
   DateValues on 1=1
),

Analysis AS
(
Select
    [Involvment In Last Year] = CASE
        WHEN
            I.Last_52_Weeks_Family_Count >=1 OR
            I.Serving = 1 OR
            I.SmallGroup = 1 OR
            I.count_gift_last_12_months >= 1 OR
            --https://stackoverflow.com/questions/71022/sql-max-of-multiple-columns/6871572#6871572
            (SELECT Max(v)
                FROM (VALUES
                    (last_attendance),
                    (last_giving_unit_contribution),
                    (last_web_activity),
                    (last_tag_updated),
                    (last_note)) AS value(v)
            ) >= TodayMinus1Year
            THEN 1 ELSE 0 END,
    [Involvment In Last 2 Years] = CASE
        WHEN
            I.Last_52_Weeks_Family_Count >=1 OR
            I.Serving = 1 OR
            I.SmallGroup = 1 OR
            (SELECT Max(v)
                FROM (VALUES
                    (last_attendance),
                    (last_giving_unit_contribution),
                    (last_web_activity),
                    (last_tag_updated),
                    (last_note)) AS value(v)
            ) >= TodayMinus2Years
            THEN 1 ELSE 0 END,
    AttendanceAfterLastStatusChange = CASE WHEN I.last_attendance > I.last_status_update THEN 'Yes' ELSE 'No' END,
    I.*
    FROM Involvement I

)

Select
[Recommended Status ID] =
        CASE
            WHEN (former_status = '' OR (former_status = '1' and AttendanceAfterLastStatusChange = 'Yes'))
            THEN CASE
                     WHEN [Meets Regular Attender Criteria] = 1 THEN 'Regular Attender (Non-Member)'         -- 960   = Regular Attender (Non-Member)
                     WHEN [Meets Occasional Attender Criteria] = 1 THEN 'Occasional Attender (Non-Member)'   -- 10415 = Occasional Attender (Non-Member)
                     WHEN [Meets Visitor Criteria] = 1 THEN 'Visitor-Wheaton Campus'                         -- 961   = Visitor-Wheaton Campus
                     -- 960   = Regular Attender, 10415 = Occasional Attender, 10387 = Former Attender
                     WHEN member_status_id in (960,10415) AND (count_gift_last_12_months < 4 or count_gift_last_12_months is null)
                           AND (total_giving_last_12_months < 500 or total_giving_last_12_months is null) Then 'Former Attender'
                     WHEN member_status_id = 961 THEN 'Former Visitor'                                               -- 961   = Visitor-Wheaton Campus,-- 10414 = Former Visitor'
                  END
            END,
A.* FROM Analysis A

GO