SET STATISTICS TIME ON;
    DROP TABLE IF EXISTS #Additions;
    DROP TABLE IF EXISTS #Terminations;
    DROP TABLE IF EXISTS #Changes;


    SELECT record_sequence,
           snapshot_date,
           npi_hk,
           tin_hk,
           Exclusion_Reason,
           tin_number,
           npi,
           Prov_Last_Name,
           prov_first_name,
           prov_mi,
           clinic_Name,
           [OHCS Change Date],
           [OHCS Eff Date],
           [OHCS Term Date],
           specialty,
           sub_specialty,
           remit_address1,
           remit_address2,
           remit_city,
           remit_state,
           remit_zip,
           loc_address1,
           loc_address2,
           loc_city,
           loc_state,
           loc_zip,
           loc_county,
           license_number,
           license_state,
           license_issue_date,
           acn_prov_id,
           provider_id,
           office_location_id,
           main_status,
           sub_status,
           status_date,
           loc_eff_date,
           loc_term_date,
           tin_eff_date,
           tin_term_date,
           prov_phone,
           prov_fax,
           gender,
           client_term_date,
           cred_status_eff_date,
           [Status],
           Provider_Type,
           Social_Group,
           Request_Type,
           Change_Type
    INTO #Additions
    FROM #FirstResult
    WHERE Request_Type = 'Add';

    SELECT record_sequence,
           snapshot_date,
           npi_hk,
           tin_hk,
           Exclusion_Reason,
           tin_number,
           npi,
           Prov_Last_Name,
           prov_first_name,
           prov_mi,
           clinic_Name,
           [OHCS Change Date],
           [OHCS Eff Date],
           [OHCS Term Date],
           specialty,
           sub_specialty,
           remit_address1,
           remit_address2,
           remit_city,
           remit_state,
           remit_zip,
           loc_address1,
           loc_address2,
           loc_city,
           loc_state,
           loc_zip,
           loc_county,
           license_number,
           license_state,
           license_issue_date,
           acn_prov_id,
           provider_id,
           office_location_id,
           main_status,
           sub_status,
           status_date,
           loc_eff_date,
           loc_term_date,
           tin_eff_date,
           tin_term_date,
           prov_phone,
           prov_fax,
           gender,
           client_term_date,
           cred_status_eff_date,
           [Status],
           Provider_Type,
           Social_Group,
           Request_Type,
           Change_Type
    INTO #Changes
    FROM #FirstResult
    WHERE Request_Type = 'Change';


    ;WITH cteLastTermDate
    AS (SELECT LAST_VALUE([OHCS Term Date]) OVER (PARTITION BY npi ORDER BY [a].[OHCS Term Date]) AS dt,
               a.npi
        FROM #FirstResult a
        UNION ALL
        SELECT MAX(hn.load_date) AS dt,
               npi
        FROM rv.hub_npi hn
        WHERE NOT EXISTS
        (
            SELECT 1 FROM stg.stg_facets_full f WHERE f.npi_hk = hn.npi_hk
        )
              AND hn.source = 'FACETS'
        GROUP BY hn.npi)
    SELECT record_sequence,
           snapshot_date,
           tin_number,
           Exclusion_Reason,
           npi,
           Prov_Last_Name,
           prov_first_name,
           prov_mi,
           clinic_Name,
           [OHCS Change Date],
           [OHCS Eff Date],
           [OHCS Term Date],
           CAST(CASE
                    WHEN Status = 'Termed' THEN
                        CASE
                            WHEN Provider_Type IN ( 'Group', 'Solo' ) THEN
                                [OHCS Term Date]
                            WHEN Provider_Type = 'Rendering' THEN
                            (
                                SELECT TOP (1)
                                       dt
                                FROM
                                (SELECT dt FROM cteLastTermDate c WHERE c.npi = cte.npi) x
                                ORDER BY dt DESC
                            )
                        END
                END AS DATE) AS latest_term_date,
           specialty,
           sub_specialty,
           remit_address1,
           remit_address2,
           remit_city,
           remit_state,
           remit_zip,
           loc_address1,
           loc_address2,
           loc_city,
           loc_state,
           loc_zip,
           loc_county,
           license_number,
           license_state,
           license_issue_date,
           acn_prov_id,
           provider_id,
           office_location_id,
           main_status,
           sub_status,
           status_date,
           loc_eff_date,
           loc_term_date,
           tin_eff_date,
           tin_term_date,
           prov_phone,
           prov_fax,
           gender,
           client_term_date,
           cred_status_eff_date,
           [Status],
           Provider_Type,
           Social_Group,
           Request_Type,
           Change_Type
    INTO #Terminations
    FROM #FirstResult cte
    WHERE Request_Type = 'Term';

                    WHEN cte2.Provider_Type = 'Rendering' THEN
                    (
                        SELECT TOP (1)
                               dt
                        FROM cteEarliestEffectiveDate ct
                        WHERE ct.npi = cte2.npi
                        ORDER BY dt
                    )
                END AS DATE) AS earliest_effective_date,
           CAST([cte2].[OHCS Change Date] AS DATE) AS change_date,
           CAST(cte2.[OHCS Term Date] AS DATE) AS term_date,
           cte2.latest_term_date,
           cte2.tin_number,
           cte2.npi,
           CAST(cte2.provider_id AS VARCHAR(50)) AS provider_id,
           CAST(cte2.Prov_Last_Name AS VARCHAR(100)) AS provider_last_name,
           CAST(cte2.prov_first_name AS VARCHAR(30)) AS provider_first_name,
           CAST(cte2.prov_mi AS VARCHAR(30)) AS provider_middle_initial,
           CAST(cte2.clinic_Name AS VARCHAR(100)) AS clinic_name,
           -- cte2.prov_group,
           CAST(CASE
                    WHEN cte2.Provider_Type = 'Group' THEN
                        '70'
                    WHEN cte2.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        tx.T50_specialty
                END AS VARCHAR(100)) AS t50_specialty,
           CAST(CASE
                    WHEN cte2.Provider_Type = 'Group' THEN
                        '24'
                    WHEN cte2.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        tx.T50_provider_type
                END AS VARCHAR(100)) AS t50_provider_type,
           CAST(CASE
                    WHEN cte2.Provider_Type = 'Group' THEN
                        '193200000X'
                    WHEN cte2.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        tx.taxonomy
                END AS VARCHAR(10)) AS t15_taxonomy,
           CAST(cte2.license_number AS VARCHAR(15)) AS license_number,
           CAST(cte2.license_state AS VARCHAR(50)) AS license_state,
           CAST(cte2.license_issue_date AS DATE) AS license_issue_date,
           CAST(cte2.loc_address1 AS VARCHAR(50)) AS svc_address_line1,
           CAST(cte2.loc_address2 AS VARCHAR(50)) AS svc_address_line2,
           CAST(cte2.loc_city AS VARCHAR(30)) AS svc_city,
           CAST(cte2.loc_state AS VARCHAR(2)) AS svc_state,
           CAST(cte2.loc_zip AS VARCHAR(9)) AS svc_zip,
           CAST(cte2.remit_address1 AS VARCHAR(50)) AS remit_address_line1,
           CAST(cte2.remit_address2 AS VARCHAR(50)) AS remit_address_line2,
           CAST(cte2.remit_city AS VARCHAR(30)) AS remit_city,
           CAST(cte2.remit_state AS VARCHAR(2)) AS remit_state,
           CAST(cte2.remit_zip AS VARCHAR(9)) AS remit_zip
    FROM #Terminations cte2
        LEFT JOIN bv.view_specialty_taxonomy tx
            ON cte2.specialty = tx.specialty
               AND tx.source = 'OHCS';

    SET STATISTICS TIME OFF;