Informatics Metric OMOP BigQuery.sql

/*
CTSA Common Metrics 2022 
	OMOP 
	BigQuery
Author : Robert Miller, Tufts CTSI
https://github.com/ncats/CTSA-Metrics

Structure:
	1) *** user input variables ***
	2) clear potentially leftover temp tables
	3) generate prerequisite temp tables
	4) generate metrics 

Assumes:
	Ability to create temp tables
	Read access to OMOP CDM clinical and vocabulary tables
		-if your site stores the clinical and vocabulary tables in separate databases or schemas you will need to update the table references accordingly 

References:
	Clinical tables:	PERSON, DEATH, DRUG_EXPOSURE, DEVICE_EXPOSURE, OBSERVATION, MEASUREMENT, PROCEDURE_OCCURRENCE, CONDITION_OCCURRENCE
	Vocabulary tables:	CONCEPT, CONCEPT_RELATIONSHIP, CONCEPT_ANCESTOR
	Vocabularies:		ICD9CM, ICD10CM, SNOMED, LOINC, CPT4, HCPCS, RxNorm, ICD9Proc, ICD10PCS

*/ 



--1) *** user input variables ***
DROP TABLE IF EXISTS #tmp_cm22_user_input;
/* 
-------------------------
	START user input
*/
CREATE TABLE #tmp_cm22_user_input
 DISTSTYLE ALL
AS
SELECT
	2 as data_model				-- Which CDM are you using?								(1=PCORNet, 2=OMOP, 3=TriNetX, 4=i2b2/ACT)
	,NULL as nlp_any			-- Does your hub have any NLP capabilities?				(0=No, 1=Yes, NULL=unknown)
	,NULL as text_search		-- Does your site have free text search capabilities?	(0=No, 1=Yes, NULL=unknown)
;
/* 
	END user input 
-------------------------	
*/


--2) clear potentially leftover temp tables
DROP TABLE IF EXISTS #tmp_cm22_vital_concepts;
DROP TABLE IF EXISTS #tmp_cm22_smoking_concepts;
DROP TABLE IF EXISTS #tmp_cm22_c1yr;
DROP TABLE IF EXISTS #tmp_cm22_c5yr;
DROP TABLE IF EXISTS #tmp_cm22_concept_checks;
DROP TABLE IF EXISTS #concept_checks_1yr;
DROP TABLE IF EXISTS #concept_checks_5yr;
--3) generate prerequisite temp tables
-- vital sign concepts
CREATE TABLE #tmp_cm22_vital_concepts
 DISTSTYLE ALL
AS
SELECT
conc.*
FROM
CONCEPT_ANCESTOR ca
INNER JOIN CONCEPT conc
ON ancestor_concept_id IN
(
	3036277
	,3025315
	,45876174
	,1002813
	,4245997
	,1004059
	,4178505
)
AND ca.descendant_concept_id = conc.concept_id
AND conc.standard_concept = 'S'
;
-- smoking concepts
CREATE TABLE #tmp_cm22_smoking_concepts
 DISTSTYLE ALL
AS
SELECT
conc.*
FROM
CONCEPT_RELATIONSHIP cr 
INNER JOIN CONCEPT conc
ON cr.concept_id_1 IN
(
	SELECT concept_id
	FROM CONCEPT
	WHERE concept_code in 
	(
		'Z87.891'
		,'F17.20'
		,'F17.200'
		, 'F17.201'
		, 'F17.203'
		, 'F17.208'
		, 'F17.209'
		, 'F17.21'
		, 'F17.210'
		, 'F17.211'
		, 'F17.213'
		, 'F17.218'
		, 'F17.219'
	)
)
AND cr.concept_id_2 = conc.concept_id
AND conc.standard_concept = 'S'
AND cr.relationship_id = 'Maps to'
;
-- Generate 1yr cohort 
-- cohort definition : any fact in timeframe (diagnosis, procedure, drug, device, measurement, observation) 
CREATE TABLE #tmp_cm22_c1yr
DISTKEY(person_id)
AS
SELECT
DISTINCT  person_id 
FROM
condition_occurrence 
WHERE condition_start_date BETWEEN '01-01-2021' AND '12-31-2021'
UNION 
SELECT DISTINCT person_id
FROM procedure_occurrence 
WHERE procedure_date BETWEEN '01-01-2021' AND '12-31-2021'
UNION 
SELECT DISTINCT person_id
FROM drug_exposure
WHERE drug_exposure_start_date BETWEEN '01-01-2021' AND '12-31-2021'
UNION 
SELECT DISTINCT person_id
FROM device_exposure
WHERE device_exposure_start_date BETWEEN '01-01-2021' AND '12-31-2021'
UNION 
SELECT DISTINCT person_id
FROM measurement
WHERE measurement_date BETWEEN '01-01-2021' AND '12-31-2021'
UNION 
SELECT DISTINCT person_id
FROM observation
WHERE observation_date BETWEEN '01-01-2021' AND '12-31-2021'
;
-- Generate 5yr cohort
CREATE TABLE #tmp_cm22_c5yr
DISTKEY(person_id)
AS
SELECT
DISTINCT  person_id 
FROM
condition_occurrence 
WHERE condition_start_date BETWEEN '01-01-2017' AND '12-31-2021'		
UNION 
SELECT DISTINCT person_id
FROM procedure_occurrence 
WHERE procedure_date BETWEEN '01-01-2017' AND '12-31-2021'	
UNION 
SELECT DISTINCT person_id
FROM drug_exposure
WHERE drug_exposure_start_date BETWEEN '01-01-2017' AND '12-31-2021'
UNION 
SELECT DISTINCT person_id
FROM device_exposure
WHERE device_exposure_start_date BETWEEN '01-01-2017' AND '12-31-2021'
UNION 
SELECT DISTINCT person_id
FROM measurement
WHERE measurement_date BETWEEN '01-01-2017' AND '12-31-2021'
UNION 
SELECT DISTINCT person_id
FROM observation
WHERE observation_date BETWEEN '01-01-2017' AND '12-31-2021'
;
-- Vocabulary utilization concept checks 
-- create list of concepts 
CREATE TABLE #tmp_cm22_concept_checks
 DISTSTYLE ALL
AS
SELECT
x.concept_id as src_concept_id
	,x.vocabulary_id as src_vocabulary_id
	,x.domain_id as src_domain_id
	,x.concept_code as src_concept_code
	,mapped_concept.concept_id as mapped_concept_id
	,mapped_concept.vocabulary_id as mapped_vocabulary_id
	,mapped_concept.domain_id as mapped_domain_id
	,mapped_concept.concept_code as mapped_concept_code
FROM
(
	SELECT *
	FROM concept
	WHERE vocabulary_id = 'ICD9CM'
	AND concept_code in ('174.9','244.9','250.00','268.9','272.0','272.4','278.00','285.9','300.00','305.1','311','327.23','401.1','401.9','414.00','414.01','427.31','428.0','462','465.9','477.9','493.90','496','530.81','599.0','715.90','719.46','724.2','724.5','729.1','729.5','733.00','780.79','784.0','786.05','786.2','786.50','789.00','V03.82','V04.81','V06.1','V10.3','V20.2','V22.0','V22.0','V22.1','V22.1','V43.3','V45.89','V54.89','V58.0','V58.11','V58.61','V58.69','V67.09','V70.0','V72.31','V76.12','V76.2')
	UNION
	SELECT *
	FROM concept
	WHERE vocabulary_id = 'ICD10CM'
	AND concept_code in ('D64.9','E03.9','E11.65','E11.65','E11.9','E55.9','E66.01','E66.9','E78.00','E78.2','E78.5','F17.200','F17.210','F17.210','F32.9','F41.1','F41.9','G47.33','G89.29','I10','I25.10','I48.0','I48.2','I48.91','J02.9','J06.9','J44.9','J45.909','K21.9','M19.90','M25.561','M25.562','M54.2','M54.5','N18.3','R05','R06.02','R07.9','R10.9','R53.83','R94.31','Y92.9','Y93.9','Z00.00','Z00.129','Z01.419','Z01.818','Z12.11','Z12.31','Z23','Z51.0','Z51.11','Z71.3','Z79.01','Z79.4','Z79.82','Z79.899','Z87.891','Z98.890')
	UNION
	SELECT *
	FROM concept
	WHERE vocabulary_id = 'LOINC'
	AND concept_code in ('10466-1','1742-6','1744-2','1751-7','17861-6','19123-9','1920-8','1960-4','1962-0','1963-8','1975-2','2019-8','2028-9','20564-1','20570-8','2075-0','2160-0','2339-0','2345-7','26450-7','26485-3','2703-7','2708-6','2711-0','2713-6','2744-1','2777-1','2823-3','2885-2','2951-2','30180-4','3094-0','3097-3','32623-1','33037-3','33914-3','4544-3','48642-3','48643-1','51731-8','58413-6','5902-2','5905-5','61152-5','62238-1','6301-6','6463-4','66746-9','6690-2','6768-6','704-7','706-2','711-2','713-8','71695-1','718-7','731-0','736-9','742-7','751-8','770-8','777-3','785-6','786-4','787-2','788-0','789-8','8310-5','933-2','934-0')
	UNION
	SELECT *
	FROM concept
	WHERE vocabulary_id = 'RxNorm'
	AND concept_code in ('834023','2418','6918','104491','105029','1050490','1189779','1246235','1360292','141928','1495474','1495476','152698','152923','153892','1664448','205770','206765','206766','206821','208161','209387','209459','212340','212549','212575','213169','261224','284400','311036','540472','540848','541713','541815','541872','542673','542678','617314','617318','617320','628530','824194','828350','835605','859096','861006','861008','863671','865098','866516','967525','1049221','998740','1115005','1361615','1437702','1049621','876193','866924','857002','1658634','1659137','1665088','1732006','1735003','1735004','1740467','1807628','1807632','1807633','198440','310273','310431','313782','314200','318272','617311','834127','854235','1797907','1795964','901044','1490493')
	UNION
	SELECT *
	FROM concept
	WHERE vocabulary_id = 'CPT4'
	AND concept_code in ('00790','01967','36415','70450','71045','80048','80051','80053','80061','80076','81000','81002','81003','82043','82306','82310','82330','82565','82607','82805','82947','82962','83036','83605','83615','83690','83735','84100','84132','84153','84295','84439','84443','84450','84460','84484','84520','84550','84999','85004','85014','85025','85027','85610','85652','85730','86140','86850','86900','86901','87040','87070','87086','88185','88305','88399','90460','90471','93000','93005','93010','94640','95004','97530','99024','99203','99212','99213','99214','99215','99232','99233','99244','99283','99284','99285')
	UNION
	SELECT *
	FROM concept
	WHERE vocabulary_id = 'HCPCS'
	AND concept_code in ('A7003','A9585','A9999','E1399','G0008','G0009','G0105','G0121','G0378','G0444','J0131','J0133','J0171','J0461','J0583','J0585','J0690','J0692','J0712','J0878','J1030','J1040','J1050','J1100','J1442','J1642','J1644','J1650','J1756','J1815','J1953','J2001','J2185','J2248','J2250','J2370','J2405','J2543','J2704','J2710','J2795','J3010','J3301','J3420','J3475','J3480','J3490','J7030','J7185','J7187','J7189','J7192','J7195','J7323','J7512','J7613','J8540','J9250','J9263','J9267','Q0091','Q0162','Q5101','Q9959','Q9967') 
	UNION
	SELECT *
	FROM concept
	WHERE vocabulary_id = 'ICD9Proc'
	AND concept_code in ('00.40','00.66','03.31','03.91','03.92','04.81','13.41','13.71','33.24','37.22','38.91','38.93','38.97','39.95','42.92','45.13','45.16','45.23','45.25','45.42','51.23','54.91','57.32','57.94','59.8','64.0','73.59','74.1','75.69','80.51','81.54','81.62','81.92','86.04','86.3','86.59','87.03','88.41','88.53','88.56','88.72','93.39','93.54','93.83','96.04','96.71','96.72','99.04','99.23','99.29') 
	UNION
	SELECT *
	FROM concept
	WHERE vocabulary_id = 'ICD10PCS'
	AND concept_code in ('009U3ZX','01NB0ZZ','027034Z','02HV33Z','03HY32Z','0BH17EZ','0BJ08ZZ','0CJS8ZZ','0DB68ZX','0DB98ZX','0DH67UZ','0DJ08ZZ','0DJD8ZZ','0FT44ZZ','0HQ9XZZ','0KQM0ZZ','0SRC0J9','0SRD0J9','0T9B70Z','0U7C7ZZ','0VTTXZZ','0W9930Z','0W9B30Z','10907ZC','10D00Z1','10E0XZZ','30233K1','30233N1','30233R1','3E033VJ','3E0436Z','3E0G76Z','4A023N7','4A10X4G','5A1221Z','5A1935Z','5A1945Z','5A1955Z','5A1D70Z','5A2204Z','8E0W4CZ','B020ZZZ','B030ZZZ','B2111ZZ','B2151ZZ','B24BZZ4','B32R1ZZ','BW28ZZZ','HZ2ZZZZ') 
) x
LEFT OUTER JOIN concept_relationship cr
ON x.concept_id = cr.concept_id_1
AND cr.relationship_id = 'Maps to'
AND cr.invalid_reason IS NULL
LEFT OUTER JOIN concept mapped_concept
ON cr.concept_id_2 = mapped_concept.concept_id
;
-- Concept check - 1yr
CREATE TABLE #concept_checks_1yr 
 DISTSTYLE ALL
AS
SELECT
src_vocabulary_id, count(distinct cc.src_concept_code) as denominator, count(distinct x.src_concept_id) as numerator 
FROM
#tmp_cm22_concept_checks cc
LEFT OUTER JOIN
(
	SELECT DISTINCT src_concept_id
	FROM #tmp_cm22_concept_checks
	WHERE mapped_concept_id IN
	(
		SELECT DISTINCT condition_concept_id as concept_id
		FROM condition_occurrence cond
		INNER JOIN #tmp_cm22_c1yr c1yr
		ON cond.person_id = c1yr.person_id
		AND cond.condition_start_date BETWEEN '01-01-2021' AND '12-31-2021' 
		UNION
		SELECT DISTINCT procedure_concept_id as concept_id
		FROM procedure_occurrence y
		INNER JOIN #tmp_cm22_c1yr c1yr
		ON y.person_id = c1yr.person_id
		AND y.procedure_date BETWEEN '01-01-2021' AND '12-31-2021' 
		UNION
		SELECT DISTINCT drug_concept_id as concept_id
		FROM drug_exposure y
		INNER JOIN #tmp_cm22_c1yr c1yr
		ON y.person_id = c1yr.person_id
		AND y.drug_exposure_start_date BETWEEN '01-01-2021' AND '12-31-2021' 
		UNION
		SELECT DISTINCT observation_concept_id as concept_id
		FROM observation y
		INNER JOIN #tmp_cm22_c1yr c1yr
		ON y.person_id = c1yr.person_id
		AND y.observation_date BETWEEN '01-01-2021' AND '12-31-2021' 
		UNION
		SELECT DISTINCT measurement_concept_id as concept_id
		FROM measurement y
		INNER JOIN #tmp_cm22_c1yr c1yr
		ON y.person_id = c1yr.person_id
		AND y.measurement_date BETWEEN '01-01-2021' AND '12-31-2021' 
	)
) x
ON cc.src_concept_id = x.src_concept_id
group by src_vocabulary_id
;
-- Concept check - 5yr
CREATE TABLE #concept_checks_5yr
 DISTSTYLE ALL
AS
SELECT
src_vocabulary_id, count(distinct cc.src_concept_code) as denominator, count(distinct x.src_concept_id) as numerator 
FROM
#tmp_cm22_concept_checks cc
LEFT OUTER JOIN
(
	SELECT DISTINCT src_concept_id
	FROM #tmp_cm22_concept_checks
	WHERE mapped_concept_id IN
	(
		SELECT DISTINCT condition_concept_id as concept_id
		FROM condition_occurrence cond
		INNER JOIN #tmp_cm22_c1yr c1yr
		ON cond.person_id = c1yr.person_id
		AND cond.condition_start_date BETWEEN '01-01-2017' AND '12-31-2021' 
		UNION
		SELECT DISTINCT procedure_concept_id as concept_id
		FROM procedure_occurrence y
		INNER JOIN #tmp_cm22_c1yr c1yr
		ON y.person_id = c1yr.person_id
		AND y.procedure_date BETWEEN '01-01-2017' AND '12-31-2021' 
		UNION
		SELECT DISTINCT drug_concept_id as concept_id
		FROM drug_exposure y
		INNER JOIN #tmp_cm22_c1yr c1yr
		ON y.person_id = c1yr.person_id
		AND y.drug_exposure_start_date BETWEEN '01-01-2017' AND '12-31-2021' 
		UNION
		SELECT DISTINCT observation_concept_id as concept_id
		FROM observation y
		INNER JOIN #tmp_cm22_c1yr c1yr
		ON y.person_id = c1yr.person_id
		AND y.observation_date BETWEEN '01-01-2017' AND '12-31-2021' 
		UNION
		SELECT DISTINCT measurement_concept_id as concept_id
		FROM measurement y
		INNER JOIN #tmp_cm22_c1yr c1yr
		ON y.person_id = c1yr.person_id
		AND y.measurement_date BETWEEN '01-01-2017' AND '12-31-2021' 
	)
) x
ON cc.src_concept_id = x.src_concept_id
group by src_vocabulary_id
;
-- 4) Generate output metrics 
SELECT 
	'data_model' as variable_name
	,(SELECT TOP 1 data_model from #tmp_cm22_user_input) as one_year -- 2 = OMOP
	,(SELECT TOP 1 data_model from #tmp_cm22_user_input) as five_year -- 2 = OMOP
UNION
SELECT
	'nlp_any' as variable_name
	,(SELECT TOP 1 nlp_any from #tmp_cm22_user_input) as one_year 
	,(SELECT TOP 1 nlp_any from #tmp_cm22_user_input) as five_year
UNION
SELECT
	'text_search' as variable_name
	,(SELECT TOP 1 text_search from #tmp_cm22_user_input) as one_year 
	,(SELECT TOP 1 text_search from #tmp_cm22_user_input) as five_year
UNION 
SELECT 
'total_patients' as variable_name
,(
	SELECT COUNT(DISTINCT person_id) 
	FROM #tmp_cm22_c1yr
) as one_year
,(
	SELECT COUNT(DISTINCT person_id) 
	FROM #tmp_cm22_c5yr
) as five_year
UNION 
SELECT 
'uniq_pt_with_age' as variable_name
,(
	SELECT COUNT(DISTINCT per.person_id) 
	FROM #tmp_cm22_c1yr x
	INNER JOIN person per
	ON x.person_id = per.person_id
	AND per.birth_datetime IS NOT NULL
) as one_year
,(
	SELECT COUNT(DISTINCT per.person_id) 
	FROM #tmp_cm22_c5yr x
	INNER JOIN person per
	ON x.person_id = per.person_id
	AND per.birth_datetime IS NOT NULL
) as five_year
UNION 
SELECT 
'uniq_pt_age_over_120' as variable_name
,(
	SELECT COUNT(DISTINCT x.person_id) 
	FROM 
	(
		-- " DOD-DOB > 120 "
		SELECT DISTINCT per.person_id 
		FROM person per
		INNER JOIN death dth 
		ON per.person_id = dth.person_id
		AND DATEDIFF(year, per.birth_datetime, dth.death_date) > 120
		-- No DOD, 12/31-2021 - DOB > 120
		UNION
		SELECT DISTINCT person_id 
		FROM person 
		WHERE DATEDIFF(year, birth_datetime, '12-31-2022') > 120
		AND person_id NOT IN (SELECT person_id FROM death)
	) per
	INNER JOIN #tmp_cm22_c1yr x
	ON per.person_id = x.person_id
) as one_year
,(
	SELECT COUNT(DISTINCT x.person_id) 
	FROM 
	(
		-- " DOD-DOB > 120 "
		SELECT per.person_id 
		FROM person per
		INNER JOIN death dth 
		ON per.person_id = dth.person_id
		AND DATEDIFF(year, per.birth_datetime, dth.death_date) > 120
		-- No DOD, 12/31-2021 - DOB > 120
		UNION
		SELECT person_id 
		FROM person 
		WHERE DATEDIFF(year, birth_datetime, '12-31-2022') > 120
		AND person_id NOT IN (SELECT person_id FROM death)
	) per
	INNER JOIN #tmp_cm22_c5yr x
	ON per.person_id = x.person_id
) as five_year
UNION 
SELECT 
'uniq_pt_with_gender' as variable_name
,(
	SELECT COUNT(DISTINCT per.person_id) 
	FROM person per
	INNER JOIN #tmp_cm22_c1yr x
	ON per.person_id = x.person_id
	AND per.gender_concept_id IN (
		8507		-- Male
		,8532		-- Female
		,8570		-- Ambiguous
		,8521		-- Other
	)
) as one_year
,(
	SELECT COUNT(DISTINCT per.person_id) 
	FROM person per
	INNER JOIN #tmp_cm22_c5yr x
	ON per.person_id = x.person_id
	AND per.gender_concept_id IN (
		8507		-- Male
		,8532		-- Female
		,8570		-- Ambiguous
		,8521		-- Other
	)
) as five_year
UNION 
SELECT 
'uniq_pt_loinc' as variable_name
,(
	SELECT COUNT(DISTINCT x.person_id) 
	FROM (
		SELECT DISTINCT person_id
		FROM measurement meas
		INNER JOIN concept conc
		ON conc.vocabulary_id = 'LOINC'
		AND meas.measurement_concept_id = conc.concept_id
		AND measurement_date BETWEEN '01-01-2021' AND '12-31-2021'
		UNION 
		SELECT DISTINCT person_id
		FROM observation obs
		INNER JOIN concept conc
		ON conc.vocabulary_id = 'LOINC'
		AND obs.observation_concept_id = conc.concept_id
		AND observation_date BETWEEN '01-01-2021' AND '12-31-2021'
	) per
	INNER JOIN #tmp_cm22_c1yr x
	ON per.person_id = x.person_id
) as one_year
,(
	SELECT COUNT(DISTINCT x.person_id) 
	FROM (
		SELECT DISTINCT person_id
		FROM measurement meas
		INNER JOIN concept conc
		ON conc.vocabulary_id = 'LOINC'
		AND meas.measurement_concept_id = conc.concept_id
		AND measurement_date BETWEEN '01-01-2017' AND '12-31-2021'
		UNION 
		SELECT DISTINCT person_id
		FROM observation obs
		INNER JOIN concept conc
		ON conc.vocabulary_id = 'LOINC'
		AND obs.observation_concept_id = conc.concept_id
		AND observation_date BETWEEN '01-01-2017' AND '12-31-2021'
	) per
	INNER JOIN #tmp_cm22_c5yr x
	ON per.person_id = x.person_id
) as five_year
UNION 
SELECT 
'uniq_pt_med_rxnorm' as variable_name
,(
	SELECT COUNT(DISTINCT x.person_id) 
	FROM drug_exposure drug
	INNER JOIN concept conc
	ON conc.vocabulary_id in ('RxNorm')
	AND drug.drug_concept_id = conc.concept_id
	AND drug_exposure_start_date BETWEEN '01-01-2021' AND '12-31-2021'
	INNER JOIN #tmp_cm22_c1yr x
	ON drug.person_id = x.person_id
) as one_year
,(
	SELECT COUNT(DISTINCT x.person_id) 
	FROM drug_exposure drug
	INNER JOIN concept conc
	ON conc.vocabulary_id in ('RxNorm')
	AND drug.drug_concept_id = conc.concept_id
	AND drug_exposure_start_date BETWEEN '01-01-2017' AND '12-31-2021'
	INNER JOIN #tmp_cm22_c5yr x
	ON drug.person_id = x.person_id
) as five_year
UNION 
SELECT 
'uniq_pt_icd_dx' as variable_name
,(
	SELECT COUNT(DISTINCT x.person_id)
	FROM condition_occurrence cond
	INNER JOIN #tmp_cm22_c1yr x
	ON cond.person_id = x.person_id
	AND condition_concept_id IN 
	(
			SELECT cr.concept_id_2
			FROM concept c1
			INNER JOIN concept_relationship cr
			ON c1.concept_id = cr.concept_id_1
			AND relationship_id = 'Maps to'
			AND c1.vocabulary_id IN ('ICD9CM', 'ICD10CM')
	)
	AND condition_start_date BETWEEN '01-01-2021' AND '12-31-2021'
) as one_year
,(
	SELECT COUNT(DISTINCT x.person_id)
	FROM condition_occurrence cond
	INNER JOIN #tmp_cm22_c5yr x
	ON cond.person_id = x.person_id
	AND condition_concept_id IN 
	(
			SELECT cr.concept_id_2
			FROM concept c1
			INNER JOIN concept_relationship cr
			ON c1.concept_id = cr.concept_id_1
			AND relationship_id = 'Maps to'
			AND c1.vocabulary_id IN ('ICD9CM', 'ICD10CM')
	)
	AND condition_start_date BETWEEN '01-01-2017' AND '12-31-2021'
) as five_year
UNION 
SELECT 
'uniq_pt_snomed_dx' as variable_name
,(
	SELECT COUNT(DISTINCT x.person_id)
	FROM condition_occurrence cond
	INNER JOIN concept conc 
	ON conc.vocabulary_id = 'SNOMED'
	AND cond.condition_concept_id = conc.concept_id
	AND condition_start_date BETWEEN '01-01-2021' AND '12-31-2021'
	INNER JOIN #tmp_cm22_c1yr x
	ON cond.person_id = x.person_id
) as one_year
,(
	SELECT COUNT(DISTINCT x.person_id)
	FROM condition_occurrence cond
	INNER JOIN concept conc 
	ON conc.vocabulary_id = 'SNOMED'
	AND cond.condition_concept_id = conc.concept_id
	AND condition_start_date BETWEEN '01-01-2017' AND '12-31-2021'
	INNER JOIN #tmp_cm22_c5yr x
	ON cond.person_id = x.person_id
) as five_year
UNION 
SELECT 
'uniq_pt_icd_proc' as variable_name
,(
	SELECT COUNT(DISTINCT x.person_id)
	FROM procedure_occurrence proce
	INNER JOIN #tmp_cm22_c1yr x
	ON proce.person_id = x.person_id
	AND procedure_concept_id IN 
	(
			SELECT cr.concept_id_2
			FROM concept c1
			INNER JOIN concept_relationship cr
			ON c1.concept_id = cr.concept_id_1
			AND relationship_id = 'Maps to'
			AND c1.vocabulary_id IN ('ICD10PCS', 'ICD9Proc')
	)
	AND procedure_date BETWEEN '01-01-2021' AND '12-31-2021'
) as one_year
,(
	SELECT COUNT(DISTINCT x.person_id)
	FROM procedure_occurrence proce
	INNER JOIN #tmp_cm22_c5yr x
	ON proce.person_id = x.person_id
	AND procedure_concept_id IN 
	(
			SELECT cr.concept_id_2
			FROM concept c1
			INNER JOIN concept_relationship cr
			ON c1.concept_id = cr.concept_id_1
			AND relationship_id = 'Maps to'
			AND c1.vocabulary_id IN ('ICD10PCS', 'ICD9Proc')
	)
	AND procedure_date BETWEEN '01-01-2017' AND '12-31-2021'
) as five_year
UNION 
SELECT 
'uniq_pt_cpt' as variable_name
,(
	SELECT COUNT(DISTINCT x.person_id)
	FROM procedure_occurrence proce
	INNER JOIN concept conc
	ON conc.vocabulary_id IN ('CPT4', 'HCPCS')
	AND proce.procedure_concept_id = conc.concept_id
	AND procedure_date BETWEEN '01-01-2021' AND '12-31-2021'
	INNER JOIN #tmp_cm22_c1yr x
	ON proce.person_id = x.person_id
) as one_year
,(
	SELECT COUNT(DISTINCT x.person_id)
	FROM procedure_occurrence proce
	INNER JOIN concept conc
	ON conc.vocabulary_id IN ('CPT4', 'HCPCS')
	AND proce.procedure_concept_id = conc.concept_id
	AND procedure_date BETWEEN '01-01-2017' AND '12-31-2021'
	INNER JOIN #tmp_cm22_c5yr x
	ON proce.person_id = x.person_id
) as five_year
UNION 
SELECT 
'uniq_pt_snomed_proc' as variable_name
,(
	SELECT COUNT(DISTINCT x.person_id)
	FROM procedure_occurrence proce
	INNER JOIN concept conc
	ON conc.vocabulary_id = 'SNOMED'
	AND proce.procedure_concept_id = conc.concept_id
	AND procedure_date BETWEEN '01-01-2021' AND '12-31-2021'
	INNER JOIN #tmp_cm22_c1yr x
	ON proce.person_id = x.person_id
) as one_year
,(
	SELECT COUNT(DISTINCT x.person_id)
	FROM procedure_occurrence proce
	INNER JOIN concept conc
	ON conc.vocabulary_id = 'SNOMED'
	AND proce.procedure_concept_id = conc.concept_id
	AND procedure_date BETWEEN '01-01-2017' AND '12-31-2021'
	INNER JOIN #tmp_cm22_c5yr x
	ON proce.person_id = x.person_id
) as five_year
UNION 
SELECT 
'uniq_pt_note' as variable_name
,(
	SELECT COUNT(DISTINCT x.person_id)
	FROM note n
	INNER JOIN #tmp_cm22_c1yr x
	ON n.person_id = x.person_id
	AND note_date BETWEEN '01-01-2021' AND '12-31-2021'
) as one_year
,(
	SELECT COUNT(DISTINCT x.person_id)
	FROM note n
	INNER JOIN #tmp_cm22_c5yr x
	ON n.person_id = x.person_id
	AND note_date BETWEEN '01-01-2017' AND '12-31-2021'
) as five_year
UNION 
SELECT 
'uniq_pt_vital_sign' as variable_name
,(
	SELECT COUNT(distinct x.person_id) 
	FROM
	(
		SELECT DISTINCT person_id
		FROM MEASUREMENT
		WHERE measurement_concept_id IN 
		(
			SELECT concept_id
			FROM #tmp_cm22_vital_concepts
			WHERE domain_id = 'Measurement'
		)
		AND measurement_date BETWEEN '01-01-2021' AND '12-31-2021'
		UNION 
		SELECT DISTINCT person_id
		FROM OBSERVATION
		WHERE observation_concept_id IN 
		(
			SELECT concept_id
			FROM #tmp_cm22_vital_concepts
			WHERE domain_id = 'Observation'
		)
		AND observation_date BETWEEN '01-01-2021' AND '12-31-2021'
	) per
	INNER JOIN #tmp_cm22_c1yr x
	ON per.person_id = x.person_id
) as one_year
,(
	SELECT COUNT(distinct x.person_id) 
	FROM
	(
		SELECT DISTINCT person_id
		FROM MEASUREMENT
		WHERE measurement_concept_id IN 
		(
			SELECT concept_id
			FROM #tmp_cm22_vital_concepts
			WHERE domain_id = 'Measurement'
		)
		AND measurement_date BETWEEN '01-01-2017' AND '12-31-2021'
		UNION 
		SELECT DISTINCT person_id
		FROM OBSERVATION
		WHERE observation_concept_id IN 
		(
			SELECT concept_id
			FROM #tmp_cm22_vital_concepts
			WHERE domain_id = 'Observation'
		)
		AND observation_date BETWEEN '01-01-2017' AND '12-31-2021'
	) per
	INNER JOIN #tmp_cm22_c5yr x
	ON per.person_id = x.person_id
) as five_year
UNION 
SELECT 
'uniq_pt_smoking' as variable_name
,(
	SELECT COUNT(distinct x.person_id) 
	FROM
	(
		SELECT DISTINCT person_id 
		FROM CONDITION_OCCURRENCE
		WHERE condition_concept_id IN 
		(
			SELECT concept_id
			FROM #tmp_cm22_smoking_concepts
			WHERE domain_id = 'Condition'
		)
		AND condition_start_date BETWEEN '01-01-2021' AND '12-31-2021'
		UNION 
		SELECT DISTINCT person_id 
		FROM OBSERVATION
		WHERE observation_concept_id IN 
		(
			SELECT concept_id
			FROM #tmp_cm22_smoking_concepts
			WHERE domain_id = 'Observation'
		)
		AND observation_date BETWEEN '01-01-2021' AND '12-31-2021'
	) per
	INNER JOIN #tmp_cm22_c1yr x
	ON per.person_id = x.person_id
) as one_year
,(
	SELECT COUNT(distinct x.person_id) 
	FROM
	(
		SELECT DISTINCT person_id 
		FROM CONDITION_OCCURRENCE
		WHERE condition_concept_id IN 
		(
			SELECT concept_id
			FROM #tmp_cm22_smoking_concepts
			WHERE domain_id = 'Condition'
		)
		AND condition_start_date BETWEEN '01-01-2017' AND '12-31-2021'
		UNION 
		SELECT DISTINCT person_id 
		FROM OBSERVATION
		WHERE observation_concept_id IN 
		(
			SELECT concept_id
			FROM #tmp_cm22_smoking_concepts
			WHERE domain_id = 'Observation'
		)
		AND observation_date BETWEEN '01-01-2017' AND '12-31-2021'
	) per
	INNER JOIN #tmp_cm22_c5yr x
	ON per.person_id = x.person_id
) as five_year
UNION 
SELECT 
'uniq_pt_opioid' as variable_name
,(
	SELECT COUNT(DISTINCT x.person_id)
	FROM
	(
		-- " SNOMED -14784000 and its descendants "
		SELECT DISTINCT person_id
		FROM condition_occurrence
		WHERE condition_concept_id IN 
		(
			SELECT descendant_concept_id
			FROM concept_ancestor
			WHERE ancestor_concept_id = 4032799 --Opioid-induced organic mental disorder
		) 
		AND condition_start_date BETWEEN '01-01-2021' AND '12-31-2021'
		UNION 
		--"RxNorm (methadone {6813} and descendants, buprenorphine {1819} and descendant TTYs) "
		SELECT DISTINCT person_id
		FROM drug_exposure
		WHERE drug_concept_id IN 
		(
			SELECT descendant_concept_id
			FROM concept_ancestor
			WHERE ancestor_concept_id IN (
				1103640		-- methadone (6813 is the concept code)
				,1133201	-- buprenorphine (1819 is the concept code)
			)
		) 
		AND drug_exposure_start_date BETWEEN '01-01-2021' AND '12-31-2021'
	) per
	INNER JOIN #tmp_cm22_c1yr x
	ON per.person_id = x.person_id
) as one_year
,(
	SELECT COUNT(DISTINCT x.person_id)
	FROM
	(
		-- " SNOMED -14784000 and its descendants "
		SELECT DISTINCT person_id
		FROM condition_occurrence
		WHERE condition_concept_id IN 
		(
			SELECT descendant_concept_id
			FROM concept_ancestor
			WHERE ancestor_concept_id = 4032799 --Opioid-induced organic mental disorder
		) 
		AND condition_start_date BETWEEN '01-01-2017' AND '12-31-2021'
		UNION 
		--"RxNorm (methadone {6813} and descendants, buprenorphine {1819} and descendant TTYs) "
		SELECT DISTINCT person_id
		FROM drug_exposure
		WHERE drug_concept_id IN 
		(
			SELECT descendant_concept_id
			FROM concept_ancestor
			WHERE ancestor_concept_id IN (
				1103640		-- methadone (6813 is the concept code)
				,1133201	-- buprenorphine (1819 is the concept code)
			)
		) 
		AND drug_exposure_start_date BETWEEN '01-01-2017' AND '12-31-2021'
	) per
	INNER JOIN #tmp_cm22_c5yr x
	ON per.person_id = x.person_id
) as five_year
UNION 
SELECT 
'uniq_pt_any_insurance_value' as variable_name
,(
	SELECT COUNT(DISTINCT x.person_id)
	FROM payer_plan_period per
	INNER JOIN #tmp_cm22_c1yr x
	ON per.person_id = x.person_id
	AND (
		payer_plan_period_start_date BETWEEN '01-01-2021' AND '12-31-2021'
		OR 
		payer_plan_period_end_date BETWEEN '01-01-2021' AND '12-31-2021'
	)
) as one_year
,(
	SELECT COUNT(DISTINCT x.person_id)
	FROM payer_plan_period per
	INNER JOIN #tmp_cm22_c5yr x
	ON per.person_id = x.person_id
	AND (
		payer_plan_period_start_date BETWEEN '01-01-2017' AND '12-31-2021'
		OR 
		payer_plan_period_end_date BETWEEN '01-01-2017' AND '12-31-2021'
	)
) as five_year
-- NULL as NA. Best approach TBD for OMOP 
UNION 
SELECT 
	'uniq_pt_insurance_value_set' as variable_name
	,NULL as one_year
	,NULL as five_year
UNION
SELECT 'CPT4_check' as variable_name
	,(SELECT CAST(CAST(numerator as numeric) / CAST(denominator as numeric)*100 as int) FROM #concept_checks_1yr WHERE src_vocabulary_id = 'CPT4') as one_year
	,(SELECT CAST(CAST(numerator as numeric) / CAST(denominator as numeric)*100 as int) FROM #concept_checks_5yr WHERE src_vocabulary_id = 'CPT4') as five_year
UNION
SELECT 'ICD10CM_check' as variable_name
	,(SELECT CAST(CAST(numerator as numeric) / CAST(denominator as numeric)*100 as int) FROM #concept_checks_1yr WHERE src_vocabulary_id = 'ICD10CM') as one_year
	,(SELECT CAST(CAST(numerator as numeric) / CAST(denominator as numeric)*100 as int) FROM #concept_checks_5yr WHERE src_vocabulary_id = 'ICD10CM') as five_year
UNION
SELECT 'ICD10PCS_check' as variable_name
	,(SELECT CAST(CAST(numerator as numeric) / CAST(denominator as numeric)*100 as int) FROM #concept_checks_1yr WHERE src_vocabulary_id = 'ICD10PCS') as one_year
	,(SELECT CAST(CAST(numerator as numeric) / CAST(denominator as numeric)*100 as int) FROM #concept_checks_5yr WHERE src_vocabulary_id = 'ICD10PCS') as five_year
UNION
SELECT 'ICD9CM_check' as variable_name
	,(SELECT CAST(CAST(numerator as numeric) / CAST(denominator as numeric)*100 as int) FROM #concept_checks_1yr WHERE src_vocabulary_id = 'ICD9CM') as one_year
	,(SELECT CAST(CAST(numerator as numeric) / CAST(denominator as numeric)*100 as int) FROM #concept_checks_5yr WHERE src_vocabulary_id = 'ICD9CM') as five_year
UNION
SELECT 'ICD9Proc_check' as variable_name
	,(SELECT CAST(CAST(numerator as numeric) / CAST(denominator as numeric)*100 as int) FROM #concept_checks_1yr WHERE src_vocabulary_id = 'ICD9Proc') as one_year
	,(SELECT CAST(CAST(numerator as numeric) / CAST(denominator as numeric)*100 as int) FROM #concept_checks_5yr WHERE src_vocabulary_id = 'ICD9Proc') as five_year
UNION
SELECT 'LOINC_check' as variable_name
	,(SELECT CAST(CAST(numerator as numeric) / CAST(denominator as numeric)*100 as int) FROM #concept_checks_1yr WHERE src_vocabulary_id = 'LOINC') as one_year
	,(SELECT CAST(CAST(numerator as numeric) / CAST(denominator as numeric)*100 as int) FROM #concept_checks_5yr WHERE src_vocabulary_id = 'LOINC') as five_year
UNION
SELECT 'RxNorm_check' as variable_name
	,(SELECT CAST(CAST(numerator as numeric) / CAST(denominator as numeric)*100 as int) FROM #concept_checks_1yr WHERE src_vocabulary_id = 'RxNorm') as one_year
	,(SELECT CAST(CAST(numerator as numeric) / CAST(denominator as numeric)*100 as int) FROM #concept_checks_5yr WHERE src_vocabulary_id = 'RxNorm') as five_year
;