Updating every row in a table with IF Statement conditions

Question

Hi everyone, this one has me stuck fast.

Data:

Month           Total         Impact    Forecast
------------------------------------------------
2010-04-01    45792.0000    1.0000    NULL
2010-05-01    51789.0000    1.0000    NULL
2010-06-01    58228.0000    1.0000    NULL
2010-07-01    52956.5217    1.0000    NULL
2010-08-01    53600.4700    0.8810    NULL
2010-09-01    54257.8784    1.1838    NULL
2010-10-01    55134.0669    1.0000    NULL 

Now what I'm trying to do is loop through the current contents of the table and update the Forecast column using conditional IF statements as follows:

If Impact = 1.0000 then forecast = (current month) total * Impact
If Impact < 1.0000 then forecast = (month -1) total * Impact
If Impact > 1.0000 then forecast = (month -2) total * Impact

I did think of using the CASE statement though calculating the forecast using previous dates stumped me, I presume I will need to utilise a while statements and RBAR.

Much appreciated.

Answer 1

You could make one fancy combined statement, but I'd just use 3 separate update statements:

UPDATE table
SET forecast = (current month) total * Impact
WHERE Impact = 1.0000
GO
UPDATE table
SET forecast = (month -1) total * Impact 
WHERE Impact < 1.0000
GO
UPDATE table
SET forecast = (month -2) total * Impact 
WHERE Impact > 1.0000
GO

Answer 2

How about this:

UPDATE [Table]
SET
    [Forecast] = [Impact] * CASE
        WHEN [Impact] = 1.0000 THEN
        (
            SELECT
                [InnerTable].[Total]
            FROM [Table] AS [InnerTable]
            WHERE [InnerTable].[Month] = [Table].[Month]
        )
        WHEN [Impact] < 1.0000 THEN
        (
            SELECT
                [InnerTable].[Total]
            FROM [Table] AS [InnerTable]
            WHERE [InnerTable].[Month] = DATEADD(month, -1, [Table].[Month])
        )
        ELSE
        (
            SELECT
                [InnerTable].[Total]
            FROM [Table] AS [InnerTable]
            WHERE [InnerTable].[Month] = DATEADD(month, -2, [Table].[Month])
        )
    END    
FROM [Table]

Answer 3

try this:

DECLARE @YourTable table (MonthOf datetime, TotalOf numeric(9,4), Impact numeric(9,4), forecast numeric(9,4))

INSERT @YourTable VALUES('2010-04-01',    45792.0000,    1.0000,    NULL)
INSERT @YourTable VALUES('2010-05-01',    51789.0000,    1.0000,    NULL)
INSERT @YourTable VALUES('2010-06-01',    58228.0000,    1.0000,    NULL)
INSERT @YourTable VALUES('2010-07-01',    52956.5217,    1.0000,    NULL)
INSERT @YourTable VALUES('2010-08-01',    53600.4700,    0.8810,    NULL)
INSERT @YourTable VALUES('2010-09-01',    54257.8784,    1.1838,    NULL)
INSERT @YourTable VALUES('2010-10-01',    55134.0669,    1.0000,    NULL)

;WITH MonthValues AS
(
    SELECT
        DATEADD(month,DATEDIFF(month,0,MonthOf),0) AS MonthYear
            ,SUM(TotalOf) TotalOf
    FROM @YourTable
    GROUP BY DATEADD(month,DATEDIFF(month,0,MonthOf),0)
)
UPDATE y
    SET Forecast=CASE
                     WHEN Impact = 1.0000 then m1.TotalOf * Impact
                     WHEN Impact < 1.0000 then m2.TotalOf * Impact
                     WHEN Impact > 1.0000 then m3.TotalOf * Impact
                 END
    FROM @YourTable                     y
        LEFT OUTER JOIN MonthValues  m1 ON DATEADD(month,DATEDIFF(month,0,y.MonthOf),0)=m1.MonthYear
        LEFT OUTER JOIN MonthValues  m2 ON DATEADD(month,-1,DATEADD(month,DATEDIFF(month,0,y.MonthOf),0))=m2.MonthYear
        LEFT OUTER JOIN MonthValues  m3 ON DATEADD(month,-2,DATEADD(month,DATEDIFF(month,0,y.MonthOf),0))=m3.MonthYear

SELECT * FROM @YourTable

OUTPUT:

MonthOf                 TotalOf     Impact  forecast
----------------------- ----------- ------- -----------
2010-04-01 00:00:00.000 45792.0000  1.0000  45792.0000
2010-05-01 00:00:00.000 51789.0000  1.0000  51789.0000
2010-06-01 00:00:00.000 58228.0000  1.0000  58228.0000
2010-07-01 00:00:00.000 52956.5217  1.0000  52956.5217
2010-08-01 00:00:00.000 53600.4700  0.8810  46654.6956
2010-09-01 00:00:00.000 54257.8784  1.1838  62689.9304
2010-10-01 00:00:00.000 55134.0669  1.0000  55134.0669

(7 row(s) affected)