public
Created

  • Download Gist
953063.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78
CREATE TABLE TTable
(
ProjectId INT,
MonthYear DATETIME,
Month VARCHAR(5),
Year INT,
Generation FLOAT,
Expected FLOAT,
CarryOver FLOAT
)
INSERT INTO TTable
VALUES
(10,'2011-10-01 00:00:00.000','10',2011,56.748,56,0.748)
 
INSERT INTO TTable
VALUES
(10,'2011-11-01 00:00:00.000','11',2011,12.004,NULL,NULL)
 
INSERT INTO TTable
VALUES
(10,'2011-12-01 00:00:00.000','12',2011,10.632,NULL,NULL)
 
INSERT INTO TTable
VALUES
(10,'2012-01-01 00:00:00.000','01',2012,11.928,NULL,NULL)
INSERT INTO TTable
VALUES
(10,'2012-02-01 00:00:00.000','02',2012,7.580,NULL,NULL)
INSERT INTO TTable
VALUES
(100,'2011-12-01 00:00:00.000','12',2011,5.897,5,0.897)
INSERT INTO TTable
VALUES
(100,'2012-01-01 00:00:00.000','01',2012,0.881,NULL,NULL)
 
select * from ttable
 
SELECT * FROM TTable
 
DECLARE rowItems CURSOR FOR
SELECT ProjectId, [Month], [Year], Generation FROM TTable
ORDER BY ProjectId,[Year] ,CAST([Month] as int)
 
DECLARE @p int, @m VARCHAR(5), @y int,@g FLOAT,@priorP int, @carryOver FLOAT, @expected FLOAT
 
OPEN rowItems
 
FETCH NEXT FROM rowItems INTO @p, @m, @y, @g
 
SET @priorP = -1
SET @carryOver = 0.0
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
IF NOT @p = @priorP SET @carryOver = 0.0
SET @expected = @g+@carryOver
SET @carryOver = ROUND(@expected-FLOOR(@expected),3,0)
-- SELECT @p AS ProjectID, @m AS [Month], @y AS [Year], @g AS [Generation],
-- FLOOR(@expected) AS Expected,
-- @carryOver AS CarryOver
 
UPDATE TTable
SET EXPECTED = FLOOR(@expected), CarryOver = @carryOver
WHERE ProjectId = @p and [Month] = @m and [Year] = @y
SET @priorP = @p
 
FETCH NEXT FROM rowItems INTO @p, @m, @y, @g
 
END
 
CLOSE rowItems
DEALLOCATE rowItems
 
SELECT * FROM TTable

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.