June 22, 2012 at 12:05 pm
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.
June 22, 2012 at 12:27 pm
Nope, clear as mud. You are going to have to provide DDL (CREATE TABLE statement) for the table(s) involved, sample data for each of the tables (8 to12 rows of data avg, not live data), expected results based on the sample data.
June 22, 2012 at 12:32 pm
Presuming you're going after a rolling 3-month average, something like this gets you there (the first part of the CTE is purely to set up the test data you provided).
;with monthaverage as (
select cast('20120501' as datetime) yearmonth, 'Creamcheese' product, 'Suzy' who, 4348 qty
union all
select '20120401', 'Creamcheese', 'Suzy', 3482
union all
select '20120301', 'Creamcheese', 'Suzy', 2743
union all
select '20120201', 'Creamcheese', 'Suzy', 3286
union all
select '20120101','Creamcheese', 'Suzy', 3276
union all
select '20111201','Creamcheese', 'Suzy', 5763
union all
select '20111101', 'Creamcheese', 'Suzy', 7546),
orderedMonthAvg as (
select ROW_NUMBER() over (PARTITION by product order by yearmonth) RN,* from monthaverage)
select o1.RN, o1.product,o1.yearmonth,o1.who,o1.qty, avg(o2.qty) rollingaverage from orderedMonthAvg o1 join orderedMonthAvg o2
on o1.product =o2.product and o1.RN between o2.RN-2 and o2.RN
group by o1.RN, o1.product,o1.yearmonth,o1.who,o1.qty
having COUNT(o2.qty)=3
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 22, 2012 at 12:32 pm
here's a WAG, but it only seems accurate for the the periods that have three months in them
With MyCTE (Date,Employee,Units)
AS
(
SELECT CONVERT(datetime,'2012-05-01'),'Creamcheese,Suzy',4348 UNION ALL
SELECT '2012-04-01','Creamcheese,Suzy',3482 UNION ALL
SELECT '2012-03-01','Creamcheese,Suzy',2743 UNION ALL
SELECT '2012-02-01','Creamcheese,Suzy',3286 UNION ALL
SELECT '2012-01-01','Creamcheese,Suzy',3276 UNION ALL
SELECT '2011-12-01','Creamcheese,Suzy',5763 UNION ALL
SELECT '2011-11-01','Creamcheese,Suzy',7546
)
SELECT
V1.Date,
V1.Employee,
((V1.Units + ISNULL(V2.Units,0) + ISNULL(V3.Units,0)) / 3.0),
*
FROM MyCTE V1
LEFT OUTER JOIN MyCTE V2
ON V1.Employee = V2.Employee
AND DATEDIFF(mm,0,V1.Date) = DATEDIFF(mm,0,V2.Date) - 1
LEFT OUTER JOIN MyCTE V3
ON V1.Employee = V3.Employee
AND DATEDIFF(mm,0,V1.Date) = DATEDIFF(mm,0,V3.Date) - 2
Lowell
June 22, 2012 at 1:23 pm
Because you said it needs to start over at the beginning of each year, I added in more rows for 2011 to test/demonstrate the results of that.
IF OBJECT_ID('tempdb..#Totals') IS NOT NULL
DROP TABLE #Totals
CREATE TABLE #Totals
(
TotalDate DATE,
EmpID TINYINT,
Total INT
)
GO
INSERT INTO #Totals
VALUES
('08/01/2011', 17, 5511),
('09/01/2011', 17, 6108),
('10/01/2011', 17, 2712),
('11/01/2011', 17, 7546),
('12/01/2011', 17, 5763),
('01/01/2012', 17, 3276),
('02/01/2012', 17, 3286),
('03/01/2012', 17, 2743),
('04/01/2012', 17, 3482),
('05/01/2012', 17, 4348)
SELECT B.EmpID, B.TotalDate, AVG(A.Total)
FROM #Totals A
INNER JOIN #Totals B
ON A.EmpID = B.EmpID
AND A.TotalDate > DATEADD(mm, -3, B.TotalDate)
AND A.TotalDate <= B.TotalDate
AND YEAR(A.TotalDate) = YEAR(B.TotalDate)
GROUP BY B.EmpID, B.TotalDate
I wasn't perfectly certain how to manage the dates, unless it will always be represented by the first of the month. But this should work, though maybe not the fastest.
June 22, 2012 at 1:47 pm
JeffEm,
This worked absolutely perfectly. THANK YOU!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply