clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 756648 fiddles created (13609 in the last week).

CREATE TABLE Hist ( UniqueIdentifierCode VARCHAR(10) PRIMARY KEY, EffectiveDate date, Value1 decimal(18,4), Value2 decimal(18,4), Value3 decimal(18,4), Total decimal(18,4) );
 hidden batch(es)


INSERT INTO Hist VALUES ('N01', '20151112', 2.0, 2.0, 3.0, 20), ('N02', '20151114', 2.0, 3.0, 1.0, 0), ('N03', '20151115', 1.0, 1.0, 1.0, 0), ('N04', '20151117', 1.0, 3.0, 1.0, 0), ('N05', '20151119', 2.0, 1.0, 3.0, 0);
5 rows affected
 hidden batch(es)


DECLARE @NEW_TOTAL DECIMAL(18,4) = 0; DECLARE @FIRST BIT = 1; DECLARE @V1 DECIMAL(18,4), @V2 DECIMAL(18,4), @V3 DECIMAL(18,4), @T DECIMAL(18,4); DECLARE curHist CURSOR FOR SELECT Value1, Value2, Value3, Total FROM Hist ORDER BY EffectiveDate FOR UPDATE OF Total; OPEN curHist; FETCH NEXT FROM curHist INTO @V1, @V2, @V3, @T; WHILE (@@FETCH_STATUS = 0) BEGIN IF @FIRST = 1 BEGIN SET @NEW_TOTAL = (@V1 + @V2 + @V3) * @T; END ELSE BEGIN UPDATE Hist SET Total = @NEW_TOTAL WHERE CURRENT OF curHist; SET @NEW_TOTAL = (@V1 + @V2 + @V3) * @NEW_TOTAL; END SET @FIRST = 0; FETCH NEXT FROM curHist INTO @V1, @V2, @V3, @T; END CLOSE curHist; DEALLOCATE curHist; SELECT * FROM Hist;
UniqueIdentifierCode EffectiveDate Value1 Value2 Value3 Total
N01 2015-11-12 2.0000 2.0000 3.0000 20.0000
N02 2015-11-14 2.0000 3.0000 1.0000 140.0000
N03 2015-11-15 1.0000 1.0000 1.0000 840.0000
N04 2015-11-17 1.0000 3.0000 1.0000 2520.0000
N05 2015-11-19 2.0000 1.0000 3.0000 12600.0000
 hidden batch(es)