-- https://corelationercag.forumbee.com/t/h7hkx53/account-last-activity-date
-- @BPUTTKAMMER 2021-06-22
-- TO GET ACCOUNT DORMANCY, NOT SHARE OR LOAN DORMANCY, BUT TO GROUP BY ACCOUNT
-- WILL ALSO OUTPUT COLUMN OF OLDEST LAST ACTIVITY ALONG WITH SELECTING MOST RECENT ACTIVITY BASED ON DAYS OVER 365
-- VERSION 4
-- PUBLISHED TO JASPERSOFT 2021-06-30
-- TITLE: Account Level Dormancy
-- DESCRIPTION: Account level dormancy, based on most recent share/loan last activity date @BPuttkammer
WITH ACCOUNT_RECENT_ACTIVITY (ACCOUNT_SERIAL, LAST_ACTIVITY_DATE) AS (
    SELECT
        PARENT_SERIAL AS ACCOUNT_SERIAL
        , MAX(LAST_ACTIVITY_DATE) AS LAST_ACTIVITY_DATE
    FROM
        (
            SELECT
                SHARE.PARENT_SERIAL
                , SHARE.LAST_ACTIVITY_DATE,
                'S ' || SHARE.ID AS ID
            FROM
                CORE.SHARE AS SHARE INNER JOIN
                CORE.SH_TYPE AS SH_TYPE ON SH_TYPE.SERIAL=SHARE.TYPE_SERIAL
            WHERE
            	SHARE.CLOSE_DATE IS NULL
            	AND SH_TYPE.CATEGORY NOT IN ('C')
            	AND SHARE.CHARGE_OFF_DATE IS NULL
            UNION
            SELECT
                LOAN.PARENT_SERIAL
                , LOAN.LAST_ACTIVITY_DATE,
                'L ' || LOAN.ID AS ID
            FROM
                CORE.LOAN AS LOAN INNER JOIN
                CORE.LN_TYPE AS LN_TYPE ON LN_TYPE.SERIAL=LOAN.TYPE_SERIAL
            WHERE
            	LOAN.CLOSE_DATE IS NULL
            	AND LN_TYPE.CATEGORY NOT IN ('CC')
            	AND LOAN.CHARGE_OFF_DATE IS NULL
        )
    GROUP BY
        PARENT_SERIAL
        
), ACCOUNT_OLDEST_ACTIVITY (ACCOUNT_SERIAL, OLDEST_ACTIVITY_DATE) AS (
    SELECT
        PARENT_SERIAL AS ACCOUNT_SERIAL
        , MIN(LAST_ACTIVITY_DATE) AS OLDEST_ACTIVITY_DATE
    FROM
        (
            SELECT
                SHARE.PARENT_SERIAL
                , SHARE.LAST_ACTIVITY_DATE,
                'S ' || SHARE.ID AS ID
            FROM
                CORE.SHARE AS SHARE INNER JOIN
                CORE.SH_TYPE AS SH_TYPE ON SH_TYPE.SERIAL=SHARE.TYPE_SERIAL
            WHERE
            	SHARE.CLOSE_DATE IS NULL 
            	AND SH_TYPE.CATEGORY NOT IN ('C')
            	AND SHARE.CHARGE_OFF_DATE IS NULL
            UNION
            SELECT
                LOAN.PARENT_SERIAL
                , LOAN.LAST_ACTIVITY_DATE,
                'L ' || LOAN.ID AS ID
            FROM
                CORE.LOAN AS LOAN INNER JOIN
                CORE.LN_TYPE AS LN_TYPE ON LN_TYPE.SERIAL=LOAN.TYPE_SERIAL
            WHERE
            	LOAN.CLOSE_DATE IS NULL
            	AND LN_TYPE.CATEGORY NOT IN ('CC')
            	AND LOAN.CHARGE_OFF_DATE IS NULL
        )
    GROUP BY
        PARENT_SERIAL
),
OpenLoanSummary(accountSerial,count,balance) AS (
    SELECT
        ACCOUNT.SERIAL,
        COUNT(LOAN.SERIAL),
        COALESCE(SUM(LOAN.BALANCE),0.00)
    FROM
     CORE.ACCOUNT AS ACCOUNT LEFT OUTER JOIN
        CORE.LOAN AS LOAN ON
            ACCOUNT.SERIAL = LOAN.PARENT_SERIAL AND
            LOAN.CHARGE_OFF_DATE IS NULL AND
            LOAN.CLOSE_DATE IS NULL
    GROUP BY
        ACCOUNT.SERIAL
),
OpenShareSummary(accountSerial,count,balance) AS (
    SELECT
        ACCOUNT.SERIAL,
        COUNT(SHARE.SERIAL),
        COALESCE(SUM(SHARE.BALANCE),0.00)
    FROM
        CORE.ACCOUNT AS ACCOUNT LEFT OUTER JOIN
        CORE.SHARE AS SHARE ON
            ACCOUNT.SERIAL = SHARE.PARENT_SERIAL AND
            SHARE.CHARGE_OFF_DATE IS NULL AND
            SHARE.CLOSE_DATE IS NULL
    GROUP BY
        ACCOUNT.SERIAL
)

SELECT *
FROM (
	SELECT
	    ACCOUNT.ACCOUNT_NUMBER, PERSON.FIRST_NAME || ' ' || PERSON.LAST_NAME AS FULL_NAME,
	    DAYS_BETWEEN(ENV.POSTING_DATE, ACCOUNT_RECENT_ACTIVITY.LAST_ACTIVITY_DATE) AS DORMANT_DAYS, 
	    OpenLoanSummary.count AS OPEN_LOANS, OpenLoanSummary.balance AS OPEN_LOAN_BALANCE,
	    OpenShareSummary.count AS OPEN_SHARES, OpenShareSummary.balance AS OPEN_SHARE_BALANCE,
	    ACCOUNT_RECENT_ACTIVITY.LAST_ACTIVITY_DATE AS MOST_RECENT_ACTIVITY, 
	    ACCOUNT_OLDEST_ACTIVITY.OLDEST_ACTIVITY_DATE	
	FROM
	    CORE.ENV AS ENV CROSS JOIN
	    ACCOUNT_RECENT_ACTIVITY INNER JOIN 
	    CORE.ACCOUNT AS ACCOUNT ON ACCOUNT.SERIAL=ACCOUNT_RECENT_ACTIVITY.ACCOUNT_SERIAL INNER JOIN
	    CORE.PERSON AS PERSON ON PERSON.SERIAL=ACCOUNT.PRIMARY_PERSON_SERIAL INNER JOIN
	    ACCOUNT_OLDEST_ACTIVITY ON ACCOUNT_OLDEST_ACTIVITY.ACCOUNT_SERIAL=ACCOUNT.SERIAL LEFT OUTER JOIN
	    OpenLoanSummary ON OpenLoanSummary.accountSerial=ACCOUNT.SERIAL LEFT OUTER JOIN
	    OpenShareSummary ON OpenShareSummary.accountSerial=ACCOUNT.SERIAL
	) a
WHERE
	DORMANT_DAYS > 365
	--AND MOST_RECENT_ACTIVITY <> OLDEST_ACTIVITY_DATE
ORDER BY
	MOST_RECENT_ACTIVITY DESC, ACCOUNT_NUMBER ASC
--FETCH FIRST 200 ROWS ONLY
;