Need a running total per encounter

[azoe]

What I need is a total charges.amt figure for each patient visit (I said distinct because there can be several payments to one charge which causes the charge to appear on multiple rows but I only want it added one time).

In thread767-1169148 I asked for help with a total for my charge figure. The solution was good except that I had trouble with a stringvar variable size limitation.

So I decided to try a SQL solution. Using faq149-182 I tried the sum in bold in the code below. I did it wrong because I get a total for the first encounter only (it shows that total on every row).

Then I read thread183-1070743 and it looks like I could do a While Loop (which I have never done - can I do it in the query below? And if so, what clause does it go in, the Select?)

Can I reset the variable for each patient encounter? (pe.enc_id is the key, I believe pe.enc_nbr is also unique)

In a Crystal Report I need to display the total for all encounters at the bottom (I don't know if I should try to get the total for each encounter in SQL and then total that in Crystal or if I should just try to get the total for all the encounters in SQL).

Ultimately I also want to be able to total by the groups I'm doing in Crystal which includes financial class and payer.


Thank you

Select  distinct c.charge_id, 
	td.trans_id, 
        pe.enc_Timestamp as EncDate,  
	pe.enc_id as PEenc_id,
	c.source_id as ChgEnc_id,
	pm.payer_id as PMpayer_id,
	pm.payer_name as PMpayerName,
	ep.payer_id as EncPayerID,
	t.payer_id as Tpayer_id,
	pe.cob1_payer_id as EncPrimaryPayer,
	pe.enc_nbr,
	c.create_Timestamp as ChgCreateDate,
	c.amt,
[b](  Select sum(amt)
	from charges inner join patient_encounter on charges.source_id = patient_encounter.enc_id
	where charges.create_Timestamp between '20050108' and '20051227'
	and (enc_nbr = '404236' or enc_nbr = '418268' or enc_nbr = '415443')
	--and c.charge_id <> charges.charge_id
) as ChgSum,
[/b]
		Case When td.trans_id is null then 0
		Else 1
		End as Pmt1_None0,
	
		Case 
		When EncPrimPayer.payer_name is not null then EncPrimPayer.payer_name --when there is an encounter_payer fill in the one with cob=1
		When pe.cob1_payer_id is null then 'Self'
		Else (
			select payer_name --need this part to fill in primary payer where pmts made from secondary payer
			from payer_mstr inner join encounter_payer on payer_mstr.payer_id = encounter_payer.payer_id 
			where encounter_payer.enc_id = ep.enc_id and cob = 1
			)
		End  as EncounterChgPayer,
	
		Case
		When pe.cob1_payer_id is Null then 'SP'
		When pe.cob1_payer_id is not Null then 
	 (
		SELECT	[mstr_list_item_desc]
		FROM	[mstr_lists] inner join payer_mstr on mstr_lists.mstr_list_item_id = payer_mstr.financial_class
			inner join encounter_payer on payer_mstr.payer_id = encounter_payer.payer_id
		WHERE	encounter_payer.enc_id = ep.enc_id and cob = 1 
	) 
		End As [ChargeFC],
	

	tcm.description as tranDescrip,
	td.paid_amt,
	td.adj_amt,
	
	Case
	When td.trans_id is null then 'NoTrans'
	When tcm.description = 'ZZ System Applied Void Charge' and pe.cob1_payer_id is not null then EncPrimPayer.payer_name
	When pe.cob1_payer_id is null then 'Self'
	When (td.trans_id is not null and t.payer_id is null /*and pe.cob1_payer_id is not null*/) then 'Self'
	--When pe.cob1_payer_id is not null and t.payer_id is null then 'Void'--if this line commented out they'll say Null which may be ok too
	Else pm.payer_name
	End as PmtPayer,

	Case 
	--When tcm.description = 'ZZ System Applied Void Charge' and pe.cob1_payer_id is not null then EncPrimPayer.financial_class
	When (td.trans_id is not null and t.payer_id is null /*and pe.cob1_payer_id is null*/) then 'SP'
	Else ml.mstr_list_item_desc 
	End as PmtPayerFC,

	t.closing_date

FROM    person per   
	left outer join patient_encounter pe on (pe.person_id = per.person_id and pe.practice_id = per.practice_id)
        left outer join charges c on (c.person_id = pe.person_id and c.source_id = pe.enc_id and pe.practice_id = c.practice_id)
	left outer join trans_detail td on (td.charge_id = c.charge_id and td.source_id = c.source_id and c.practice_id = td.practice_id)
	left outer join transactions t on (td.trans_id = t.trans_id and td.practice_id = t.practice_id)-- and t.payer_id = ep.payer_id)
	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)
	left outer join payer_mstr EncPrimPayer on EncPrimPayer.payer_id = pe.cob1_payer_id--(EncPrimPayer.payer_id = ep.payer_id and cob=1)
	left outer join encounter_payer ep on (pe.enc_id = ep.enc_id and pe.person_id = ep.person_id and EncPrimPayer.payer_id = ep.payer_id)
	left outer join tran_code_mstr tcm on t.tran_code_id = tcm.tran_code_id
		
WHERE   
  c.create_Timestamp between '20050108' and '20051227'	
and pe.enc_nbr = '404236' or pe.enc_nbr = '418268' or pe.enc_nbr = '415443'--only limited to these so I can check sums
order by c.charge_id

 

[druer]

I don't really have the time (ie brain power right now) to read through and understand exactly what you are looking to do and fix the whole thing you've typed ... but I think you are simply looking to do a GROUP BY so that you can sum up all charges and have that sum GROUPED BY. Check out GROUP BY in books on line for examples. Sorry if that doesn't help much, but in reading the first part of request, I think that would do what you want.

 

[Welshbird]

I agree with Druer,buoth in concept and in lack of brain power today!

Do you need the bold bit to look something like this?

(Select patientid, sum(amt) as ChgSum
from charges 
inner join 
patient_encounter 
on charges.source_id = patient_encounter.enc_id
where charges.create_Timestamp 
between '20050108' and '20051227'
and (enc_nbr = '404236' or enc_nbr = '418268' or enc_nbr = '415443')
and c.charge_id <> charges.charge_id
Group by patientid
)

Of course, patientid that I've used to simply group might be called summat else.

Am I folloing the gist here or missing the point?

Fee

[red] Nadolig Llawen a Blwyddyn Newydd Dda [/red]

 

[azoe]

I know - my brain hurts too - this is a bad week to have to think this hard.
I'm not sure what the grouping will do to my Crystal Report, but I will try it. Part of the problem with that is that patient encounters have insurance attached to them and payments have insurance associated with it. I need to show the primary insurance on the encounter for any charges where no payments have been made. If a payment has been made I need to show whatever insurance company actually paid. So I'm not sure which payer to group on.
Everything was fine until that string length error in Crystal.

Fee: Thanks - I'm not sure if that yields results I can use because I got this error:

Server: Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Thank you

 

[Welshbird]

Oh, Sorry Azoe - I think that might be due to the week too.

(Isn't it time for a glass of wine and a snooze on the sofa with a nice movie right now?)

Quite correct - If I had read that properly I would have spotted my error before I posted. Apologies....

I'm just running something myself at the moment, but will try and test on some made-up data if I can later this week, and post you a working version!



Fee

[red] Nadolig Llawen a Blwyddyn Newydd Dda [/red]