npi_provider_view

Materialized: true

Definition

 WITH npi_simple_name AS (
         SELECT DISTINCT ON (n1.npi) n1.npi,
                CASE
                    WHEN (n1.name_type = 'Legal Name'::claims.npi_name_type) THEN array_to_string(array_remove(ARRAY[n1.name_prefix, n1.name_first, n1.name_last_or_organization, n1.name_suffix], ''::text), ' '::text)
                    WHEN ((n2.name_type = 'Doing Business As'::claims.npi_name_type) OR (n1.name_type = 'Legal Business Name'::claims.npi_name_type)) THEN n1.name_last_or_organization
                    ELSE NULL::text
                END AS name
           FROM (claims.npi_name n1
             LEFT JOIN claims.npi_name n2 ON (((n2.npi = n1.npi) AND ((n1.name_type = 'Legal Name'::claims.npi_name_type) OR (n1.name_type = 'Legal Business Name'::claims.npi_name_type) OR ((n2.name_type = 'Doing Business As'::claims.npi_name_type) AND (length(n2.name_last_or_organization) > 5) AND (NOT (n2.name_last_or_organization ~ '^[[:alnum:]]*[[:digit:]][[:alnum:]]*$'::text)))))))
        ), npi_taxonomy_simple AS (
         WITH npi_codes AS (
                 SELECT DISTINCT ON (t_1.npi, t_1.is_primary) t_1.npi,
                    array_agg(t_1.taxonomy_code) AS taxonomy_codes,
                    array_agg(l.display_name) AS display_names,
                    t_1.is_primary
                   FROM (claims.npi_taxonomy t_1
                     LEFT JOIN claims.nucc_taxonomy_lookup l ON ((l.taxonomy_code = t_1.taxonomy_code)))
                  GROUP BY t_1.npi, t_1.is_primary
                )
         SELECT DISTINCT ON (primary_codes.npi) primary_codes.npi,
            primary_codes.taxonomy_codes[1] AS primary_code,
            primary_codes.display_names[1] AS primary_display_name,
            array_remove(secondary_codes.taxonomy_codes, ''::text) AS secondary_taxonomy_codes,
            array_remove(secondary_codes.display_names, NULL::text) AS secondary_taxonomy_display_names
           FROM (npi_codes primary_codes
             LEFT JOIN npi_codes secondary_codes ON (((secondary_codes.npi = primary_codes.npi) AND (NOT secondary_codes.is_primary))))
          WHERE primary_codes.is_primary
        ), npi_address_simple AS (
         SELECT npi_address.npi,
            npi_address.address_id,
                CASE
                    WHEN (npi_address.is_mailing_address AND npi_address.is_primary_practice_location AND npi_address.is_secondary_practice_location) THEN 'Mailing Address, Primary Practice Location, and Secondary Practice Location'::text
                    WHEN (npi_address.is_mailing_address AND npi_address.is_primary_practice_location) THEN 'Mailing Address and Primary Practice Location'::text
                    WHEN (npi_address.is_mailing_address AND npi_address.is_secondary_practice_location) THEN 'Mailing Address and Secondary Practice Location'::text
                    WHEN (npi_address.is_primary_practice_location AND npi_address.is_secondary_practice_location) THEN 'Primary Practice Location and Secondary Practice Location'::text
                    WHEN npi_address.is_mailing_address THEN 'Mailing Address'::text
                    WHEN npi_address.is_primary_practice_location THEN 'Practice Location'::text
                    WHEN npi_address.is_secondary_practice_location THEN 'Secondary Practice Location'::text
                    ELSE NULL::text
                END AS address_type
           FROM claims.npi_address
        )
 SELECT DISTINCT ON (p.npi) p.npi,
    min(n.name) AS name,
    p.group_type,
    p.created_date,
    min(t.primary_code) AS primary_code,
    min(t.primary_display_name) AS primary_display_name,
    min(t.secondary_taxonomy_codes) AS secondary_taxonomy_codes,
    min(t.secondary_taxonomy_display_names) AS secondary_taxonomy_display_names,
    array_agg(a.address_id) AS addresses,
    array_agg(phone.phone_number) AS telephone_numbers,
    array_agg(fax.fax_number) AS faxes,
    array_agg(a.address_type) AS address_types
   FROM (((((((claims.npi_provider p
     LEFT JOIN npi_simple_name n ON ((n.npi = p.npi)))
     LEFT JOIN npi_taxonomy_simple t ON ((t.npi = p.npi)))
     LEFT JOIN npi_address_simple a ON ((a.npi = p.npi)))
     LEFT JOIN claims.npi_phone ph ON ((ph.npi = p.npi)))
     LEFT JOIN claims.phone USING (phone_id))
     LEFT JOIN claims.npi_fax f ON ((f.npi = p.npi)))
     LEFT JOIN claims.fax USING (fax_id))
  WHERE ((p.deactivation_date IS NULL) OR (p.reactivation_date >= p.deactivation_date))
  GROUP BY p.npi;

Indexes:

• npi_provider_view_npi_key (Unique) - npi

Column	Data Type	Indexed
npi	bigint	No
name	text	No
group_type	npi_group_type	No
created_date	date	No
primary_code	text	No
primary_display_name	text	No
secondary_taxonomy_codes	text[]	No
secondary_taxonomy_display_names	text[]	No
addresses	bigint[]	No
telephone_numbers	text[]	No
faxes	text[]	No
address_types	text[]	No