Network Reporting samples
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.