Financial rpt - too many records and want field repeated

[azoe]

Here's the query I have:

Select  c.charge_id, 
	td.trans_id, 
 	pe.enc_Timestamp as EncDate,  
	pe.enc_nbr,
	c.amt,
	(
		SELECT	[payer_name]
		FROM	[payer_mstr]
		WHERE	[payer_ID] = [ep].[payer_id] and cob = 1  --cob = 1 means they are the primary payer  if it was cob=2 then that is a secondary payer, etc.
	) AS [EncChgPayer],  --secondary payers and below show Null  FC - how to make it show primary payer instead of Null ?
	
	(
		SELECT	[mstr_list_item_desc]
		FROM	[mstr_lists]
		WHERE	[mstr_list_item_id] = [pm].[financial_class] and ([pm].[payer_ID] = [ep].[payer_id] and cob = 1) 
	) As [EncChgFC],
	
	td.paid_amt,
	td.adj_amt,
	pm.payer_name as PmtPayer, 
	ml.mstr_list_item_desc as PmtPayerFC

FROM    person per   
	left outer join patient_encounter pe on (pe.person_id = per.person_id)
        left outer join encounter_payer ep on (pe.enc_id = ep.enc_id and pe.person_id = ep.person_id)
        left outer join charges c on (c.person_id = pe.person_id and c.source_id = pe.enc_id)
	left outer join trans_detail td on (td.charge_id = c.charge_id and td.source_id = c.source_id)
	left outer join transactions t on (td.trans_id = t.trans_id) --and t.payer_id = ep.payer_id) can’t make these last two equal because there is no entry in the ep table when patient has no insurance (self pay)
	left outer join payer_mstr pm on (pm.payer_id = t.payer_id) --get payment payer name
	left outer join mstr_lists ml on (ml.mstr_list_item_id = pm.financial_class)

WHERE   c.create_Timestamp between '20050912' and '20050913'

What I’m trying to do is create a “changes in accounts receivable” report. So I need to get all the charges on patient encounters created between 2 dates (and the associated payments and adjustments). I also need to show financial class.
The financial class (FC) is associated with a payer (an insurance company or self pay) and there may or may not have been any payment. Payers are associated with patient encounters (there can either be one payer, several payers or no payers which is self pay).
The encounter_payers table only contains payers that are insurance companies. If a patient is self pay only there are no records in the encounter_payers table for that patient.
On any charges associated with an encounter I want to show all the same FC which should be the financial class of the primary payer on the encounter (which could be null if they are self pay only).

On payments and adjustments I want to show the actual payer which is available as payer_id in the transactions table.

The id of the FC is mstr_list_item_id in the mstr_lists table and financial_class in the payer_mstr table. The actual name of the FC is mstr_list_item_desc in the mstr_lists table so I have to link to that a couple different ways depending on what financial class name I need (the primary one associated with the encounter or the financial class associated with the payer_id in the transaction table).

Here’s an example: Patient A has Medicare(primary) and Blue Cross (secondary). The charge (chg) was 53.76 and Medicare paid 43.01 and Blue Cross sent a payment (pmt) of .00 to show they weren’t going to pay. (Enc is encounter) (FC is financial class).
I want 2 records returned (one for each payment). I expect to see:

Chg Pmt EncChgPayer EncChgPayerFC Pmt payer Pmt payer FC
53.76 43.01 Medicare MC Medicare MC
53.76 .00 Medicare MC Blue Cross BC


Here’s what I get returned instead:
Chg Pmt EncChgPayer EncChgPayerFC Pmt payer Pmt payer FC
53.76 43.01 Medicare MC Medicare MC
53.76 .00 Null Null Blue Cross BC
53.76 .00 Medicare Null Blue Cross BC
53.76 43.01 Null Null Medicare MC

It looks like Pmt Payer and Pmt Payer FC are working well, but I don’t understand why I get 4 records. Is there a way to show Medicare as the EncChgPayer for both records (which looks like it happened in the 3rd record) and MC as the EncChgPayerFC for both records?

Thanks -

 

[azoe]

I think I'm one step closer but still have a question.
I moved this join to be the last join and added this condition pm.payer_id = ep.payer_id:

left outer join encounter_payer ep on (pe.enc_id = ep.enc_id and pe.person_id = ep.person_id and pm.payer_id = ep.payer_id)

Now I get:
Chg Pmt EncChgPayer EncChgPayerFC PmtPayer PmtPayerFC
53.76 43.01 Medicare MC Medicare MC
53.76 .00 Null Null Blue Cross BC

So now I just need to know why the second EncChgPayer and EncChgPayerFC both return Null instead of Medicare and MC respectively. I need to show the EncChgPayer and EncChgPayerFC of the primary payer on the encounter which in this case is Medicare. I know Medicare is primary because the ep.cob field = 1 on the Medicare record. For Blue Cross ep.cob field = 2.

Thanks -