Network Reporting samples

AD
DM
DS

The following sections provide practical samples using both SQL queries and basic or aggregate queries using the Report Builder.

HCPs by region/specialty

This example finds cardiovascular disease specialists licensed in the state of New York.

SELECT
        hcp.vid__v,
        first_name__v,
        last_name__v,
        medical_degree_1__v,
        specialty_1__v,
        specialty_2__v,
        formatted_address__v,
        type_value__v,
        license_number__v
    FROM
        hcp JOIN address
            ON hcp.vid__v = address.entity_vid__v
        AND address_ordinal__v = 1 JOIN license
            ON hcp.vid__v = license.entity_vid__v
        AND type_value__v = 'NY'
        AND best_state_license__v = 'Y'
    WHERE
        specialty_1__v = 'CD'
    ORDER BY
        vid__v DESC ;

Sample results:

Note: There is no need to filter on address ordinal since basic report queries automatically select the best address that meets your criteria. You can also return the name of the primary HCO related to the respective HCPs.

HCO counts by country and class of trade

This example provides a breakdown of HCOs by class of trade and country. It also illustrates the use of group by for statistics and shorthand notation for group by and order by.

SELECT primary_country__v,
        major_class_of_trade__v,
        Count (primary_country__v)  FROM   hco WHERE  hco_status__v  =   'A'  GROUP  BY 1,
           2 ORDER  BY 3 DESC ;

Sample results:

HCOs without addresses

This example illustrates subquery capability and performs a common data quality check.

SELECT
        vid__v,
        corporate_name__v
    FROM
        hco
    WHERE
        hco_status__v = 'A'
        AND NOT EXISTS (
            SELECT
                    entity_vid__v
                FROM
                    address
                WHERE
                    entity_vid__v = hco.vid__v
                    AND address_status__v = 'A'
        )

Sample results:

HCO average hospital size by region

This example shows the breakdown by state of the average number of MDs and DOs working at hospitals. It also illustrates summarizing numerical fields with other functions such as average, sum, and so on.

SELECT
        administrative_area__v,
        AVG (count_all_locn_md_do__v)
    FROM
        hco JOIN address
            ON entity_vid__v = hco.vid__v
        AND address_ordinal__v = 1
    WHERE
        hco_status__v = 'A'
        AND count_all_locn_md_do__v IS NOT NULL
        AND hco_type__v IN (
            '1_22',
            '4:6'
        )
    GROUP BY
        administrative_area__v
    ORDER BY
        2 DESC ;

Sample results:

HCPs with hyphenated last names

This example finds all HCPs with a hyphen in their last name and demonstrates regex support. 

SELECT
        vid__v,
        first_name__v,
        last_name__v
    FROM
        hcp
    WHERE
        last_name__v similar TO '[a-zA-Z]+[-]{1}[a-zA-Z]+'
        AND hcp_status__v = 'A' ;

Sample results:

Recently added HCOs

This example is a very common query that includes custom keys and addresses.

SELECT
        hco.created_date__v,
        hco.vid__v,
        corporate_name__v,
        hco_type__v,
        formatted_address__v,
        custom_key_value__v AS crm_id
    FROM
        hco JOIN address
            ON address.entity_vid__v = hco.vid__v
        AND address_ordinal__v = 1 JOIN customkey
            ON customkey.custom_key_entity_id__v = hco.vid__v
        AND custom_key_source_type__v LIKE 'VCRM%'
        AND custom_key_status__v = 'A'
        AND Datediff (
            'day',
            hco.created_date__v,
            CURRENT_DATE
        ) <= 5

Note: The % wildcard character is not required.

Sample results:

HCP number of specialties

This query uses the EXPLODE expression to display the number of HCPs that have a particular specialty. The EXPLODE function scans all ten specialty fields.

SELECT
        specialty,
        COUNT (
            DISTINCT vid__v
        )
    FROM
        (
            SELECT
                    vid__v,
                    EXPLODE specialties AS specialty
                FROM
                    hcp
                WHERE
                    specialty IS NOT NULL
        )
    GROUP BY
        specialty

Sample results:

For more information about using the EXPLODE expression, see the SQL Reference.