Calculate rolling month value in SQL Server

Question

I have a table in SQL Server 2005 database that has following columns:

Id,ProductName,Year,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec

What I need to do is to calculate an average ROLLING value for a product.

For instance, if product named "Car" has 2 rows in a table(Year 2009 and 2010) I want to calculate average values from August 2009 to August 2010 or March 2009 to March 2010.

What is the best way to accomplish this?

if you had these column as rows in the table, it would've been easier to get the value you are expecting. — Sachin Shanbhag
Use a proper data model maybe? Columns that carry the names of the months just screams of bad design; the columns should be ID, ProductName, Year, Month. Then all sorts of calculations will be easy. — tdammers
Why wouldn't you just store the date instead of year & month? If it's the last day of the month, you could do other time calculations a lot easier (daily average). — JeffO
Well guys, I know it looks like a bad design but the reason it's done the way you see it is that this table is used by a rendering engine that renders webpages for CRUD operations. Since userinterface should look like this(textfield for each month) the underlying table structure must be like in example. — igorti

Adriaan Stander Adriaan Stander · Accepted Answer · 2010-08-12T12:42:07

Using Sql Server 2005, you can have a look at the UNPIVOT.

Once you have them back into rows where they belong, you can start playing around with the data.

Something like

DECLARE @Table TABLE(
        Id INT,
        ProductName VARCHAR(20),
        [Year] INT,
        Jan FLOAT,
        Feb FLOAT,
        Mar FLOAT,
        Apr FLOAT,
        May FLOAT,
        Jun FLOAT,
        Jul FLOAT,
        Aug FLOAT,
        Sep FLOAT,
        Oct FLOAT,
        Nov FLOAT,
        [Dec] FLOAT
)

INSERT INTO @Table SELECT 1,'Car',2009,1,2,3,4,5,6,7,8,9,10,11,12
INSERT INTO @Table SELECT 1,'Car',2010,1,2,3,4,5,6,7,8,9,10,11,12

SELECT  Id,
        ProductName,
        CAST(YearMonth + ' ' + CAST([Year] AS VARCHAR(4)) AS DATETIME) MonthDate, 
        Vals
FROM    (
            SELECT  Id,ProductName,[Year],Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,[Dec]
            FROM    @Table
        ) tbl
        UNPIVOT (Vals FOR YearMonth IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,[Dec])) as unpvt

Calculate rolling month value in SQL Server

2 Answers