Here are some sample rows.

ID YEAR SC_JANUARY_QTY SC_FEBRUARY_QTY SC_MARCH_QTY SC_APRIL_QTY SC_MAY_QTY SC_JUNE_QTY SC_JULY_QTY SC_AUGUST_QTY SC_SEPTEMBER_QTY SC_OCTOBER_QTY SC_NOVEMBER_QTY SC_OCTOBER_QTY
———- ———- ————– ————— ———— ———— ———- ———– ———– ————- —————- ————– ————— ————–
54896532 2006 845 795 736 852 988 1026 1154 1207 1002 894 658 599
54896532 2007 856 803 754 889 1015 1132 1196 1225 1197 951 708 623
54896532 2008 872
Example: If the day is May 2007, I would need the average of May 2006 through April 2007. If today’s date is used, I would need the average of February 2007 through January 2008. Thanks!

Hi,

This is interesting, for a very surprising reason. Though you could use AVG and/or LAG/LEAD to solve this, I beleive the simplest solution uses neither, only a simple self join:

SELECT	(	-- Begin list of items to average
		CASE WHEN mnth < 2	THEN p.sc_january_qty
					ELSE c.sc_january_qty
		END
	+	CASE WHEN mnth < 3	THEN p.sc_february_qty
					ELSE c.sc_february_qty
		END
	+	CASE WHEN mnth < 4	THEN p.sc_march_qty
					ELSE c.sc_march_qty
		END
	+	CASE WHEN mnth < 5	THEN p.sc_april_qty
					ELSE c.sc_april_qty
		END
	+	CASE WHEN mnth < 6	THEN p.sc_may_qty
					ELSE c.sc_may_qty
		END
	+	CASE WHEN mnth < 7	THEN p.sc_june_qty
					ELSE c.sc_june_qty
		END
	+	CASE WHEN mnth < 8	THEN p.sc_july_qty
					ELSE c.sc_july_qty
		END
	+	CASE WHEN mnth < 9	THEN p.sc_august_qty
					ELSE c.sc_august_qty
		END
	+	CASE WHEN mnth < 10	THEN p.sc_september_qty
					ELSE c.sc_september_qty
		END
	+	CASE WHEN mnth < 11	THEN p.sc_october_qty
					ELSE c.sc_october_qty
		END
	+	CASE WHEN mnth < 12	THEN p.sc_november_qty
					ELSE c.sc_november_qty
		END
	+				p.sc_december_qty
	)	-- End list of items to average
	/ 12	AS rolling_avg
FROM	(	-- Begin in-line view v to produce yr, mnth
	SELECT	54896532				AS id
	,	TO_NUMBER (TO_CHAR (SYSDATE, 'YYYY'))	AS yr
	,	TO_NUMBER (TO_CHAR (SYSDATE, 'MM'))	AS mnth
-- DEBUGGING: Either the two lines above or the two lines below should be commented out
--	,	2007	AS yr
--	,	8	AS mnth
	FROM	dual
	) v	-- End in-line view v to produce yr, mnth
JOIN	sc	c	ON	v.yr = c.year		-- c = current year
			AND	c.id = c.id
JOIN	sc	p	ON	v.yr = p.year + 1	-- p = previous year
			AND	c.id = p.id
;

I assume that id is a parameter. If not, simply remove all references to id.

I just noticed that double-dashes look like minus signs on this site (at least on my browser). My previous message does not use subtraction: anything that looks like a minus sign is the beginning of a comment.

I too thought analytics would be the answer. Thank you so much for your help!

The self join works very well, but it does not return data where there is just a record for the current year. The particular ID does not have a record for the previous year. Although the rolling average will be skewed, the data still needs to appear in the query results. Any ideas? Thanks.

That’s right: the join between c and p is an inner join: only rows that exist in both tables will be included. If you want to include rows from c even if there is no match in p, make it an outer join, that is, where the posted solution says

FROM ... sc c ... JOIN sc p ...

you should say

FROM ... sc c ... LEFT OUTER JOIN sc p ...

As you pointed out, this will give the total divided by 12, regardless of how many months were actually found. You can get a correct average by replacing the magic number 12 with a computation of how many months were not NULL, using CASE, DECODE or NVL2. I think the resulting code would be more complicated than that needed to pivot the data into a one-row-per-month format and use the analytic AVG function. If I have time later today, I’ll post an example of the analytic solution.

Hi, again,

This message contains
(1) a correction to my original solution
(2) details on how to make the self-join work with an OUTER JOIN (and get the right average)
(3) Pivot solution

– ******************** (1) a correction to my original solution ********************

There’s a typo in the query dated Feb. 1, 6:24PM. About four lines from the end, I said:

AND	c.id = c.id

I should have said:

AND	v.id = c.id

– ***** (2) details on how to make the self-join work with an OUTER JOIN (and get the right average) *****

If you do modify the self-join, as I suggested Feb. 7, 4:53PM, the mnth column tells you how many values you’re getting from c and how many from p, so you don’t have to test the individual columns for NULL to get the correct divisor to replace 12. However, you DO have to test each value for NULL to get the correct total, since x + y is NULL if either x or y is NULL. So if you do the self-join, the SELECT claus will end like this:

...	+	NVL	( CASE WHEN mnth < 12	THEN p.sc_november_qty
						ELSE c.sc_november_qty
			  END
			, 0
			)
	+	NVL	( p.sc_december_qty
			, 0
			)
	) /	-- End list of items to average
	(	CASE
			WHEN	p.year	IS NOT NULL
			THEN	12
			WHEN	mnth	> 1
			THEN	mnth - 1
		END
	)	AS rolling_avg

– ************************* (3) Pivot solution *************************

Somewhat shorter, but perhaps harder to understand, is this solution, where each month is pivoted onto a separate row:

SELECT avg (quantity) AS rolling_avg
FROM ( – Begin in-line view to compute dt and quantity
SELECT ADD_MONTHS (jan_1_dt, n) AS dt
, DECODE ( n
, 0 , sc_january_qty
, 1 , sc_february_qty
, 2 , sc_march_qty
, 3 , sc_april_qty
, 4 , sc_may_qty
, 5 , sc_june_qty
, 6 , sc_july_qty
, 7 , sc_august_qty
, 8 , sc_september_qty
, 9 , sc_october_qty
, 10 , sc_november_qty
, 11 , sc_december_qty
) AS quantity
, q_dt
FROM ( – Begin in-line view v to produce yr, mnth, q_dt
SELECT v2.*
, TO_DATE ( TO_CHAR (yr) || TO_CHAR (mnth)
, ‘YYYYMM’
) AS q_dt
FROM ( – Begin in-line view v2 to produce yr, mnth
SELECT 54896532 AS id
, TO_NUMBER (TO_CHAR (SYSDATE, ‘YYYY’)) AS yr
, TO_NUMBER (TO_CHAR (SYSDATE, ‘MM’)) AS mnth
– DEBUGGING: Either the two lines above or the two lines below should be commented out
– , 2007 AS yr
– , 8 AS mnth
FROM dual
) v2 – End in-line view v2 to produce yr, mnth
) v – End in-line view v to produce yr, mnth, q_dt
JOIN ( – Begin in-line view j to compute jan_1_dt
SELECT s.*
, TO_DATE (’01-Jan-’ || TO_CHAR (year)
, ‘DD-Mon-YYYY’
) AS jan_1_dt
FROM sc s
) j – End in-line view j to compute jan_1_dt
ON v.id = j.id
CROSS JOIN
( – Begin in-line view i of integers 0-11
SELECT n
FROM intgr
WHERE n BETWEEN 0 AND 11
) – End in-line view i of integers 0-11
) – End in-line view to compute dt and quantity
WHERE dt >= ADD_MONTHS (q_dt, -12)
AND dt < q_dt
;

This solution uses a table called intgr:

CREATE TABLE intgr
( n NUMBER (2));

My intgr table has 100 rows, with n ranging from 0 to 99. This is called a “counter table”, and is very useful for mimicing iteration in SQL. You can also use an in-line view as a counter table, for example:

SELECT ROWNUM AS n
FROM all_views
WHERE ROWNUM <= 12 -- or any other limit

or, in Oracle 10

SELECT LEVEL AS n
FROM dual
CONNECT BY LEVEL <= 12 -- or any other limit

This solution uses lots of in-line views. As with any query involving in-line views, if you run just the in-line views as separate queries, you’ll get a better idea of what each one is doing.

As you can see, the above solution still does not need analytic functions: the plain, old aggregate AVG is sufficient. The more I think about this, the less it surprises me. When you’re only producing one row of output, you can restrict what rows contribute to the output in the WHERE-clause. You might need analytic functions if you were displaying rolling averages for several target dates on several output rows of the same query, especially if the periods overlapped. If you do need to do something like that, you can adapt the query above to use the analytic AVG.

What is the difference between CODE block on double dashes, --, 

Versus pre tags

<pre>
these are double dashes –
</pre>

Ok, what happens if I use an HTML specified code tag??

<code>

These are double dashes, –

</code>