Modifying TSQL procedure - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Modifying TSQL procedure

Author

Topic

Nav522
Starting Member

27 Posts

Posted - 2009-10-01 : 20:12:52

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 inside a 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 07:54:44

isnt it just a matter of replacing ALLOCATION_CHECK_AMOUNT by ALLOCATION_CHECK_AMOUNT + RETAINED_BY_CLIENT in above case staement?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources