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
