Case statement in SQL Server with rolling weeks - easier way?

SQL Fiddle

Schema Setup: (SQLFiddle uses MS SQL 2014, but this will still work in 2008.)

CREATE TABLE MPI ( Code int, Region varchar(5), UseForecast bit
    , ForecastMultiplier decimal(5,1), MinimumOnHand int ) ;
INSERT INTO MPI ( Code, Region, UseForecast, ForecastMultiplier, MinimumOnHand )
VALUES 
      ( 1,'R1',0,0,50 )
    , ( 1,'R2',1,2,0)
    , ( 2,'R1',1,4.5,0)
    , ( 2,'R3',1,3,0)
    , ( 3,'R1',1,12,0)
;

CREATE TABLE FP ( Code int,  Region varchar(5), Month1 int, Month2 int
    , Month3 int, Month4 int, Month5 int, Month6 int, Month7 int
    , Month8 int, Month9 int, Month10 int, Month11 int, Month12 int ) ;
INSERT INTO FP (Code, Region, Month1, Month2, Month3, Month4, Month5
    , Month6, Month7, Month8, Month9, Month10, Month11, Month12 )
VALUES 
      ( 1,'R1',200,200,50,75,200,50,50,50,50,50,50,80 )
    , ( 1,'R2',500,500,500,500,500,500,500,50,50,50,50,500 )
    , ( 2,'R1',1000,0,0,0,0,0,0,50,50,50,50,0 )
    , ( 2,'R3',25,1000,1000,1000,1000,1000,1000,50,50,50,50,1000 )
;

Build Simple Calendar Table:

NOTE: The calendar table is for general use in your database, so add whatever calculations you need. I often use a modified version of Aaron Bertrand's example: https://github.com/shawnoden/SQL_Stuff/blob/master/sql_CreateDateDimension.sql

/* #dim is just a temp holding table for intermediate calculations. */
CREATE TABLE #dim (
      theDate           date        PRIMARY KEY
    , theDay            AS DATEPART(day, theDate)           --int
    , theWeek           AS DATEPART(week, theDate)          --int 
    , theMonth          AS DATEPART(month, theDate)         --int
    , theYear           AS DATEPART(year, theDate)          --int
    , yyyymmdd          AS CONVERT(char(8), theDate, 112)   /* yyyymmdd */
    , mm_dd_yy          AS CONVERT(char(10), theDate, 101)  /* mm/dd/yyyy */
);

/**************************************************************************/
/* Use the catalog views to generate as many rows as we need. */

INSERT INTO #dim ( theDate ) 
SELECT d
FROM (
    SELECT d = DATEADD(day, rn - 1, '20170101')
    FROM 
    (
        SELECT TOP (DATEDIFF(day, '20160101', '20190101')) 
            rn = ROW_NUMBER() OVER (ORDER BY s1.object_id)
        FROM sys.all_objects AS s1
        CROSS JOIN sys.all_objects AS s2
        ORDER BY s1.object_id
    ) AS x
) AS y;

/* Now create the final ref table for the dates. */
CREATE TABLE refDateDimension
(
      DateKey             int         NOT NULL PRIMARY KEY
    , theDate             date        NOT NULL
    , theDay              tinyint     NOT NULL  
    , WeekOfMonth         tinyint     NOT NULL
    , theMonth            tinyint     NOT NULL
    , theYear             int         NOT NULL
    , mm_dd_yy            char(10)    NOT NULL   /* mm/dd/yyyy */
);

/* Insert data in the dimension table. */
INSERT refDateDimension WITH (TABLOCKX)
SELECT
      DateKey              = CONVERT(int, yyyymmdd)
    , theDate              = theDate
    , theDay               = CONVERT(tinyint, theDay)
    , WeekOfMonth          = CONVERT(tinyint
                              , DENSE_RANK() OVER 
                                (PARTITION BY theYear, theMonth 
                                 ORDER BY theWeek)
                           )
    , theMonth             = CONVERT(tinyint, theMonth)
    , theYear              = theYear
    , mm_dd_yy             = mm_dd_yy
FROM #dim
OPTION (MAXDOP 1);

/* CLEANUP */
DROP TABLE #dim ;

Now we use the calendar table to quickly find the week number.:

SELECT s1.Code
  , s1.Region
  , CEILING(s1.ForecastMinOnHand) AS ForecastMinOnHand /* Round up to even number. */
FROM (
  SELECT MPI.Code, MPI.Region
    --, MPI.UseForecast, MPI.ForecastMultiplier, d.WeekOfMonth
    , CASE 
          WHEN MPI.UseForecast = 0          THEN MPI.MinimumOnHand
          WHEN MPI.ForecastMultiplier = 1    THEN CASE d.WeekOfMonth 
                                                      WHEN 1 THEN FP.Month1 
                                                      WHEN 2 THEN (FP.Month1*.75) + (FP.Month2*.25)
                                                      WHEN 3 THEN (FP.Month1*.5) + (FP.Month2*.5)
                                                      ELSE (FP.Month1*.25) + (FP.Month2*.75)
                                                 END
          WHEN MPI.ForecastMultiplier = 2    THEN CASE d.WeekOfMonth 
                                                      WHEN 1 THEN FP.Month1 + FP.Month2
                                                      WHEN 2 THEN (FP.Month1*.75) + FP.Month2 + (FP.Month3*.25)
                                                      WHEN 3 THEN (FP.Month1*.5) + FP.Month2 + (FP.Month3*.5)
                                                      ELSE (FP.Month1*.25) + FP.Month2 + (FP.Month3*.75)
                                                 END     
          WHEN MPI.ForecastMultiplier = 3    THEN CASE d.WeekOfMonth 
                                                      WHEN 1 THEN FP.Month1 + FP.Month2 + FP.Month3
                                                      WHEN 2 THEN (FP.Month1*.75) + FP.Month2 + FP.Month3 + (FP.Month4*.25)
                                                      WHEN 3 THEN (FP.Month1*.5) + FP.Month2 + FP.Month3 + (FP.Month4*.5)
                                                      ELSE (FP.Month1*.25) + FP.Month2 + FP.Month3 + (FP.Month4*.75)
                                                 END   
          WHEN MPI.ForecastMultiplier = 4.5  THEN CASE d.WeekOfMonth 
                                                      WHEN 1 THEN FP.month1 + fp.month2 + fp.month3 + fp.month4 + (FP.month5*.5)
                                                      WHEN 2 THEN (FP.Month1*.75) + fp.month2 + fp.month3 + fp.month4 + (FP.Month5*.75)
                                                      WHEN 3 THEN (FP.Month1*.5) + fp.month2 + fp.month3 + fp.month4 + FP.Month5
                                                      ELSE (FP.Month1*.25) + fp.month2 + fp.month3 + fp.month4 + FP.Month5 + (FP.Month6*.25)
                                                 END     

     END AS ForecastMinOnHand
     --, MPI.*, FP.*
  FROM MPI 
  INNER JOIN refDateDimension d ON CAST(getDate() AS date) = d.theDate 
  INNER JOIN FP ON MPI.Code = FP.Code
    AND MPI.Region = FP.Region
) s1

Results:

| Code | Region | ForecastMinOnHand |
|------|--------|-------------------|
|    1 |     R1 |                50 |
|    1 |     R2 |              1000 |
|    2 |     R1 |               750 |
|    2 |     R3 |              2269 |

My main purpose of using the calendar table here is that it takes away the GetWeekNumber() function and saves a bunch of processing cycles. It will also let your SQL optimizer run a bit more efficiently. In general, you would add a lot more columns to it to account for the time calculations you need elsewhere. It's a utility table. And extremely handy.

I still think the long CASE statement can be distilled down a bit. If you have the ability to edit the ForecastPivot view, you should be able to get at some of this data a little easier without a pivot.