I have two tables
WAC table
ID wac_inc item
-- ----------------- ----
1 2.310000000000000 A
2 1.100000000000000 A
3 2.130000000000000 A
4 1.340000000000000 A
Baseline Table
item baseline
---- ------------------
A 10.000000000000000
Expected Result
ID wac_inc item Running_Mul
-- ----------------- ---- -----------
1 2.310000000000000 A 10.231 -- 10 * (1+(2.310000000000000/100))
2 1.100000000000000 A 10.343541 -- 10.231 * (1+(1.100000000000000/100))
3 2.130000000000000 A 10.563858 -- 10.343541 * (1+(2.130000000000000/100))
4 1.340000000000000 A 10.705413 -- 10.563858 * (1+(1.340000000000000/100))
Formula to find running_mul
is
Baseline * (1 + (wac_inc/100))
SQLFIDDLE
here for every row previous row Running_Mul
value is the baseline
and for the first row baseline
will be coming from baseline table
.
Hope i made it clear. AFAIK we can do this using CURSOR
but i want to avoid RBAR
as much as possible.
Can anyone suggest me the better way of doing it.
How to calculate run distance Divide your running time by your pace If your pace is 8 minutes per mile and you ran for 32 minutes: 32 min ÷ 8 min per mi = 4 miles
These are the steps to do long multiplication by hand: Arrange your numbers with the larger number on top and the smaller number on bottom. Line up the place values of the numbers in columns. Proceed right to left. Multiply the ones digit of the bottom number to the next digit to the left in the top number.
Divide your running time by your pace If your pace is 8 minutes per mile and you ran for 32 minutes: 32 min ÷ 8 min per mi = 4 miles How to calculate run time
How to calculate running pace. Divide your run time by your distance; If you ran 3 miles in 30 minutes: 30 min ÷ 3 mi = 10 minutes per mile pace How to calculate run distance. Divide your running time by your pace; If your pace is 8 minutes per mile and you ran for 32 minutes:
Try:
DECLARE @t TABLE
(
ID INT ,
wac DECIMAL(30, 10) ,
item CHAR(1)
)
DECLARE @b TABLE
(
item CHAR(1) ,
baseline DECIMAL(30, 10)
)
INSERT INTO @t
VALUES ( 1, 2.31, 'A' ),
( 2, 1.10, 'A' ),
( 3, 2.13, 'A' ),
( 4, 1.34, 'A' )
INSERT INTO @b
VALUES ( 'A', 10 );
WITH ordercte
AS ( SELECT * ,
ROW_NUMBER() OVER ( PARTITION BY item ORDER BY ID ) AS rn
FROM @t
),
rec
AS ( SELECT t.item ,
t.ID ,
t.wac ,
t.rn ,
b.baseline * ( 1 + ( t.wac / 100 ) ) AS m
FROM ordercte t
JOIN @b b ON b.item = t.item
WHERE t.rn = 1
UNION ALL
SELECT t.item ,
t.ID ,
t.wac ,
t.rn ,
c.m * ( 1 + ( t.wac / 100 ) )
FROM ordercte t
JOIN rec c ON t.item = c.item
AND t.rn = c.rn + 1
)
SELECT id ,
wac ,
item ,
m
FROM rec
Output:
id wac item m
1 2.3100000000 A 10.231000
2 1.1000000000 A 10.343541
3 2.1300000000 A 10.563858
4 1.3400000000 A 10.705414
EDIT1
I was trying to implement LOG EXP trick but could not manage unless @usr lead me to solution. So all credits to user @usr:
WITH ordercte
AS ( SELECT t.ID ,
t.wac ,
t.item ,
b.baseline ,
ROW_NUMBER() OVER ( PARTITION BY t.item ORDER BY ID ) AS rn
FROM @t t
JOIN @b b ON b.item = t.item
)
SELECT baseline
* EXP(SUM(LOG(( 1 + ( wac / 100 ) ))) OVER ( PARTITION BY item ORDER BY rn )) AS m
FROM ordercte
Or just:
SELECT t.ID, t.wac, t.item, baseline
* EXP(SUM(LOG(( 1 + ( wac / 100 ) ))) OVER ( PARTITION BY t.item ORDER BY t.ID )) AS m
FROM @t t
JOIN @b b ON b.item = t.item
if ID is the field you order by.
Output:
ID wac item m
1 2.3100000000 A 10.231
2 1.1000000000 A 10.343541
3 2.1300000000 A 10.5638584233
4 1.3400000000 A 10.7054141261722
EDIT2
For SQL 2008 use:
WITH cte
AS ( SELECT t.ID ,
t.wac ,
t.item ,
baseline ,
( SELECT SUM(LOG(( 1 + ( wac / 100 ) )))
FROM @t it
WHERE it.item = t.item AND it.ID <= t.ID
) AS e
FROM @t t
JOIN @b b ON b.item = t.item
)
SELECT ID, wac, item, baseline * EXP(e) AS m
FROM cte
EDIT3
Here is complete solution for SQL Server 2008 with dialing with NULLs and negative values:
WITH cte
AS ( SELECT t.ID ,
t.wac ,
t.item ,
b.baseline ,
ca.e,
ca.n,
ca.m
FROM @t t
JOIN @b b ON b.item = t.item
CROSS APPLY(SELECT SUM(LOG(ABS(NULLIF( 1 + wac / 100 , 0)))) as e,
SUM(SIGN(CASE WHEN 1 + wac / 100 < 0 THEN 1 ELSE 0 END)) AS n,
MIN(ABS(1 + wac / 100)) AS m
FROM @t it
WHERE it.item = t.item AND it.ID <= t.ID
) ca
)
SELECT ID, wac, item, baseline *
CASE
WHEN m = 0 THEN 0
WHEN n % 2 = 1 THEN -1 * EXP(e)
ELSE EXP(e)
END as Result
FROM cte
You can transform a series of multiplications into a series of additions with the following math trick:
exp(log(a) + log(b)) = a * b
So MUL(a)
is EXP(SUM(LOG(a)))
.
SELECT SUM(val) AS [Sum], EXP(SUM(LOG(val))) AS Product
FROM (VALUES
(1), (2), (3), (4)
) x(val)
This emits sum = 10, product = 24
.
Potential problems are rounding errors and zero factors.
You can now use one of the usual ways to achieve a running aggregate such as windowing functions. That's a solved problem.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With