Introduction
Veeva Network now enables customers to access the OpenData master database through the SQL Query Editor. You can query, analyze, and report on data from both your Network instance and the OpenData master instance to support your business operations.
Here are five common scenarios along with sample SQL queries using this feature.
Scenario 1: Count HCPs in OpenData
Verteo Biopharma is concentrating its efforts on oncology. They would like to know the total number of HCPs specializing in Oncology in the US, as well as the number of oncology HCPs that have been covered by Verteo Pharma.
If you have a similar need:
- Modify the SQL query to suit your specific requirements and run the query.
- Save the query as a report. This way, you can access it easily and quickly from your Saved Report list whenever you need it.
Report result
Sample SQL
SELECT
'Verteo_HCPs' AS Source,
COUNT (vid__v)
FROM
hcp
WHERE
specialties IN ( 'ON', '19', 'PONC', '19_2', '19_3' )
AND hcp_status__v = 'A'
AND is_veeva_master__v = 'TRUE'
UNION
SELECT
'OpenData_HCPs' AS Source,
COUNT (vid__v)
FROM
opendata_na.hcp
WHERE
specialties IN ('ON','19','PONC', '19_2', '19_3' )
AND hcp_status__v = 'A'
Scenario 2: List out the potential target HCPs that have not yet been downloaded
Continuing with the previous scenario, Verteo Biopharma would like to obtain a list of HCPs that have not existed in their Network, along with detailed information about each of them. The list needs to be able to be exported to an Excel file for further analysis.
If you have a similar need:
- Modify the SQL query to suit your specific requirements and run the query.
- Click the Download Report button to download the result into an Excel file.
Report result
Sample SQL
SELECT
odhcp.vid__v,
formatted_name__v,
npi_num__v,
hcp_type__v,
odhcp.medical_degree_1__v,
odhcp.specialty_1__v,
odaddress.formatted_address__v,
listagg (
DISTINCT fl.ancestor_name__v,
'| ') AS "Health System"
FROM
opendata_na.hcp odhcp LEFT OUTER JOIN opendata_na.address odaddress
ON odhcp.vid__v = odaddress.entity_vid__v
AND odaddress.address_status__v = 'A'
AND address_ordinal__v = 1
AND odaddress.record_state__v = 'VALID' LEFT OUTER JOIN opendata_na.flat_hierarchy fl
ON fl.entity_vid__v = odhcp.vid__v
AND hco_type__v_ancestor = '4:37'
WHERE
odhcp.specialties IN ( 'ON', '19', 'PONC', '19_2', '19_3' )
AND hcp_status__v = 'A'
AND odhcp.vid__v NOT IN (
SELECT
vid__v
FROM
hcp
WHERE
specialties IN ( 'ON', '19', 'PONC', '19_2', '19_3' )
)
GROUP BY
odhcp.vid__v,
odhcp.formatted_name__v,
npi_num__v,
hcp_type__v,
odhcp.medical_degree_1__v,
odhcp.specialty_1__v,
odaddress.formatted_address__v
ORDER BY
odhcp.vid__v
Scenario 3: Find the missing parents related to the downloaded HCPs/HCOs
Verteo Biopharma would like to obtain a comprehensive hierarchy view of its target HCPs. They have discovered that some of the parent HCOs of the target HCPs have not been downloaded. Verteo Biopharma would like to receive a complete list of these missing HCOs in order to decide whether or not to download them.
If you have similar need:
- Modify the SQL query to suit your specific requirements and run the query.
- Click the Download Report button to download the result into an Excel file.
Sample SQL
SELECT
hco.vid__v,
hco.corporate_name__v,
hco.hco_type__v,
listagg (distinct fl.ancestor_name__v,'| ') as "Health Systems"
FROM
opendata_na.hco left join opendata_na.flat_hierarchy fl on hco.vid__v=entity_vid__v and fl.hco_type__v_ancestor ='4:37'
WHERE
hco.vid__v IN (
SELECT
DISTINCT us_opendata_hcos.parent_hco_vid__v
FROM
(
SELECT
parenthco.parent_hco_vid__v,
parenthco.vid__v,
parenthco.entity_vid__v
FROM
hcp INNER JOIN opendata_na.parenthco
ON hcp.vid__v = parenthco.entity_vid__v INNER JOIN opendata_na.hco
ON hco.vid__v = parenthco.parent_hco_vid__v
WHERE
parenthco.parent_hco_status__v = 'A'
) us_opendata_hcos LEFT OUTER JOIN (
SELECT
hco.vid__v
FROM
hco
WHERE
hco_status__v = 'A'
AND primary_country__v = 'US'
AND record_owner_type__v = 'VOD'
) downloaded_us_hcos
ON downloaded_us_hcos.vid__v = us_opendata_hcos.parent_hco_vid__v
WHERE
downloaded_us_hcos.vid__v IS NULL
)
group by hco.vid__v,
hco.corporate_name__v,
hco.hco_type__v
Scenario 4: Join custom tables with OpenData
Verteo Biopharma has obtained a list of NPI numbers from another source. They would like to have all these HCOs/HCPs in Network. To accomplish this, it is necessary to know which of them already exist in Verteo Network, which ones are only present in OpenData and need to be downloaded, and which ones do not exist and must be created through DCRs.
If you have similar requirement:
- Use the NPI list file to create a custom table.
- Modify the SQL query to suit your specific requirements and run the query.
- Click the Download Report button to download the result into an Excel file.
Report result
Sample SQL
SELECT
ct.npi, nt.vid__v, nt.Name, nt.record_type,nt.source
FROM
npi_list__ct ct LEFT OUTER JOIN (
SELECT
vid__v,record_state__v, npi_num__v,formatted_name__v AS "Name",
'HCP' AS record_type, 'Verteo Network' AS "Source"
FROM
hcp
WHERE
npi_num__v IS NOT NULL
UNION
SELECT
vid__v, record_state__v,npi_num__v,corporate_name__v AS "Name",
'HCO' AS record_type, 'Verteo Network' AS "Source"
FROM
hco
WHERE
npi_num__v IS NOT NULL
UNION
SELECT
vid__v, record_state__v,npi_num__v,formatted_name__v AS "Name",
'HCP' AS record_type, 'OpenData' AS "Source"
FROM
opendata_na.hcp
WHERE
npi_num__v IS NOT NULL
AND npi_num__v NOT IN (
SELECT
npi_num__v
FROM
hcp
WHERE
npi_num__v IN (
SELECT
npi
FROM
npi_list__ct
)
)
UNION
SELECT
vid__v,record_state__v,npi_num__v,corporate_name__v AS "Name",
'HCO' AS record_type, 'OpenData' AS "Source"
FROM
opendata_na.hco
WHERE
npi_num__v IS NOT NULL
AND npi_num__v NOT IN (
SELECT
npi_num__v
FROM
hco
WHERE
npi_num__v IN (
SELECT
npi
FROM
npi_list__ct
)
)
) nt
ON ct.npi = nt.npi_num__v
Order by source
Scenario 5: Query flattened hierarchy from OpenData
Verteo Biopharma would like to obtain a count of oncologists and hospitals in each health system and compare those numbers to the data in their own network instance.
If you have similar requirement:
- Modify the SQL query to suit your specific requirements and run the query.
- Save the query as a report. This way, you can access it easily and quickly from your Saved Report list whenever you need it.
Report result
Sample SQL
SELECT
od_hospital.vid AS "Health System VID",
od_hospital.Name AS "Health System Name",
od_hospital.Hospitals AS "OpenData Hospitals",
verteo_hospital.Hospitals AS "Verteo Hospitals",
od_hcp.hcps AS "OpenData HCPs",
verteo_hcp.hcps AS "Verteo HCPs"
FROM
(
SELECT
ancestor_vid__v AS "vid",ancestor_name__v AS "Name"
FROM
opendata_na.flat_hierarchy
WHERE
hco_type__v_ancestor = '4:37'
GROUP BY
ancestor_vid__v, ancestor_name__v
) odfl LEFT JOIN (
SELECT
ancestor_vid__v AS "vid",ancestor_name__v AS "Name",
COUNT (entity_vid__v) AS "Hospitals"
FROM
opendata_na.flat_hierarchy
WHERE
hco_type__v_ancestor = '4:37'
AND major_class_of_trade__v_entity = '32'
GROUP BY
ancestor_vid__v,ancestor_name__v
) od_hospital
ON odfl.vid = od_hospital.vid LEFT JOIN (
SELECT
ancestor_vid__v AS "vid", ancestor_name__v AS "Name",
COUNT (entity_vid__v) AS "Hospitals"
FROM
flat_hierarchy
WHERE
hco_type__v_ancestor = '4:37'
AND major_class_of_trade__v_entity = '32'
GROUP BY
ancestor_vid__v, ancestor_name__v
) verteo_hospital
ON verteo_hospital.vid = odfl.vid LEFT JOIN (
SELECT
ancestor_vid__v AS "vid", ancestor_name__v AS "Name",
COUNT (fh.entity_vid__v) AS "HCPs"
FROM
opendata_na.flat_hierarchy fh JOIN opendata_na.hcp
ON fh.entity_vid__v = hcp.vid__v
WHERE
fh.hco_type__v_ancestor = '4:37'
AND fh.entity_type__v = 'HCP'
AND hcp.specialties IN ( 'ON', '19', 'PONC', '19_2', '19_3' )
GROUP BY
ancestor_vid__v,ancestor_name__v
) od_hcp
ON od_hcp.vid = odfl.vid LEFT JOIN (
SELECT
ancestor_vid__v AS "vid",ancestor_name__v AS "Name",
COUNT (fh.entity_vid__v) AS "HCPs"
FROM
flat_hierarchy fh JOIN hcp
ON fh.entity_vid__v = hcp.vid__v
WHERE
fh.hco_type__v_ancestor = '4:37'
AND fh.entity_type__v = 'HCP'
AND hcp.specialties IN ( 'ON', '19', 'PONC', '19_2', '19_3')
GROUP BY
ancestor_vid__v, ancestor_name__v
) verteo_hcp
ON verteo_hcp.vid = odfl.vid
WHERE
verteo_hcp.hcps <> 0 OR od_hcp.hcps <> 0 OR verteo_hospital.Hospitals <> 0 OR od_hospital.Hospitals <> 0
See the following topic for more information: