DECLARE @DateRange DATE = '1900-01-01';

    WITH CTE
    AS (
        SELECT *
            ,ROW_NUMBER() OVER (
                PARTITION BY [Individual KEY]
                ORDER BY CASE [Member STATUS]
                        WHEN 'Active'
                            THEN 1
                        WHEN 'Pending'
                            THEN 2
                        ELSE 3
                        END
                    ,[STATUS_DT] DESC
                ) AS rn
        FROM [AAANE].[dbo].[ufn_adobe_full_load_new_extract_range] (@DateRange) AS t1
        )
    SELECT [Individual KEY]
        ,[Member NUMBER Associate ID]
        ,[Member NUMBER]
        ,[Member STATUS]
        ,[Email Address]
        ,[Global Opt-OUT]
        ,[Coverage Level Code]
        ,[FIRST Name]
        ,[LAST Name]
        ,[Address Line 1]
        ,[Address Line 2]
        ,[City]
        ,[State]
        ,[ZIP]
        ,[ZIP4]
        ,[County]
        ,[Region Code]
        ,[MSA Code]
        ,[Birth DATE]
        ,[Gender]
        ,[JOIN AAA DATE]
        ,[JOIN Club DATE]
        ,[Member Expiration DATE]
        ,[Renew Method]
        ,[Hertz STATUS]
    FROM CTE
    WHERE rn = 1
        --only return records with valid emails
        AND [Email Address] LIKE '%_@__%.__%'
        AND PATINDEX('%[^a-z,0-9,@,.,_,\-]%', [Email Address]) = 0
        AND ISNULL([Individual KEY], '') <> ''
        AND EXISTS (
            SELECT *
            FROM [JP_DEV].[dbo].[Adobe_ReloadRecords] AS t2
            WHERE cte.[Individual KEY] = t2.[Individual KEY]
        )