claims_db/phclaims/stage/tables/load_stage.mcare_claim_icdcm_header.R
In PHSKC-APDE/Medicaid: Query and analyze WA State Medicaid, Medicare, and APCD data
#### CODE TO LOAD & TABLE-LEVEL QA STAGE.MCARE_claim_icdcm_header
# Eli Kern, PHSKC (APDE)
#
# 2019-12
#
#2024-05-13 Eli update: Data from HCA, ETL in inthealth_edw
### Run from 02_master_mcare_claims_analytic.R script
# https://github.com/PHSKC-APDE/claims_data/blob/main/claims_db/db_loader/mcare/02_master_mcare_claims_analytic.R
#### Load script ####
load_stage.mcare_claim_icdcm_header_f <- function() {
### Run SQL query
odbc::dbGetQuery(inthealth, glue::glue_sql(
"--Code to load data to stage.mcare_claim_icdcm_header table
--ICD-CM codes reshaped to long
--Eli Kern (PHSKC-APDE)
--2024-05
------------------
--STEP 1: Select and union desired columns from multi-year claim tables on stage schema
--Exclude all denied claims using proposed approach per ResDAC 01-2020 consult
--Unpivot and insert into table shell
-------------------
insert into stg_claims.stage_mcare_claim_icdcm_header
select distinct
id_mcare,
claim_header_id,
first_service_date,
last_service_date,
--original diagnosis code
diagnoses as 'icdcm_raw',
--normalized diagnosis code
case
when (diagnoses like '[0-9]%' and len(diagnoses) = 3) then diagnoses + '00'
when (diagnoses like '[0-9]%' and len(diagnoses) = 4) then diagnoses + '0'
when (diagnoses like 'V%' and first_service_date < '2015-10-01' and len(diagnoses) = 3) then diagnoses + '00'
when (diagnoses like 'V%' and first_service_date < '2015-10-01' and len(diagnoses) = 4) then diagnoses + '0'
when (diagnoses like 'E%' and first_service_date < '2015-10-01' and len(diagnoses) = 3) then diagnoses + '00'
when (diagnoses like 'E%' and first_service_date < '2015-10-01' and len(diagnoses) = 4) then diagnoses + '0'
else diagnoses
end as 'icdcm_norm',
--version of diagnosis code
cast(case
when (diagnoses like '[0-9]%') then 9
when (diagnoses like 'V%' and first_service_date < '2015-10-01') then 9
when (diagnoses like 'E%' and first_service_date < '2015-10-01') then 9
else 10
end
as tinyint) as 'icdcm_version',
--diagnosis code number
cast(substring(icdcm_number, 3,10) as varchar(200)) as 'icdcm_number',
filetype_mcare,
getdate() as last_run
from (
--bcarrier
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'carrier' as filetype_mcare,
dxadmit = null,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
dx13 = null,
dx14 = null,
dx15 = null,
dx16 = null,
dx17 = null,
dx18 = null,
dx19 = null,
dx20 = null,
dx21 = null,
dx22 = null,
dx23 = null,
dx24 = null,
dx25 = null,
dxecode_1 = null,
dxecode_2 = null,
dxecode_3 = null,
dxecode_4 = null,
dxecode_5 = null,
dxecode_6 = null,
dxecode_7 = null,
dxecode_8 = null,
dxecode_9 = null,
dxecode_10 = null,
dxecode_11 = null,
dxecode_12 = null
from stg_claims.mcare_bcarrier_claims as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using carrier/dme claim method
where a.carr_clm_pmt_dnl_cd in ('1','2','3','4','5','6','7','8','9')
--exclude claims among people who have no eligibility data
and b.bene_id is not null
--dme
union
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'dme' as filetype_mcare,
dxadmit = null,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
dx13 = null,
dx14 = null,
dx15 = null,
dx16 = null,
dx17 = null,
dx18 = null,
dx19 = null,
dx20 = null,
dx21 = null,
dx22 = null,
dx23 = null,
dx24 = null,
dx25 = null,
dxecode_1 = null,
dxecode_2 = null,
dxecode_3 = null,
dxecode_4 = null,
dxecode_5 = null,
dxecode_6 = null,
dxecode_7 = null,
dxecode_8 = null,
dxecode_9 = null,
dxecode_10 = null,
dxecode_11 = null,
dxecode_12 = null
from stg_claims.mcare_dme_claims as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using carrier/dme claim method
where a.carr_clm_pmt_dnl_cd in ('1','2','3','4','5','6','7','8','9')
--exclude claims among people who have no eligibility data
and b.bene_id is not null
--hha
union
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'hha' as filetype_mcare,
dxadmit = null,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
icd_dgns_cd13 as dx13,
icd_dgns_cd14 as dx14,
icd_dgns_cd15 as dx15,
icd_dgns_cd16 as dx16,
icd_dgns_cd17 as dx17,
icd_dgns_cd18 as dx18,
icd_dgns_cd19 as dx19,
icd_dgns_cd20 as dx20,
icd_dgns_cd21 as dx21,
icd_dgns_cd22 as dx22,
icd_dgns_cd23 as dx23,
icd_dgns_cd24 as dx24,
icd_dgns_cd25 as dx25,
icd_dgns_e_cd1 as dxecode_1,
icd_dgns_e_cd2 as dxecode_2,
icd_dgns_e_cd3 as dxecode_3,
icd_dgns_e_cd4 as dxecode_4,
icd_dgns_e_cd5 as dxecode_5,
icd_dgns_e_cd6 as dxecode_6,
icd_dgns_e_cd7 as dxecode_7,
icd_dgns_e_cd8 as dxecode_8,
icd_dgns_e_cd9 as dxecode_9,
icd_dgns_e_cd10 as dxecode_10,
icd_dgns_e_cd11 as dxecode_11,
icd_dgns_e_cd12 as dxecode_12
from stg_claims.mcare_hha_base_claims as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using facility claim method
where (a.clm_mdcr_non_pmt_rsn_cd = '' or a.clm_mdcr_non_pmt_rsn_cd is null)
--exclude claims among people who have no eligibility data
and b.bene_id is not null
--hospice
union
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'hospice' as filetype_mcare,
dxadmit = null,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
icd_dgns_cd13 as dx13,
icd_dgns_cd14 as dx14,
icd_dgns_cd15 as dx15,
icd_dgns_cd16 as dx16,
icd_dgns_cd17 as dx17,
icd_dgns_cd18 as dx18,
icd_dgns_cd19 as dx19,
icd_dgns_cd20 as dx20,
icd_dgns_cd21 as dx21,
icd_dgns_cd22 as dx22,
icd_dgns_cd23 as dx23,
icd_dgns_cd24 as dx24,
icd_dgns_cd25 as dx25,
icd_dgns_e_cd1 as dxecode_1,
icd_dgns_e_cd2 as dxecode_2,
icd_dgns_e_cd3 as dxecode_3,
icd_dgns_e_cd4 as dxecode_4,
icd_dgns_e_cd5 as dxecode_5,
icd_dgns_e_cd6 as dxecode_6,
icd_dgns_e_cd7 as dxecode_7,
icd_dgns_e_cd8 as dxecode_8,
icd_dgns_e_cd9 as dxecode_9,
icd_dgns_e_cd10 as dxecode_10,
icd_dgns_e_cd11 as dxecode_11,
icd_dgns_e_cd12 as dxecode_12
from stg_claims.mcare_hospice_base_claims as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using facility claim method
where (a.clm_mdcr_non_pmt_rsn_cd = '' or a.clm_mdcr_non_pmt_rsn_cd is null)
--exclude claims among people who have no eligibility data
and b.bene_id is not null
--inpatient
union
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'inpatient' as filetype_mcare,
admtg_dgns_cd as dxadmit,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
icd_dgns_cd13 as dx13,
icd_dgns_cd14 as dx14,
icd_dgns_cd15 as dx15,
icd_dgns_cd16 as dx16,
icd_dgns_cd17 as dx17,
icd_dgns_cd18 as dx18,
icd_dgns_cd19 as dx19,
icd_dgns_cd20 as dx20,
icd_dgns_cd21 as dx21,
icd_dgns_cd22 as dx22,
icd_dgns_cd23 as dx23,
icd_dgns_cd24 as dx24,
icd_dgns_cd25 as dx25,
icd_dgns_e_cd1 as dxecode_1,
icd_dgns_e_cd2 as dxecode_2,
icd_dgns_e_cd3 as dxecode_3,
icd_dgns_e_cd4 as dxecode_4,
icd_dgns_e_cd5 as dxecode_5,
icd_dgns_e_cd6 as dxecode_6,
icd_dgns_e_cd7 as dxecode_7,
icd_dgns_e_cd8 as dxecode_8,
icd_dgns_e_cd9 as dxecode_9,
icd_dgns_e_cd10 as dxecode_10,
icd_dgns_e_cd11 as dxecode_11,
icd_dgns_e_cd12 as dxecode_12
from stg_claims.mcare_inpatient_base_claims as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using facility claim method
where (a.clm_mdcr_non_pmt_rsn_cd = '' or a.clm_mdcr_non_pmt_rsn_cd is null)
--exclude claims among people who have no eligibility data
and b.bene_id is not null
--inpatient data structure j
union
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'inpatient' as filetype_mcare,
admtg_dgns_cd as dxadmit,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
icd_dgns_cd13 as dx13,
icd_dgns_cd14 as dx14,
icd_dgns_cd15 as dx15,
icd_dgns_cd16 as dx16,
icd_dgns_cd17 as dx17,
icd_dgns_cd18 as dx18,
icd_dgns_cd19 as dx19,
icd_dgns_cd20 as dx20,
icd_dgns_cd21 as dx21,
icd_dgns_cd22 as dx22,
icd_dgns_cd23 as dx23,
icd_dgns_cd24 as dx24,
icd_dgns_cd25 as dx25,
icd_dgns_e_cd1 as dxecode_1,
icd_dgns_e_cd2 as dxecode_2,
icd_dgns_e_cd3 as dxecode_3,
icd_dgns_e_cd4 as dxecode_4,
icd_dgns_e_cd5 as dxecode_5,
icd_dgns_e_cd6 as dxecode_6,
icd_dgns_e_cd7 as dxecode_7,
icd_dgns_e_cd8 as dxecode_8,
icd_dgns_e_cd9 as dxecode_9,
icd_dgns_e_cd10 as dxecode_10,
icd_dgns_e_cd11 as dxecode_11,
icd_dgns_e_cd12 as dxecode_12
from stg_claims.mcare_inpatient_base_claims_j as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using facility claim method
where (a.clm_mdcr_non_pmt_rsn_cd = '' or a.clm_mdcr_non_pmt_rsn_cd is null)
--exclude claims among people who have no eligibility data
and b.bene_id is not null
--outpatient
union
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'outpatient' as filetype_mcare,
dxadmit = null,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
icd_dgns_cd13 as dx13,
icd_dgns_cd14 as dx14,
icd_dgns_cd15 as dx15,
icd_dgns_cd16 as dx16,
icd_dgns_cd17 as dx17,
icd_dgns_cd18 as dx18,
icd_dgns_cd19 as dx19,
icd_dgns_cd20 as dx20,
icd_dgns_cd21 as dx21,
icd_dgns_cd22 as dx22,
icd_dgns_cd23 as dx23,
icd_dgns_cd24 as dx24,
icd_dgns_cd25 as dx25,
icd_dgns_e_cd1 as dxecode_1,
icd_dgns_e_cd2 as dxecode_2,
icd_dgns_e_cd3 as dxecode_3,
icd_dgns_e_cd4 as dxecode_4,
icd_dgns_e_cd5 as dxecode_5,
icd_dgns_e_cd6 as dxecode_6,
icd_dgns_e_cd7 as dxecode_7,
icd_dgns_e_cd8 as dxecode_8,
icd_dgns_e_cd9 as dxecode_9,
icd_dgns_e_cd10 as dxecode_10,
icd_dgns_e_cd11 as dxecode_11,
icd_dgns_e_cd12 as dxecode_12
from stg_claims.mcare_outpatient_base_claims as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using facility claim method
where (a.clm_mdcr_non_pmt_rsn_cd = '' or a.clm_mdcr_non_pmt_rsn_cd is null)
--exclude claims among people who have no eligibility data
and b.bene_id is not null
--outpatient data structure j
union
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'outpatient' as filetype_mcare,
dxadmit = null,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
icd_dgns_cd13 as dx13,
icd_dgns_cd14 as dx14,
icd_dgns_cd15 as dx15,
icd_dgns_cd16 as dx16,
icd_dgns_cd17 as dx17,
icd_dgns_cd18 as dx18,
icd_dgns_cd19 as dx19,
icd_dgns_cd20 as dx20,
icd_dgns_cd21 as dx21,
icd_dgns_cd22 as dx22,
icd_dgns_cd23 as dx23,
icd_dgns_cd24 as dx24,
icd_dgns_cd25 as dx25,
icd_dgns_e_cd1 as dxecode_1,
icd_dgns_e_cd2 as dxecode_2,
icd_dgns_e_cd3 as dxecode_3,
icd_dgns_e_cd4 as dxecode_4,
icd_dgns_e_cd5 as dxecode_5,
icd_dgns_e_cd6 as dxecode_6,
icd_dgns_e_cd7 as dxecode_7,
icd_dgns_e_cd8 as dxecode_8,
icd_dgns_e_cd9 as dxecode_9,
icd_dgns_e_cd10 as dxecode_10,
icd_dgns_e_cd11 as dxecode_11,
icd_dgns_e_cd12 as dxecode_12
from stg_claims.mcare_outpatient_base_claims_j as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using facility claim method
where (a.clm_mdcr_non_pmt_rsn_cd = '' or a.clm_mdcr_non_pmt_rsn_cd is null)
--exclude claims among people who have no eligibility data
and b.bene_id is not null
--snf
union
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'snf' as filetype_mcare,
admtg_dgns_cd as dxadmit,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
icd_dgns_cd13 as dx13,
icd_dgns_cd14 as dx14,
icd_dgns_cd15 as dx15,
icd_dgns_cd16 as dx16,
icd_dgns_cd17 as dx17,
icd_dgns_cd18 as dx18,
icd_dgns_cd19 as dx19,
icd_dgns_cd20 as dx20,
icd_dgns_cd21 as dx21,
icd_dgns_cd22 as dx22,
icd_dgns_cd23 as dx23,
icd_dgns_cd24 as dx24,
icd_dgns_cd25 as dx25,
icd_dgns_e_cd1 as dxecode_1,
icd_dgns_e_cd2 as dxecode_2,
icd_dgns_e_cd3 as dxecode_3,
icd_dgns_e_cd4 as dxecode_4,
icd_dgns_e_cd5 as dxecode_5,
icd_dgns_e_cd6 as dxecode_6,
icd_dgns_e_cd7 as dxecode_7,
icd_dgns_e_cd8 as dxecode_8,
icd_dgns_e_cd9 as dxecode_9,
icd_dgns_e_cd10 as dxecode_10,
icd_dgns_e_cd11 as dxecode_11,
icd_dgns_e_cd12 as dxecode_12
from stg_claims.mcare_snf_base_claims as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using facility claim method
where (a.clm_mdcr_non_pmt_rsn_cd = '' or a.clm_mdcr_non_pmt_rsn_cd is null)
--exclude claims among people who have no eligibility data
and b.bene_id is not null
) as a
--reshape from wide to long
unpivot(diagnoses for icdcm_number in (
dxadmit,
dx01,
dx02,
dx03,
dx04,
dx05,
dx06,
dx07,
dx08,
dx09,
dx10,
dx11,
dx12,
dx13,
dx14,
dx15,
dx16,
dx17,
dx18,
dx19,
dx20,
dx21,
dx22,
dx23,
dx24,
dx25,
dxecode_1,
dxecode_2,
dxecode_3,
dxecode_4,
dxecode_5,
dxecode_6,
dxecode_7,
dxecode_8,
dxecode_9,
dxecode_10,
dxecode_11,
dxecode_12)
) as diagnoses
where diagnoses is not null AND diagnoses!=' ';",
.con = inthealth))
}
#### Table-level QA script ####
qa_stage.mcare_claim_icdcm_header_qa_f <- function() {
#confirm that claim types with dx01 have data for each year
res1 <- dbGetQuery(conn = inthealth, glue_sql(
"select 'stg_claims.stage_mcare_claim_icdcm_header' as 'table',
'rows with non-null dx01' as qa_type,
filetype_mcare, year(last_service_date) as service_year, count(*) as qa
from stg_claims.stage_mcare_claim_icdcm_header
where icdcm_norm is not null and icdcm_number = '01'
group by filetype_mcare, year(last_service_date)
order by filetype_mcare, year(last_service_date);",
.con = inthealth))
#confirm that claim types with admit dx have data for each year
res2 <- dbGetQuery(conn = inthealth, glue_sql(
"select 'stg_claims.stage_mcare_claim_icdcm_header' as 'table',
'rows with non-null dx_admit' as qa_type,
filetype_mcare, year(last_service_date) as service_year, count(*) as qa
from stg_claims.stage_mcare_claim_icdcm_header
where icdcm_norm is not null and icdcm_number = 'admit'
group by filetype_mcare, year(last_service_date)
order by filetype_mcare, year(last_service_date);",
.con = inthealth))
#confirm that claim types with ecode dx01 have data for each year
res3 <- dbGetQuery(conn = inthealth, glue_sql(
"select 'stg_claims.stage_mcare_claim_icdcm_header' as 'table',
'rows with non-null dx ecode 1' as qa_type,
filetype_mcare, year(last_service_date) as service_year, count(*) as qa
from stg_claims.stage_mcare_claim_icdcm_header
where icdcm_norm is not null and icdcm_number = 'ecode_1'
group by filetype_mcare, year(last_service_date)
order by filetype_mcare, year(last_service_date);",
.con = inthealth))
#make sure everyone is in bene_enrollment table
res4 <- dbGetQuery(conn = inthealth, glue_sql(
"select 'stg_claims.stage_mcare_claim_icdcm_header' as 'table', '# members not in bene_enrollment, expect 0' as qa_type,
count(a.id_mcare) as qa
from stg_claims.stage_mcare_claim_icdcm_header as a
left join stg_claims.mcare_bene_enrollment as b
on a.id_mcare = b.bene_id
where b.bene_id is null;",
.con = inthealth))
res_final <- mget(ls(pattern="^res")) %>% bind_rows()
}
PHSKC-APDE/Medicaid documentation built on Sept. 17, 2024, 2:47 a.m.
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
#### CODE TO LOAD & TABLE-LEVEL QA STAGE.MCARE_claim_icdcm_header
# Eli Kern, PHSKC (APDE)
#
# 2019-12
#
#2024-05-13 Eli update: Data from HCA, ETL in inthealth_edw
### Run from 02_master_mcare_claims_analytic.R script
# https://github.com/PHSKC-APDE/claims_data/blob/main/claims_db/db_loader/mcare/02_master_mcare_claims_analytic.R
#### Load script ####
load_stage.mcare_claim_icdcm_header_f <- function() {
### Run SQL query
odbc::dbGetQuery(inthealth, glue::glue_sql(
"--Code to load data to stage.mcare_claim_icdcm_header table
--ICD-CM codes reshaped to long
--Eli Kern (PHSKC-APDE)
--2024-05
------------------
--STEP 1: Select and union desired columns from multi-year claim tables on stage schema
--Exclude all denied claims using proposed approach per ResDAC 01-2020 consult
--Unpivot and insert into table shell
-------------------
insert into stg_claims.stage_mcare_claim_icdcm_header
select distinct
id_mcare,
claim_header_id,
first_service_date,
last_service_date,
--original diagnosis code
diagnoses as 'icdcm_raw',
--normalized diagnosis code
case
when (diagnoses like '[0-9]%' and len(diagnoses) = 3) then diagnoses + '00'
when (diagnoses like '[0-9]%' and len(diagnoses) = 4) then diagnoses + '0'
when (diagnoses like 'V%' and first_service_date < '2015-10-01' and len(diagnoses) = 3) then diagnoses + '00'
when (diagnoses like 'V%' and first_service_date < '2015-10-01' and len(diagnoses) = 4) then diagnoses + '0'
when (diagnoses like 'E%' and first_service_date < '2015-10-01' and len(diagnoses) = 3) then diagnoses + '00'
when (diagnoses like 'E%' and first_service_date < '2015-10-01' and len(diagnoses) = 4) then diagnoses + '0'
else diagnoses
end as 'icdcm_norm',
--version of diagnosis code
cast(case
when (diagnoses like '[0-9]%') then 9
when (diagnoses like 'V%' and first_service_date < '2015-10-01') then 9
when (diagnoses like 'E%' and first_service_date < '2015-10-01') then 9
else 10
end
as tinyint) as 'icdcm_version',
--diagnosis code number
cast(substring(icdcm_number, 3,10) as varchar(200)) as 'icdcm_number',
filetype_mcare,
getdate() as last_run
from (
--bcarrier
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'carrier' as filetype_mcare,
dxadmit = null,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
dx13 = null,
dx14 = null,
dx15 = null,
dx16 = null,
dx17 = null,
dx18 = null,
dx19 = null,
dx20 = null,
dx21 = null,
dx22 = null,
dx23 = null,
dx24 = null,
dx25 = null,
dxecode_1 = null,
dxecode_2 = null,
dxecode_3 = null,
dxecode_4 = null,
dxecode_5 = null,
dxecode_6 = null,
dxecode_7 = null,
dxecode_8 = null,
dxecode_9 = null,
dxecode_10 = null,
dxecode_11 = null,
dxecode_12 = null
from stg_claims.mcare_bcarrier_claims as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using carrier/dme claim method
where a.carr_clm_pmt_dnl_cd in ('1','2','3','4','5','6','7','8','9')
--exclude claims among people who have no eligibility data
and b.bene_id is not null
--dme
union
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'dme' as filetype_mcare,
dxadmit = null,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
dx13 = null,
dx14 = null,
dx15 = null,
dx16 = null,
dx17 = null,
dx18 = null,
dx19 = null,
dx20 = null,
dx21 = null,
dx22 = null,
dx23 = null,
dx24 = null,
dx25 = null,
dxecode_1 = null,
dxecode_2 = null,
dxecode_3 = null,
dxecode_4 = null,
dxecode_5 = null,
dxecode_6 = null,
dxecode_7 = null,
dxecode_8 = null,
dxecode_9 = null,
dxecode_10 = null,
dxecode_11 = null,
dxecode_12 = null
from stg_claims.mcare_dme_claims as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using carrier/dme claim method
where a.carr_clm_pmt_dnl_cd in ('1','2','3','4','5','6','7','8','9')
--exclude claims among people who have no eligibility data
and b.bene_id is not null
--hha
union
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'hha' as filetype_mcare,
dxadmit = null,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
icd_dgns_cd13 as dx13,
icd_dgns_cd14 as dx14,
icd_dgns_cd15 as dx15,
icd_dgns_cd16 as dx16,
icd_dgns_cd17 as dx17,
icd_dgns_cd18 as dx18,
icd_dgns_cd19 as dx19,
icd_dgns_cd20 as dx20,
icd_dgns_cd21 as dx21,
icd_dgns_cd22 as dx22,
icd_dgns_cd23 as dx23,
icd_dgns_cd24 as dx24,
icd_dgns_cd25 as dx25,
icd_dgns_e_cd1 as dxecode_1,
icd_dgns_e_cd2 as dxecode_2,
icd_dgns_e_cd3 as dxecode_3,
icd_dgns_e_cd4 as dxecode_4,
icd_dgns_e_cd5 as dxecode_5,
icd_dgns_e_cd6 as dxecode_6,
icd_dgns_e_cd7 as dxecode_7,
icd_dgns_e_cd8 as dxecode_8,
icd_dgns_e_cd9 as dxecode_9,
icd_dgns_e_cd10 as dxecode_10,
icd_dgns_e_cd11 as dxecode_11,
icd_dgns_e_cd12 as dxecode_12
from stg_claims.mcare_hha_base_claims as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using facility claim method
where (a.clm_mdcr_non_pmt_rsn_cd = '' or a.clm_mdcr_non_pmt_rsn_cd is null)
--exclude claims among people who have no eligibility data
and b.bene_id is not null
--hospice
union
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'hospice' as filetype_mcare,
dxadmit = null,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
icd_dgns_cd13 as dx13,
icd_dgns_cd14 as dx14,
icd_dgns_cd15 as dx15,
icd_dgns_cd16 as dx16,
icd_dgns_cd17 as dx17,
icd_dgns_cd18 as dx18,
icd_dgns_cd19 as dx19,
icd_dgns_cd20 as dx20,
icd_dgns_cd21 as dx21,
icd_dgns_cd22 as dx22,
icd_dgns_cd23 as dx23,
icd_dgns_cd24 as dx24,
icd_dgns_cd25 as dx25,
icd_dgns_e_cd1 as dxecode_1,
icd_dgns_e_cd2 as dxecode_2,
icd_dgns_e_cd3 as dxecode_3,
icd_dgns_e_cd4 as dxecode_4,
icd_dgns_e_cd5 as dxecode_5,
icd_dgns_e_cd6 as dxecode_6,
icd_dgns_e_cd7 as dxecode_7,
icd_dgns_e_cd8 as dxecode_8,
icd_dgns_e_cd9 as dxecode_9,
icd_dgns_e_cd10 as dxecode_10,
icd_dgns_e_cd11 as dxecode_11,
icd_dgns_e_cd12 as dxecode_12
from stg_claims.mcare_hospice_base_claims as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using facility claim method
where (a.clm_mdcr_non_pmt_rsn_cd = '' or a.clm_mdcr_non_pmt_rsn_cd is null)
--exclude claims among people who have no eligibility data
and b.bene_id is not null
--inpatient
union
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'inpatient' as filetype_mcare,
admtg_dgns_cd as dxadmit,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
icd_dgns_cd13 as dx13,
icd_dgns_cd14 as dx14,
icd_dgns_cd15 as dx15,
icd_dgns_cd16 as dx16,
icd_dgns_cd17 as dx17,
icd_dgns_cd18 as dx18,
icd_dgns_cd19 as dx19,
icd_dgns_cd20 as dx20,
icd_dgns_cd21 as dx21,
icd_dgns_cd22 as dx22,
icd_dgns_cd23 as dx23,
icd_dgns_cd24 as dx24,
icd_dgns_cd25 as dx25,
icd_dgns_e_cd1 as dxecode_1,
icd_dgns_e_cd2 as dxecode_2,
icd_dgns_e_cd3 as dxecode_3,
icd_dgns_e_cd4 as dxecode_4,
icd_dgns_e_cd5 as dxecode_5,
icd_dgns_e_cd6 as dxecode_6,
icd_dgns_e_cd7 as dxecode_7,
icd_dgns_e_cd8 as dxecode_8,
icd_dgns_e_cd9 as dxecode_9,
icd_dgns_e_cd10 as dxecode_10,
icd_dgns_e_cd11 as dxecode_11,
icd_dgns_e_cd12 as dxecode_12
from stg_claims.mcare_inpatient_base_claims as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using facility claim method
where (a.clm_mdcr_non_pmt_rsn_cd = '' or a.clm_mdcr_non_pmt_rsn_cd is null)
--exclude claims among people who have no eligibility data
and b.bene_id is not null
--inpatient data structure j
union
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'inpatient' as filetype_mcare,
admtg_dgns_cd as dxadmit,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
icd_dgns_cd13 as dx13,
icd_dgns_cd14 as dx14,
icd_dgns_cd15 as dx15,
icd_dgns_cd16 as dx16,
icd_dgns_cd17 as dx17,
icd_dgns_cd18 as dx18,
icd_dgns_cd19 as dx19,
icd_dgns_cd20 as dx20,
icd_dgns_cd21 as dx21,
icd_dgns_cd22 as dx22,
icd_dgns_cd23 as dx23,
icd_dgns_cd24 as dx24,
icd_dgns_cd25 as dx25,
icd_dgns_e_cd1 as dxecode_1,
icd_dgns_e_cd2 as dxecode_2,
icd_dgns_e_cd3 as dxecode_3,
icd_dgns_e_cd4 as dxecode_4,
icd_dgns_e_cd5 as dxecode_5,
icd_dgns_e_cd6 as dxecode_6,
icd_dgns_e_cd7 as dxecode_7,
icd_dgns_e_cd8 as dxecode_8,
icd_dgns_e_cd9 as dxecode_9,
icd_dgns_e_cd10 as dxecode_10,
icd_dgns_e_cd11 as dxecode_11,
icd_dgns_e_cd12 as dxecode_12
from stg_claims.mcare_inpatient_base_claims_j as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using facility claim method
where (a.clm_mdcr_non_pmt_rsn_cd = '' or a.clm_mdcr_non_pmt_rsn_cd is null)
--exclude claims among people who have no eligibility data
and b.bene_id is not null
--outpatient
union
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'outpatient' as filetype_mcare,
dxadmit = null,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
icd_dgns_cd13 as dx13,
icd_dgns_cd14 as dx14,
icd_dgns_cd15 as dx15,
icd_dgns_cd16 as dx16,
icd_dgns_cd17 as dx17,
icd_dgns_cd18 as dx18,
icd_dgns_cd19 as dx19,
icd_dgns_cd20 as dx20,
icd_dgns_cd21 as dx21,
icd_dgns_cd22 as dx22,
icd_dgns_cd23 as dx23,
icd_dgns_cd24 as dx24,
icd_dgns_cd25 as dx25,
icd_dgns_e_cd1 as dxecode_1,
icd_dgns_e_cd2 as dxecode_2,
icd_dgns_e_cd3 as dxecode_3,
icd_dgns_e_cd4 as dxecode_4,
icd_dgns_e_cd5 as dxecode_5,
icd_dgns_e_cd6 as dxecode_6,
icd_dgns_e_cd7 as dxecode_7,
icd_dgns_e_cd8 as dxecode_8,
icd_dgns_e_cd9 as dxecode_9,
icd_dgns_e_cd10 as dxecode_10,
icd_dgns_e_cd11 as dxecode_11,
icd_dgns_e_cd12 as dxecode_12
from stg_claims.mcare_outpatient_base_claims as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using facility claim method
where (a.clm_mdcr_non_pmt_rsn_cd = '' or a.clm_mdcr_non_pmt_rsn_cd is null)
--exclude claims among people who have no eligibility data
and b.bene_id is not null
--outpatient data structure j
union
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'outpatient' as filetype_mcare,
dxadmit = null,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
icd_dgns_cd13 as dx13,
icd_dgns_cd14 as dx14,
icd_dgns_cd15 as dx15,
icd_dgns_cd16 as dx16,
icd_dgns_cd17 as dx17,
icd_dgns_cd18 as dx18,
icd_dgns_cd19 as dx19,
icd_dgns_cd20 as dx20,
icd_dgns_cd21 as dx21,
icd_dgns_cd22 as dx22,
icd_dgns_cd23 as dx23,
icd_dgns_cd24 as dx24,
icd_dgns_cd25 as dx25,
icd_dgns_e_cd1 as dxecode_1,
icd_dgns_e_cd2 as dxecode_2,
icd_dgns_e_cd3 as dxecode_3,
icd_dgns_e_cd4 as dxecode_4,
icd_dgns_e_cd5 as dxecode_5,
icd_dgns_e_cd6 as dxecode_6,
icd_dgns_e_cd7 as dxecode_7,
icd_dgns_e_cd8 as dxecode_8,
icd_dgns_e_cd9 as dxecode_9,
icd_dgns_e_cd10 as dxecode_10,
icd_dgns_e_cd11 as dxecode_11,
icd_dgns_e_cd12 as dxecode_12
from stg_claims.mcare_outpatient_base_claims_j as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using facility claim method
where (a.clm_mdcr_non_pmt_rsn_cd = '' or a.clm_mdcr_non_pmt_rsn_cd is null)
--exclude claims among people who have no eligibility data
and b.bene_id is not null
--snf
union
select
--top 100
trim(a.bene_id) as id_mcare,
trim(a.clm_id) as claim_header_id,
cast(a.clm_from_dt as date) as first_service_date,
cast(a.clm_thru_dt as date) as last_service_date,
'snf' as filetype_mcare,
admtg_dgns_cd as dxadmit,
icd_dgns_cd1 as dx01,
icd_dgns_cd2 as dx02,
icd_dgns_cd3 as dx03,
icd_dgns_cd4 as dx04,
icd_dgns_cd5 as dx05,
icd_dgns_cd6 as dx06,
icd_dgns_cd7 as dx07,
icd_dgns_cd8 as dx08,
icd_dgns_cd9 as dx09,
icd_dgns_cd10 as dx10,
icd_dgns_cd11 as dx11,
icd_dgns_cd12 as dx12,
icd_dgns_cd13 as dx13,
icd_dgns_cd14 as dx14,
icd_dgns_cd15 as dx15,
icd_dgns_cd16 as dx16,
icd_dgns_cd17 as dx17,
icd_dgns_cd18 as dx18,
icd_dgns_cd19 as dx19,
icd_dgns_cd20 as dx20,
icd_dgns_cd21 as dx21,
icd_dgns_cd22 as dx22,
icd_dgns_cd23 as dx23,
icd_dgns_cd24 as dx24,
icd_dgns_cd25 as dx25,
icd_dgns_e_cd1 as dxecode_1,
icd_dgns_e_cd2 as dxecode_2,
icd_dgns_e_cd3 as dxecode_3,
icd_dgns_e_cd4 as dxecode_4,
icd_dgns_e_cd5 as dxecode_5,
icd_dgns_e_cd6 as dxecode_6,
icd_dgns_e_cd7 as dxecode_7,
icd_dgns_e_cd8 as dxecode_8,
icd_dgns_e_cd9 as dxecode_9,
icd_dgns_e_cd10 as dxecode_10,
icd_dgns_e_cd11 as dxecode_11,
icd_dgns_e_cd12 as dxecode_12
from stg_claims.mcare_snf_base_claims as a
left join stg_claims.mcare_bene_enrollment as b
on a.bene_id = b.bene_id
--exclude denined claims using facility claim method
where (a.clm_mdcr_non_pmt_rsn_cd = '' or a.clm_mdcr_non_pmt_rsn_cd is null)
--exclude claims among people who have no eligibility data
and b.bene_id is not null
) as a
--reshape from wide to long
unpivot(diagnoses for icdcm_number in (
dxadmit,
dx01,
dx02,
dx03,
dx04,
dx05,
dx06,
dx07,
dx08,
dx09,
dx10,
dx11,
dx12,
dx13,
dx14,
dx15,
dx16,
dx17,
dx18,
dx19,
dx20,
dx21,
dx22,
dx23,
dx24,
dx25,
dxecode_1,
dxecode_2,
dxecode_3,
dxecode_4,
dxecode_5,
dxecode_6,
dxecode_7,
dxecode_8,
dxecode_9,
dxecode_10,
dxecode_11,
dxecode_12)
) as diagnoses
where diagnoses is not null AND diagnoses!=' ';",
.con = inthealth))
}
#### Table-level QA script ####
qa_stage.mcare_claim_icdcm_header_qa_f <- function() {
#confirm that claim types with dx01 have data for each year
res1 <- dbGetQuery(conn = inthealth, glue_sql(
"select 'stg_claims.stage_mcare_claim_icdcm_header' as 'table',
'rows with non-null dx01' as qa_type,
filetype_mcare, year(last_service_date) as service_year, count(*) as qa
from stg_claims.stage_mcare_claim_icdcm_header
where icdcm_norm is not null and icdcm_number = '01'
group by filetype_mcare, year(last_service_date)
order by filetype_mcare, year(last_service_date);",
.con = inthealth))
#confirm that claim types with admit dx have data for each year
res2 <- dbGetQuery(conn = inthealth, glue_sql(
"select 'stg_claims.stage_mcare_claim_icdcm_header' as 'table',
'rows with non-null dx_admit' as qa_type,
filetype_mcare, year(last_service_date) as service_year, count(*) as qa
from stg_claims.stage_mcare_claim_icdcm_header
where icdcm_norm is not null and icdcm_number = 'admit'
group by filetype_mcare, year(last_service_date)
order by filetype_mcare, year(last_service_date);",
.con = inthealth))
#confirm that claim types with ecode dx01 have data for each year
res3 <- dbGetQuery(conn = inthealth, glue_sql(
"select 'stg_claims.stage_mcare_claim_icdcm_header' as 'table',
'rows with non-null dx ecode 1' as qa_type,
filetype_mcare, year(last_service_date) as service_year, count(*) as qa
from stg_claims.stage_mcare_claim_icdcm_header
where icdcm_norm is not null and icdcm_number = 'ecode_1'
group by filetype_mcare, year(last_service_date)
order by filetype_mcare, year(last_service_date);",
.con = inthealth))
#make sure everyone is in bene_enrollment table
res4 <- dbGetQuery(conn = inthealth, glue_sql(
"select 'stg_claims.stage_mcare_claim_icdcm_header' as 'table', '# members not in bene_enrollment, expect 0' as qa_type,
count(a.id_mcare) as qa
from stg_claims.stage_mcare_claim_icdcm_header as a
left join stg_claims.mcare_bene_enrollment as b
on a.id_mcare = b.bene_id
where b.bene_id is null;",
.con = inthealth))
res_final <- mget(ls(pattern="^res")) %>% bind_rows()
}
R Package Documentation
Browse R Packages
We want your feedback!
Note that we can't provide technical support on individual packages. You should contact the package authors for that.
Embedding an R snippet on your website
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.