Past 3 month's average every month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Past 3 month's average every month

Author

Topic

goodfences
Starting Member

1 Post

Posted - 2012-06-22 : 13:48:51

Hello, All.

I've managed to stump myself on a query. Here's what I need:

One of the metrics my company uses takes the average number of units paid in three months (including the month being measured). So, I need to write a query to be able to take any given month and average the unit count from that month and the unit count from 1, then two months back and return it as the given month's result.

Here's an example. To get figures for 2012-05-01, I need to take the Units figure for 2012-05-01, 2012-04-01, and 2012-03-01 and get an average but display them as the running average units for 2012-05-01.

Then I need to lather, rinse repeat for the whole year to date. My source tables look something like this:

Date Employee Units
2012-05-01 Creamcheese, Suzy 4348
2012-04-01 Creamcheese, Suzy 3482
2012-03-01 Creamcheese, Suzy 2743
2012-02-01 Creamcheese, Suzy 3286
2012-01-01 Creamcheese, Suzy 3276
2011-12-01 Creamcheese, Suzy 5763
2011-11-01 Creamcheese, Suzy 7546

I need to get something like this:

Date Employee Avg_3mo_Units
2012-05-01 Creamcheese, Suzy 3524
2012-04-01 Creamcheese, Suzy 3170
2012-03-01 Creamcheese, Suzy 3102
2012-02-01 Creamcheese, Suzy 4108
2012-01-01 Creamcheese, Suzy 5528

Note that there are multiple employees and I need to generate results for the entire team as well as individual (which I assume could be achieved easily enough with a group by)

Any help/suggestions you can offer are MUCH appreciated.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-06-22 : 15:55:31

[code]SELECT
a.Date,
a.Employee,
b.Avg_3mo_Units
FROM
YourTable a
OUTER APPLY
(
SELECT AVG(b.Units) AS Avg_3mo_Units
FROM YourTable b
WHERE b.Date >= DATEADD(mm,-2,a.Date)
AND b.Date <= a.Date
) b[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-22 : 23:02:25

if you've gaps in data during months you can use slight modification


SELECT
   a.Date,
   a.Employee,
   AVG(b.Units) AS Avg_3mo_Units
FROM
   YourTable a
   OUTER APPLY
   (
      SELECT TOP 3 b.Units
      FROM YourTable b
      WHERE b.Date <= a.Date
      ORDER BY b.Date DESC
   ) b
GROUP BY a.Date,
   a.Employee

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources