For all I know, insurance status is a single bit for each visit: insured or not
insured. Other distinctions I've seen are things like private insurance vs.
medicare or medicaid.
But just to be clear: I added this issue to our trac ticket list, but the
question isn't mine. It came from Dan Hale and others. I'm not quite sure how
to get all the relevant people to have just the right discussion, but I raised
it here because gpc-dev has a public archive and I can always use that to catch
people up later.
#141 has a bit more context:
Dan Hale Wednesday, July 02, 2014 5:11 PM:
So an example of a pediatric study might involve looking at rate of weight
acquisition between the ages of 2 and 5 year. Since this is children one would
really like to know how many children in that age range had 2 or more weights
obtained during well child visits. 3 or more. 4 or more, 5 or more, etc and how
representative they were of the total population (ethnicity, insurance, income,
etc). ...
If one starts with an N of 100,000 with 1 visit, then 80,000 with at least
2, 60,000 with at least 3, 40,000 with at least 4, 30,000 with 5 or more
Furthermore during this progression, does the population drift toward
middle class, insured, white, suburban, etc.
And is it possible to differentiate well child visits (likely weight and
height) versus sick (likely weight only) and also separate those out with
chronic conditions (that are likely to have many more visits naturally and are
therefore likely to be over-represented in the population with many visits)
________________________________________
From: Wanta Keith M [[email protected]]
Sent: Thursday, July 03, 2014 3:38 PM
To: Dan Connolly; Ahuja, Monika; [email protected]
Cc: Wilson Nathan
Subject: RE: [gpc-informatics] #139: insurance status
Hi Dan,
I'm going to touch base with Nathan Wilson (our analyst/ontologist) and he can
discuss with you. I did this for a living for over 5 years, so there are a
half dozen ways to make this work with the Clarity data. Rather than insurance
status, you probably want responsible party (coverage field on charge
transaction in CLARITY_TDL_TRAN) for the encounter. So you would essentially
group the charges together by encounter in CLARITY_TDL_TRAN, join to PAT_ENC on
that encounter number. Look at the coverage field/column for one of the
charges for a given encounter (on the guarantor account). The coverage on
charges of the same encounter (for a given guarantor account--CLARITY_EAR)
generally have the same coverage at the same time. That's probably what would
make the most sense here. Just be sure to use EFFECTIVE_DATE, not SERVICE DATE
in CLARITY_TDL_TRAN. That shows what happened for a given detail type. The
grain of CLARITY_TDL_TRAN is by detail type, not transaction. If you want t
o look just at transactions, see the ARPB_TRANSACTIONS table.
Keith
________________________________________
From: Dan Connolly [[email protected]]
Sent: Thursday, July 03, 2014 1:30 PM
To: Ahuja, Monika; Wanta Keith M; [email protected]
Subject: RE: [gpc-informatics] #139: insurance status
Thanks, everybody. I don't think I completely understand what's going on, but I
found insurance company names and such in there.
The query below gave me counts of some kind of account type name that looks
relevant, though some 90% are null:
"10^" "NAME"
"6.5" ""
"5.6" "Personal/Family"
"5.2" "Interface"
"3.7" "Transplant"
"3.1" "Workers Comp"
"2.8" "Third Party Liability"
"1.3" "Other"
"1" "Clearing Account"
"0" "Corporate"
I don't know how to pick out clinic visits off hand, though.
select round(log(10, count(*)), 1) "10^", name from (
select pe.PAT_ENC_CSN_ID, pe.contact_date, pc.pat_id, pc.line, pc.account_id,
pc.serv_area_id, zat.name, pc.fin_class
, payor.payor_name
from pat_enc pe
left join PAT_ACCT_CVG pc on pc.pat_id = pe.pat_id and pc.ACCOUNT_ID =
pe.ACCOUNT_ID
left join zc_account_type zat on zat.account_type_c = pc.account_type_c
left JOIN COVERAGE c ON c.COVERAGE_ID = pc.COVERAGE_ID
left join clarity_epm payor on payor.payor_id = c.PAYOR_ID
where pe.contact_date > to_date('2013-01-01', 'YYYY-MM-DD')
)
group by name
order by 1 desc
;
--
Dan
_______________________________________________
Gpc-dev mailing list
[email protected]
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
