October 1, 2009 at 5:24 pm
Hello Folks,
I am having trouble in finding out how to modify this TSQL procedure .Recovery amount in the Current procedure i am having is taking only ALLOCATION_CHECK_AMOUNT when C.Code(Code is coming from RECOVERY_TYPE table) is equal to '50','51' and '52'. My requirement is to add the RETAINED_BY_CLIENT in this current procedure which is Coming from the CASH_RECEIPT table i.e same table where ALLOCATION_CHECK_AMOUNT is also coming from. Finally i have to Sum both ALLOCATION_CHECK_AMOUNT and RETAINED_BY_CLIENT eventually giving the total amount i.e.GrossRecoverynoncash column in my report. I have no clue how to do this in this procedure. Any help is kindly appreciated.
I am using ALLOCATION_CHECK_AMOUNT in the CASE statement, Can i use a SUM in CASE statement?
Thanks,
open c_reportcontrol
fetch next from c_reportcontrol into @v_client,
@v_spool_level,
@bucket1,
@label1,
@bucket2,
@label2,
@bucket3,
@label3,
@bucket4,
@label4
while @@fetch_status = 0
begin
if @v_spool_level ='C'
begin
execute @v_id = getclientid @v_client
select @v_name = name from client where client_id = @v_id
declare c_caseinformation cursor for
select a.case_id,
legacy_case_id,
patient_name,
a.trv_policy_id,
a.trv_event_id,
/*recovery_amount, */
fee_schedule,
c.code,
case c.code when '97' then recovery_amount
when '17' then recovery_amount
when '50' then ALLOCATION_CHECK_AMOUNT
when '51' then ALLOCATION_CHECK_AMOUNT
when '52' then ALLOCATION_CHECK_AMOUNT
end,
hri_fee,
isnull(convert(numeric(18,2), admin_fee), 0),
taxes,
group_id
from cases a,
cash_receipt b,
recovery_type c
where a.case_id = b.case_id
and a.client_id = @v_id
and b.recovery_date >= @start_date
and b.recovery_date <= @end_date
and b.recovery_type_id = c.recovery_type_id
and c.code in ('97','17', '50', '51', '52')
end
October 2, 2009 at 7:02 am
I'm not really sure what you are trying to accomplish, but yes it is possible to use SUM in a CASE statement, but of course all the normal aggregation rules apply.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply