need help understanding a function.
- by Adam McC
i had previously asked for help writing/improving a function that i need to calculate a premium based on differing values for each month. the premium is split in to 12 months and earned on a percentage for each month. so if the policy start in march and we are in jan we will have earned 10 months worth. so i need to add up the monthly earning to give us the total earned. wach company wil have differeing earnings values for each month.
my original code is Here. its ghastly and slow hence the request for help.
and i was presented with the following code. the code works but returns stupendously large figures.
begin
set @begin=datepart(month,@outdate)
set @end=datepart(month,@experiencedate)
;with a as
(
select *,
case calmonth
when 'january' then 1
when 'february' then 2
when 'march' then 3
when 'april' then 4
when 'may' then 5
when 'june' then 6
when 'july' then 7
when 'august' then 8
when 'september' then 9
when 'october' then 10
when 'november' then 11
when 'december' then 12
end as Mnth
from tblearningpatterns
where clientname=@client
and earningpattern=@pattern
)
,
b as
(
select
earningvalue,
Mnth,
earningvalue as Ttl
from a
where Mnth=@begin
union all
select
a.earningvalue,
a.Mnth,
cast(b.Ttl*a.earningvalue as decimal(15,3)) as Ttl
from a
inner join b
on a.Mnth=b.Mnth+1
where a.Mnth<=@end
)
select @earningvalue=
Ttl
from b
inner join
(
select max(Mnth) as Mnth
from b
) c
on b.Mnth=c.Mnth
option(maxrecursion 12)
SET @earnedpremium = @earningvalue*@premium
end
can someone please help me out?
