求SSMS SQL查询语句：获取带月份标识的年初至今（YTD）数据

Hey there! Let's work through this YTD data requirement for SSMS. The goal is to return cumulative datasets for each month up to the current one, with a clear month column to label each period—so if it's February, you get January's standalone total and the Jan-Feb cumulative total, and so on.

Approach Overview #

We'll start by generating a sequence of months from 1 up to the current month. Then, for each month in that sequence, we'll calculate the cumulative sum of your target metric (like sales, revenue, etc.) from the start of the year up to that month. This ensures we get exactly the rows you need: one row per month, where each row's month value tells you the end of the cumulative period.

Solution 1: Using a Recursive CTE (Dynamic Month Generation) #

This method dynamically builds the month sequence using a recursive CTE, which is great if you don't want to hardcode all 12 months:

-- Replace these with your actual table and column names
DECLARE @CurrentMonth INT = MONTH(GETDATE())
DECLARE @CurrentYear INT = YEAR(GETDATE())

WITH MonthSequence AS (
    -- Start with the first month of the year
    SELECT 1 AS TargetMonth
    UNION ALL
    -- Recursively add months until we reach the current month
    SELECT TargetMonth + 1
    FROM MonthSequence
    WHERE TargetMonth < @CurrentMonth
)
SELECT
    ms.TargetMonth AS [month],
    -- Use ISNULL to replace NULL with 0 if there's no data for a period
    ISNULL(SUM(your_table.Amount), 0) AS YTD_Total
FROM MonthSequence ms
LEFT JOIN your_table
    ON YEAR(your_table.TransactionDate) = @CurrentYear
    AND MONTH(your_table.TransactionDate) <= ms.TargetMonth
GROUP BY ms.TargetMonth
ORDER BY ms.TargetMonth;

Solution 2: Using a VALUES List (Simpler, Static Month Generation) #

If you prefer a more straightforward approach, you can hardcode the 12 months and filter down to the current one. This is often faster for small datasets like month sequences:

DECLARE @CurrentMonth INT = MONTH(GETDATE())
DECLARE @CurrentYear INT = YEAR(GETDATE())

SELECT
    m.TargetMonth AS [month],
    ISNULL(SUM(your_table.Amount), 0) AS YTD_Total
FROM (
    VALUES 
        (1), (2), (3), (4), (5), (6),
        (7), (8), (9), (10), (11), (12)
) AS m(TargetMonth)
LEFT JOIN your_table
    ON YEAR(your_table.TransactionDate) = @CurrentYear
    AND MONTH(your_table.TransactionDate) <= m.TargetMonth
-- Only include months up to the current one
WHERE m.TargetMonth <= @CurrentMonth
GROUP BY m.TargetMonth
ORDER BY m.TargetMonth;

Key Notes #

• Customize for Your Data: Replace your_table with your actual table name, Amount with the metric you want to sum, and TransactionDate with your date column.
• Handling Dimensions: If you need to break down results by additional dimensions (like region, product category), just add those columns to the SELECT and GROUP BY clauses. For example:

  SELECT
      ms.TargetMonth AS [month],
      your_table.Region,
      your_table.ProductCategory,
      ISNULL(SUM(your_table.Amount), 0) AS YTD_Total
  FROM MonthSequence ms
  LEFT JOIN your_table
      ON YEAR(your_table.TransactionDate) = @CurrentYear
      AND MONTH(your_table.TransactionDate) <= ms.TargetMonth
  GROUP BY ms.TargetMonth, your_table.Region, your_table.ProductCategory
  ORDER BY ms.TargetMonth, your_table.Region, your_table.ProductCategory;
  

• NULL Handling: The ISNULL function ensures that if a month has no data, it returns 0 instead of NULL—adjust this if you prefer to keep NULLs.

Example Output #

If today is in March, your results will look something like this:

Where 1500 is January's total, 3200 is the Jan-Feb cumulative total, and 4800 is the Jan-Mar cumulative total.

内容的提问来源于stack exchange，提问作者Prashant Dhodmani