sql server - How to run sql query for each row value using set based approach -
i have following code placed inside cursor , have remove cursor due performance issue. trying use set based approach, have replace @originalvalue , @per_month variable sql query per set based approach @ same time need perform below calculation each row (with value of @originalvalue , @per_month)
if replacing @originalvalue , @per_month sql query select originalvalue , per_month tblreport reportdate = getdate()", in case below mention code not yielding result in row manner.
eg. cursor declared below records:
originalvalue per_month ------------------------ 100 1 200 3 600 4 500 7 code:
set @total = (@total * power(1 + (@value1 / 100.0), @originalvalue) + (@per_month / 100.0) * ( case when @originalvalue = 0 1 else case when (@value1 / 100.0)<> 0 (power(1 + (@value1 / 100.0), @originalvalue) - 1) / (@value1 / 100.0) * (1 + (@value1 / 100.0) * @method) else @originalvalue end end)) is there anyway can perform row based calculation using set based approach?
you can use cte running total of column. assume @value1, @method static int
declare @value1 int = 0, @method int = 1 declare @data table (id int identity(1,1), originalvalue int, per_month int) insert @data values (100, 1), (200, 3), (600, 4), (500, 7) ;with base ( -- original logic select *, power(1 + (@value1 / 100.0), originalvalue) + (per_month / 100.0) * ( case when originalvalue = 0 1 else case when (@value1 / 100.0)<> 0 (power(1 + (@value1 / 100.0), originalvalue) - 1) / (@value1 / 100.0) * (1 + (@value1 / 100.0) * @method) else originalvalue end end ) callogic @data ), runningtotal ( select *, callogic total base id = 1 union select nxt.*, curr.total * nxt.callogic runningtotal curr inner join base nxt on curr.id + 1 = nxt.id ) select * runningtotal
Comments
Post a Comment