ActiveHub.hub

Parameters

Name	Type	Mode
@StartDate	date	IN
@EndDate	date	IN
@RetentionEligibleOnly	bit	IN
@MonthEndsOnly	bit	IN
@IncludeSite	bit	IN
@IncludeCategory	bit	IN
@IncludePackage	bit	IN

Definition

--KitManagerFileID=21439
--FileName=hub.udf_MembershipEODStats.sql
--SubmittedBy=Rae Andrews (ACTIVE\randrews)

--DROP FUNCTION hub.udf_MembershipEODStats;
CREATE   FUNCTION hub.udf_MembershipEODStats (
    @StartDate DATE, 
    @EndDate DATE,
    @RetentionEligibleOnly BIT = 1,
    @MonthEndsOnly BIT = 1,
    @IncludeSite BIT = 1,
    @IncludeCategory BIT = 1,
    @IncludePackage BIT = 1)
RETURNS TABLE
AS
RETURN
    WITH
        ENV (StartDate, EndDate, NextDate) AS (
            SELECT 
                CAST(ISNULL(@StartDate, DATEFROMPARTS(YEAR(GETDATE()) - 4, 1, 1)) AS DATE) AS StartDate,
                CAST(ISNULL(@EndDate, GETDATE()) AS DATE) AS EndDate,
                CAST(DATEADD(DAY, 1, ISNULL(@EndDate, GETDATE())) AS DATE) AS NextDate
        ),
        MEMBERSHIP_LAST_HISTORY (MEMBERSHIP_HISTORY_ID) AS (
            SELECT MAX(mal.MEMBERSHIP_HISTORY_ID) AS MEMBERSHIP_HISTORY_ID
            FROM hub.MEMBERSHIP_AUDIT_LOG mal
                JOIN hub.PACKAGES p ON p.PACKAGE_ID = mal.PACKAGE_ID
                JOIN hub.PACKAGE_CATEGORIES pc ON pc.PACKAGE_CATEGORY_ID = p.CATEGORY_ID
                CROSS JOIN ENV
            WHERE mal.BEG_DATESTAMP < ENV.NextDate
            AND mal.DATE_EFFECTIVE < ENV.NextDate
            AND (CASE
                WHEN mal.END_DATESTAMP IS NOT NULL THEN mal.END_DATESTAMP
                WHEN mal.VOIDED = 1 AND mal.MEMBERSHIP_STATUS = 'VOIDED' THEN mal.BEG_DATESTAMP
                ELSE ENV.NextDate
                END) > ENV.StartDate
            AND (CASE
                WHEN mal.END_DATESTAMP IS NOT NULL THEN mal.END_DATESTAMP
                WHEN mal.VOIDED = 1 AND mal.MEMBERSHIP_STATUS = 'VOIDED' THEN mal.BEG_DATESTAMP
                ELSE ENV.NextDate
                END) > mal.DATE_EFFECTIVE
            AND (@RetentionEligibleOnly = 0 OR pc.RETENTION_ELIGIBLE = 'Yes')
            GROUP BY mal.MEMBERSHIP_ID
        ),
        MEMBERSHIP_AUDIT_DATES (MEMBERSHIP_HISTORY_ID, SITE_ID, CATEGORY_ID, PACKAGE_ID, BEG_DATE, END_DATE, IS_ACTIVE, IS_SUSPENDED) AS (
            SELECT mal.MEMBERSHIP_HISTORY_ID,
                (CASE WHEN @IncludeSite = 1 THEN mal.SITE_ID ELSE NULL END) AS SITE_ID,
                (CASE WHEN @IncludeCategory = 1 THEN p.CATEGORY_ID ELSE NULL END) AS CATEGORY_ID,
                (CASE WHEN @IncludePackage = 1 THEN mal.PACKAGE_ID ELSE NULL END) AS PACKAGE_ID,
                CAST((CASE WHEN mal.DATE_EFFECTIVE > mal.BEG_DATESTAMP THEN mal.DATE_EFFECTIVE ELSE mal.BEG_DATESTAMP END) AS DATE) AS BEG_DATE,
                DATEADD(DAY, -1, ISNULL(CAST(mal.END_DATESTAMP AS DATE), ENV.EndDate)) AS END_DATE, -- IGNORE THE LAST DATE
                (CASE WHEN mal.MEMBERSHIP_STATUS IN ('Active', 'Suspended', 'Renewing') THEN 1 ELSE 0 END) AS IS_ACTIVE,
                (CASE WHEN mal.MEMBERSHIP_STATUS = 'Suspended' THEN 1 ELSE 0 END) AS IS_SUSPENDED
            FROM hub.MEMBERSHIP_AUDIT_LOG mal
                JOIN hub.PACKAGES p ON p.PACKAGE_ID = mal.PACKAGE_ID
                JOIN hub.PACKAGE_CATEGORIES pc ON pc.PACKAGE_CATEGORY_ID = p.CATEGORY_ID
                CROSS JOIN ENV
            WHERE ISNULL(CAST(mal.END_DATESTAMP AS DATE), ENV.EndDate) > CAST(mal.BEG_DATESTAMP AS DATE) -- ONLY IF IT SPANS DAYS
            AND mal.BEG_DATESTAMP < ENV.NextDate
            AND mal.DATE_EFFECTIVE < ENV.NextDate
            AND ISNULL(mal.END_DATESTAMP, ENV.EndDate) >= DATEADD(DAY, 1, ENV.StartDate)
            AND ISNULL(mal.END_DATESTAMP, ENV.EndDate) > mal.DATE_EFFECTIVE
            AND (@RetentionEligibleOnly = 0 OR pc.RETENTION_ELIGIBLE = 'Yes')
            AND (mal.VOIDED = 0 OR mal.MEMBERSHIP_STATUS <> 'VOIDED')
            UNION ALL
            SELECT mal.MEMBERSHIP_HISTORY_ID,
                (CASE WHEN @IncludeSite = 1 THEN mal.SITE_ID ELSE NULL END) AS SITE_ID,
                (CASE WHEN @IncludeCategory = 1 THEN p.CATEGORY_ID ELSE NULL END) AS CATEGORY_ID,
                (CASE WHEN @IncludePackage = 1 THEN mal.PACKAGE_ID ELSE NULL END) AS PACKAGE_ID,
                (CASE
                    WHEN mal.END_DATESTAMP IS NOT NULL THEN CAST(mal.END_DATESTAMP AS DATE)
                    WHEN mal.VOIDED = 1 AND mal.MEMBERSHIP_STATUS = 'VOIDED' THEN CAST(mal.BEG_DATESTAMP AS DATE)
                    ELSE ENV.EndDate
                    END) AS BEG_DATE,
                (CASE
                    WHEN mal.END_DATESTAMP IS NOT NULL THEN CAST(mal.END_DATESTAMP AS DATE)
                    WHEN mal.VOIDED = 1 AND mal.MEMBERSHIP_STATUS = 'VOIDED' THEN CAST(mal.BEG_DATESTAMP AS DATE)
                    ELSE ENV.EndDate
                    END) AS END_DATE,
                (CASE WHEN mal.MEMBERSHIP_STATUS IN ('Active', 'Suspended', 'Renewing') THEN 1 ELSE 0 END) AS IS_ACTIVE,
                (CASE WHEN mal.MEMBERSHIP_STATUS = 'Suspended' THEN 1 ELSE 0 END) AS IS_SUSPENDED
            FROM hub.MEMBERSHIP_AUDIT_LOG mal
                JOIN hub.PACKAGES p ON p.PACKAGE_ID = mal.PACKAGE_ID
                JOIN hub.PACKAGE_CATEGORIES pc ON pc.PACKAGE_CATEGORY_ID = p.CATEGORY_ID
                JOIN MEMBERSHIP_LAST_HISTORY mlh ON mlh.MEMBERSHIP_HISTORY_ID = mal.MEMBERSHIP_HISTORY_ID
                CROSS JOIN ENV
            WHERE mal.DATE_EFFECTIVE < ENV.NextDate
            AND (CASE
                WHEN mal.END_DATESTAMP IS NOT NULL THEN mal.END_DATESTAMP
                WHEN mal.VOIDED = 1 AND mal.MEMBERSHIP_STATUS = 'VOIDED' THEN mal.BEG_DATESTAMP
                ELSE ENV.EndDate
                END) < ENV.NextDate
            AND (CASE
                WHEN mal.END_DATESTAMP IS NOT NULL THEN mal.END_DATESTAMP
                WHEN mal.VOIDED = 1 AND mal.MEMBERSHIP_STATUS = 'VOIDED' THEN mal.BEG_DATESTAMP
                ELSE ENV.EndDate
                END) > mal.DATE_EFFECTIVE
            AND (CASE
                WHEN mal.END_DATESTAMP IS NOT NULL THEN mal.END_DATESTAMP
                WHEN mal.VOIDED = 1 AND mal.MEMBERSHIP_STATUS = 'VOIDED' THEN mal.BEG_DATESTAMP
                ELSE ENV.EndDate
                END) <> DATEADD(DAY, 1, ISNULL(mal.DATE_EXPIRES, ENV.EndDate))
        ),
        TARGET_DATES (TARGET_DATE) AS (
            SELECT CAST(ahd.dt_dt AS DATE) AS TARGET_DATE
            FROM dbo.ACTIVE_HUB_DATES ahd
                CROSS JOIN ENV
            WHERE ahd.dt_dt BETWEEN ENV.StartDate AND ENV.EndDate
            AND (@MonthEndsOnly = 0 OR ahd.mo_end_flg = 'T')
        )
    SELECT td.TARGET_DATE AS AUDIT_DATE,
        mad.SITE_ID,
        mad.CATEGORY_ID,
        mad.PACKAGE_ID,
        COUNT(1) AS ACTIVE_COUNT,
        SUM(mad.IS_SUSPENDED) AS SUSPENDED_COUNT
    FROM MEMBERSHIP_AUDIT_DATES mad
    CROSS JOIN TARGET_DATES td
    WHERE td.TARGET_DATE BETWEEN mad.BEG_DATE AND mad.END_DATE
    AND mad.IS_ACTIVE = 1
    GROUP BY td.TARGET_DATE,
        mad.SITE_ID,
        mad.CATEGORY_ID,
        mad.PACKAGE_ID;