问参数月份选择:查询前一个月、12个月前和过去12个月的平均值。

EN

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]

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

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

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