June 5th, 2012, 09:19 PM
Join Date: Jun 2012
Time spent in forums: 1 h 2 m 42 sec
Reputation Power: 0
Forgive me for any newbee blunders.
I have started a query and am running into many problems finishing this. Any assistance would be greatly appreciated.
Using a table with over 2 million records, Monthly sales numbers by month over the last 36 months. The task is to compare 2 aggregated records for 12 month periods (last 12 months compared to the 12 months before that - [last year compared to the year before]) and do this on a monthly rolling basis for the last 36 months.
Here is what I started with, expecting to use a temptable in the first round then pull from that to run calculations.
IF OBJECT_ID('tempdb.dbo.#TMPTBLSCO') IS NOT NULL
DROP TABLE #TMPTBLSCO
WHEN B.f3 = 'BEC' THEN 'ECO'
WHEN B.f3 = 'BSO' THEN 'OTR'
END AS f4
WHEN SUM(B.f6) BETWEEN 1 AND 12 THEN 1
WHEN B.f6 BETWEEN 2 AND 13 THEN 2
WHEN B.f6 BETWEEN 3 AND 14 THEN 3
WHEN B.f6 BETWEEN 4 AND 15 THEN 4
WHEN B.f6 BETWEEN 5 AND 16 THEN 5
WHEN B.f6 BETWEEN 6 AND 17 THEN 6
END AS f7
,SUM(CASE WHEN B.f6 BETWEEN 1 AND 12 THEN B.f8 ELSE 0 END) AS a1
,SUM(CASE WHEN B.f6 BETWEEN 2 AND 13 THEN B.f8 ELSE 0 END) AS a2
FROM dbo.SCO_REF002_BRAND_SLS AS B
GROUP BY B.f1, B.f2, B.f3, B.f5
ORDER BY B.f2, B.f5