I have 2 tables:
Tresent-JanOrg (Rate, Qty)
Rate (2, 5, 7)
Qty (3, 7, 7)
Temp (JanOrg)
Pkid JanOrg FebOrg MarOrg
1 2 0 0
I have to do the calculations 3 times (3 rows in Tresent) to calculate
JanOrg = (JanOrg * Qty * Rate) / 100
and the total of janorg should then be updated in temp table under janorg
So logically
JanOrg = ((2 * 2 * 3) / 100) + ((2 * 5 * 7) / 100) + ((2 * 3 * 7) / 100)))
I am trying to use a cursor in SQL Server 2012 but it is not returning correct results
Can you please suggest the better way of performing these calculations as i tried the below code :
OPEN complex_cursor
FETCH NEXT FROM complex_cursor INTO @rate, @qty
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE #temp
SET JANORG = (JANORG * @rate * @qty) / 100
FETCH NEXT FROM complex_cursor INTO @rate, @qty
END
CLOSE complex_cursor
DEALLOCATE complex_cursor
With this SELECT, you can see the values in question - are those values correct in your opinion?
SELECT
tjo.*, temp.JanOrg,
(1.0 * tjo.Rate * tjo.Qty * temp.JanOrg) / 100.0
FROM
dbo.TresentJanOrg tjo
CROSS APPLY
dbo.TempJanOrg temp
Output:
Rate Qty JanOrg (No column name)
------------------------------------
2 3 2 0.120000
5 7 2 0.700000
7 7 2 0.980000
And if you sum up those three values, you should get what you're looking for:
DECLARE @Result DECIMAL(10,4)
SELECT @result = SUM((1.0 * tjo.Rate * tjo.Qty * temp.JanOrg) / 100.0)
FROM dbo.TresentJanOrg tjo
CROSS APPLY dbo.TempJanOrg temp
SELECT @result
Returns a value of 1.8000.
ABSOLUTELY NO CURSORS NEEDED
A single, pretty simple set-based statement does it all - and so much faster than any RBAR (row-by-agonizing-row) processing with a cursor or a while loop....