For now, the query below shows how UTHSCSA is counting patients whose primary
coverage is Medicare. However, our billing departments do not consistently
update the payors and effective dates here, so we need to consult them in the
future to determine the most accurate way to report this.
select count(distinct pat_id) from patient p
join coverage cvg on cvg.coverage_id = p.PRIM_CVG_ID
join (
select distinct payor_id from clarity_epm
where financial_class = 2 or rpt_grp_ten = 1 -- Medicare
) epm on epm.payor_id = cvg.payor_id
;
It might be better to look at the billing transactions table (example below).
But different payors could be changed for different services for the same
patient, so we'd need some algorithm to determine the primary.
select distinct pat.pat_id, tr.PAT_ENC_CSN_ID, tr.SERVICE_DATE, tr.payor_id,
epm.payor_name
from PATIENT pat
join ARPB_TRANSACTIONS tr on tr.PATIENT_ID = pat.pat_id -- billing data
join CLARITY_EPM epm on epm.payor_id = tr.payor_id -- payor info
where financial_class = 2 or rpt_grp_ten = 1 -- Medicare
and tr.TX_TYPE_C in (1) -- charged by blling
) x;
Note that in these examples, values in rpt_grp_ten are local to our institution.
-Angela
From: [email protected]
[mailto:[email protected]] On Behalf Of Phillip Reeder
Sent: Friday, January 09, 2015 7:01 PM
To: Nathan Graham; [email protected]
Subject: Re: Due Monday, January 12th: Population characterization (parts A and
B) for PCORNet LOI
At UTSW, our clarity.pat_enc_2 is null for all of the visit_payor_id column.
What have other sites found in this table/column? Anyone using data from
somewhere else in clarity?
Phillip
From: Nathan Graham <[email protected]<mailto:[email protected]>>
Date: Wednesday, January 7, 2015 at 12:18 PM
To: Phillip Reeder
<[email protected]<mailto:[email protected]>>,
"[email protected]<mailto:[email protected]>"
<[email protected]<mailto:[email protected]>>
Cc: Susan Morrison
<[email protected]<mailto:[email protected]>>
Subject: RE: Due Monday, January 12th: Population characterization (parts A and
B) for PCORNet LOI
Phillip,
I've copied gpc-dev as I think our discussion may benefit others - I hope
that's ok.
Right now, I'm looking at the clarity_epm table - this is what we (KUMC) used
for the original
proposal<http://frontiersresearch.org/frontiers/sites/default/files/frontiers/documents/GPC-PCORI-CDRN-Research-Plan-Template-KUMCv44.pdf>
to estimate insurance coverage in table 1.1. It looks like we might not have
very comprehensive data but we didn't go live with Epic billing until recently
which may explain the sparse coverage - I'm still investigating however.
...
with at_least_one_payor as (
select pat_id, min(fin.title) title -- LOI asking for patient counts, so I
thought I'd take only one if a patient has more...
from clarity.pat_enc_2 enc
join clarity.clarity_epm epm on epm.payor_id = enc.visit_payor_id
join CLARITY.zc_fin_class fin on fin.fin_class_c = epm.financial_class
group by pat_id
),
...
As a side note, a quick search of gpc-trac came up with a comment by Justin
Dale<https://informatics.gpcnetwork.org/trac/Project/ticket/139#comment:5>
indicating he used that table for some exploration into insurance status.
--
Nathan
From: Phillip Reeder [mailto:[email protected]]
Sent: Wednesday, January 07, 2015 10:37 AM
To: Nathan Graham
Cc: Susan Morrison
Subject: Re: Due Monday, January 12th: Population characterization (parts A and
B) for PCORNet LOI
Where are you getting the insurance coverage from? Are you pulling it out of
Clarity? If so, which tables? Just trying to make sure we are consistent.
Thanks,
Phillip
From: Nathan Graham <[email protected]<mailto:[email protected]>>
Date: Wednesday, January 7, 2015 at 8:58 AM
To: "[email protected]<mailto:[email protected]>"
<[email protected]<mailto:[email protected]>>,
"'[email protected]<mailto:'[email protected]>'
([email protected]<mailto:[email protected]>)"
<[email protected]<mailto:[email protected]>>,
"'[email protected]<mailto:'[email protected]>'
([email protected]<mailto:[email protected]>)"
<[email protected]<mailto:[email protected]>>,
"'[email protected]<mailto:'[email protected]>'
([email protected]<mailto:[email protected]>)"
<[email protected]<mailto:[email protected]>>,
"Alex Bokov ([email protected]<mailto:[email protected]>)"
<[email protected]<mailto:[email protected]>>, "Fothergill, Rita, R
<[email protected]<mailto:[email protected]>>
([email protected]<mailto:[email protected]>)"
<[email protected]<mailto:[email protected]>>, "'Jason Lebsack
([email protected]<mailto:[email protected]>)'
([email protected]<mailto:[email protected]>)"
<[email protected]<mailto:[email protected]>>, "Mish Thomas F
([email protected]<mailto:[email protected]>)"
<[email protected]<mailto:[email protected]>>, Phillip Reeder
<[email protected]<mailto:[email protected]>>,
"Justin Dale ([email protected]<mailto:[email protected]>)"
<[email protected]<mailto:[email protected]>>
Cc: Brittany Zschoche <[email protected]<mailto:[email protected]>>, Russ
Waitman <[email protected]<mailto:[email protected]>>, Steve Fennel
<[email protected]<mailto:[email protected]>>
Subject: Due Monday, January 12th: Population characterization (parts A and B)
for PCORNet LOI
extended the requested deadline to Monday January 12th
________________________________
UT Southwestern
Medical Center
The future of medicine, today.
_______________________________________________
Gpc-dev mailing list
[email protected]
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
