Parameter month selection: Make a query that shows previous month, 12 months ago and last 12 months average
I wanted to play around with my Total_Sales table. This is how the data looks like (using SQL Server 2008 R2)
Name Year Month Sales
------ ---- ----- -----
Alfred 2011 1 100
Alfred 2011 2 200
Alfred 2011 3 300
Alfred 2011 4 400
Alfred 2011 5 500
Alfred 2011 6 600
Alfred 2011 7 700
Alfred 2011 8 800
Alfred 2011 9 900
Alfred 2011 10 500
Alfred 2011 11 500
Alfred 2011 12 500
The SQL query I want to create should display the data like this:
Name Year Month Sales Prev_Month Month_Last_Year_Sales Last_12_Month_AVG
------ ---- ----- ----- ---------- --------------------- -----------------
Alfred 2011 1 100 NULL (year 2010, month 1) (2010_01 to 2011_01)/(12)
Alfred 2011 2 200 100 (year 2010, month 2) (2010_02 to 2011_02)/(12)
Alfred 2011 3 300 200 (year 2010, month 3) (2010_03 to 2011_03)/(12)
Alfred 2011 4 400 300 (year 2010, month 4) (2010_04 to 2011_04)/(12)
Alfred 2011 5 500 400 (year 2010, month 5) (2010_05 to 2011_05)/(12)
Alfred 2011 6 600 500 (year 2010, month 6) (2010_06 to 2011_06)/(12)
Alfred 2011 7 700 600 (year 2010, month 7) (2010开发者_开发知识库_07 to 2011_07)/(12)
Alfred 2011 8 800 700 (year 2010, month 8) (2010_08 to 2011_08)/(12)
Alfred 2011 9 900 800 (year 2010, month 9) (2010_09 to 2011_09)/(12)
Alfred 2011 10 500 900 (year 2010, month 10) (2010_10 to 2011_10)/(12)
Alfred 2011 11 500 500 (year 2010, month 11) (2010_11 to 2011_11)/(12)
Alfred 2011 12 500 500 (year 2010, month 12) (2010_12 to 2011_12)/(12)
To copy the prior month I am using this: Copy prior month value and insert into new row
SELECT
TS.name,
TS.year,
TS.month,
TS.sales,
COALESCE(TS2.sales, 0) AS prior_month_sales
FROM
TotalSales TS
LEFT OUTER JOIN TotalSales TS2 ON
TS2.name = TS.name AND
(
(TS2.year = TS.year AND TS2.month = TS.month - 1) OR
(TS.month = 1 AND TS2.month = 12 AND TS2.year = TS.year - 1)
)
The NULL in Prev_Month is to show that the start of the Total_Sales was in year 2011 month 1, so no prior data for this example.
I am planning to use a parameter, where you select a month.
Thanks for any help!SELECT
[this_month].*,
[last_month].Sales AS [prev_month_sales],
[last_year].Sales AS [month_last_year_sales],
[yearly].AverageSales AS [last_12_month_average]
FROM
Total_Sales AS [this_month]
LEFT JOIN
Total_Sales AS [last_month]
ON [last_month].Name = [this_month].Name
AND (
([last_month].Year = [this_month].Year AND [last_month].Month = [this_month].Month - 1)
OR ([last_month].Year = [this_month].Year - 1 AND [last_month].Month = 12 AND [this_month].Month = 1)
)
LEFT JOIN
TotalSales AS [last_year]
ON [last_year].Name = [this_month].Name
AND [last_year].Year = [this_month].Year - 1
AND [last_year].Month = [this_month].Month
CROSS APPLY
(
SELECT
AVG(Sales) AS AverageSales
FROM
Total_Sales
WHERE
Name = [this_month].Name
AND (
(Year = [this_month].Year AND Month <= [this_month].Month)
OR (Year = [this_month].Year - 1 AND Month > [this_month].Month)
)
)
AS [yearly]
The Average isn't the value divided by 12, as there are not always 12 months worth of data in the preceding year. But the AVG() function takes care of that for you.
Also, I'd highly reccomend against using YEAR and MONTH fields. Instead I would recommend using a DATETIME field to represent the "Month Start" and using SQL Server's Date functions...
Last Month : MonthStart = DATEADD(MONTH, -1, ThisMonth)
A Year Ago : MonthStart = DATEADD(YEAR, -1, ThisMonth)
Last Year : MonthStart > DATEADD(YEAR, -1, ThisMonth) AND MonthStart <= ThisMonth
Another answer that I have no idea is faster or not...
WITH sales AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Year, Month) AS month_id,
*
FROM
yearly_sales
)
SELECT
Name = [this_month].Name,
Year = MAX([this_month].Year),
Month = MAX([this_month].Month),
Sales = MAX([this_month].Sales),
Last_Month = MAX(CASE WHEN [13_months].month_id = [this_month].month_id - 1 THEN [13_months].Sales END),
Last_Year = MAX(CASE WHEN [13_months].month_id = [this_month].month_id - 12 THEN [13_months].Sales END),
Yearly_AVG = AVG(CASE WHEN [13_months].month_id > [this_month].month_id - 12 THEN [13_months].Sales END)
FROM
Sales AS [this_month]
INNER JOIN
Sales AS [13_months]
ON [13_months].Name = [this_month].Name
AND [13_months].month_id <= [this_month].month_id
AND [13_months].month_id >= [this_month].month_id - 12
GROUP BY
[this_month].Name
From AceAlfred -
One problem I have run into, maybe you know a quick fix? When a employee has not booked his sales for a previous month there is no data to display for this individual. Is there a way to add a row with the missing employee, where the "sales" is set to 0 and still pull the data for the other rows? Ex. Year 2012 -- Month 1 -- Name Alfred -- Sales 0 -- Prev 500
One approach is to "fix" your data, ensuring it always has values in it. I'd recommend doing that in whatever system is populating your data. Or as a nightly batch that checks for people that didn't enter their data and sticks in 0's for you (To be updated if/when the real data arrives). But if you can't...
CREATE TABLE agent (id INT, name NVARCHAR(128), start_date DATETIME, leave_date DATETIME);
-- populate with your agents
CREATE TABLE calendar (year DATETIME, month DATETIME, day DATETIME);
-- populate with all dates you want to report on
CREATE TABLE sales (agent_id INT, month_start DATETIME, total INT);
-- populate with your data
WITH new_raw_data AS
(
SELECT
agent.id AS [agent_id],
calendar.month AS [month_start],
COALESCE(sales.total, 0) AS [total]
FROM
agent
INNER JOIN
calendar
ON calendar.month_start >= COALESCE(DATEADD(month, -1, agent.start_date), '2000 Jan 01')
AND calendar.month_start <= COALESCE(agent.leave_date, '2079 Dec 31')
LEFT JOIN
sales
ON sales.agent_id = agent.id
AND sales.month_start = calendar.month_start
WHERE
calendar.month_start = calendar.day -- Only use records for the start of each month
)
,
<your other queries, using the nicely cleaned data, go here.>
精彩评论