CODEWITHSUNDEEP
sqlsql-server
igorti
igorti

Reputation: 3856

Calculate rolling month value in SQL Server

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?

Upvotes: 2

Views: 2640

Answers (2)

bobs
bobs

Reputation: 22224

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]

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166606

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

Upvotes: 3

Related Questions