Help needed in adding the RETAINED_BY_CLIENT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help needed in adding the RETAINED_BY_CLIENT

Author

Topic

Nav522
Starting Member

27 Posts

Posted - 2009-10-01 : 17:38:07

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(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 present in the Cash receipt Table i.e same table where ALLOCATION_CHECK_AMOUNT is 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.

Thanks,

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

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-01 : 17:46:21

what have you tried so far?

Nav522
Starting Member

27 Posts

Posted - 2009-10-01 : 17:52:09

Hi rohit Well actually am a novice in programming. I have no clue where to start. Can we add directly to the ALLOCATION_CHECK_AMOUNT i.e when '50' then ALLOCATION_CHECK_AMOUNT + RETINED_BY_CLIENT.

Thanks

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-01 : 18:05:17

yes you can

Nav522
Starting Member

27 Posts

Posted - 2009-10-01 : 18:37:40

can anyone modify this code as per the requirement and provide it for me. i would really appreciate it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources