Skip to content

@hoganlong /953063.sql
Created

Embed URL

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.