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
– Sachin Shanbhag, Commented Aug 12, 2010 at 12:35
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
– tdammers, Commented Aug 12, 2010 at 12:35
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
– JeffO, Commented Aug 12, 2010 at 12:59
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
– igorti, Commented Aug 12, 2010 at 17:12

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

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

bobs · Accepted Answer · 2010-08-12 18:53:09Z

Here's a solution that might work

WITH MyProducts AS
    (
    SELECT [Id], [ProductName],
        CAST([MONTH] + ' 1, ' + CAST([Year] AS varchar) AS datetime) as [MyDate], [MyValues]
    FROM ( SELECT Id,ProductName,[Year],[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
            FROM [SourceTable] ) MyTable
    UNPIVOT ([MyValues] FOR [Month] IN
        ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) AS [pivot]
    )

SELECT mp1.[ProductName], mp1.[MyDate], AVG(mp2.[MyValues]) AS MyValues
FROM [MyProducts] AS mp1
JOIN [MyProducts] AS mp2 ON mp2.MyDate BETWEEN DATEADD(MM, -11, mp1.[MyDate]) AND mp1.[MyDate]
WHERE mp1.[MyDate] BETWEEN '3/1/2010' AND '8/1/2010'
GROUP BY mp1.[ProductName], mp1.[MyDate]
ORDER BY mp1.[ProductName], mp1.[MyDate]

Collectives™ on Stack Overflow

Calculate rolling month value in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related