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.>