SQL Server：无其他日期字段时滚动筛选最近7个季度数据的方法

要实现仅保留最近7个季度的数据，核心是先将字符串格式的季度字段转换为可排序、可计算的数值或日期，再筛选出目标范围的记录。以下是具体实现方案：

1. 转换季度字符串为可计算值 #

原字段shipped_planned_fiscal_quarter格式为"YYYY - Q N"，无法直接用于时间范围判断，需先提取年份和季度信息，转换为可排序的格式：

方法一：转换为季度结束日期 #

将每个季度转换为对应自然季度的最后一天，方便后续日期范围筛选：

SELECT 
    shipped_planned_fiscal_quarter,
    -- 提取年份和季度数字
    CAST(SUBSTRING(shipped_planned_fiscal_quarter, 1, 4) AS INT) AS fiscal_year,
    CAST(RIGHT(shipped_planned_fiscal_quarter, 1) AS INT) AS fiscal_quarter,
    -- 计算该季度的结束日期
    DATEADD(QUARTER, CAST(RIGHT(shipped_planned_fiscal_quarter, 1) AS INT), DATEFROMPARTS(CAST(SUBSTRING(shipped_planned_fiscal_quarter, 1, 4) AS INT), 1, 1)) - 1 AS quarter_end_date
FROM your_table_name;

方法二：转换为季度序号 #

将季度转换为YYYY*4 + 季度数的整数（例如2021Q4对应2021*4+4=8088），通过序号差值判断是否在最近7个季度内：

SELECT 
    shipped_planned_fiscal_quarter,
    CAST(SUBSTRING(shipped_planned_fiscal_quarter, 1, 4) AS INT)*4 + CAST(RIGHT(shipped_planned_fiscal_quarter, 1) AS INT) AS quarter_seq
FROM your_table_name;

2. 筛选最近7个季度的数据 #

基于上述转换后的字段，编写查询筛选出最近7个季度的记录：

基于季度日期的完整查询 #

WITH QuarterDates AS (
    SELECT 
        *,
        DATEADD(QUARTER, CAST(RIGHT(shipped_planned_fiscal_quarter, 1) AS INT), DATEFROMPARTS(CAST(SUBSTRING(shipped_planned_fiscal_quarter, 1, 4) AS INT), 1, 1)) - 1 AS quarter_end_date
    FROM your_table_name
),
CurrentQuarter AS (
    -- 获取当前自然季度的起始和结束日期
    SELECT 
        DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0) AS current_quarter_start,
        DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 1, 0) - 1 AS current_quarter_end
)
SELECT q.*
FROM QuarterDates q
CROSS JOIN CurrentQuarter c
-- 筛选当前季度及往前推6个季度的记录（共7个季度）
WHERE q.quarter_end_date >= DATEADD(QUARTER, -6, c.current_quarter_start);

基于季度序号的完整查询 #

WITH QuarterSeq AS (
    SELECT 
        *,
        CAST(SUBSTRING(shipped_planned_fiscal_quarter, 1, 4) AS INT)*4 + CAST(RIGHT(shipped_planned_fiscal_quarter, 1) AS INT) AS quarter_seq
    FROM your_table_name
),
CurrentQuarterSeq AS (
    -- 获取当前季度对应的序号
    SELECT YEAR(GETDATE())*4 + DATEPART(QUARTER, GETDATE()) AS current_seq
)
SELECT q.*
FROM QuarterSeq q
CROSS JOIN CurrentQuarterSeq c
-- 筛选序号在当前序号-6到当前序号之间的记录（共7个季度）
WHERE q.quarter_seq >= c.current_seq - 6;

3. 优化与注意事项 #

• 财年与自然年不一致的情况：如果公司财年起始月不是1月，需调整日期转换逻辑，例如先将财年季度映射为自然季度，或基于财年规则计算季度日期。
• 性能优化：若表数据量较大，可将转换后的quarter_seq或quarter_end_date设为持久化计算列，避免每次查询重复计算：

  ALTER TABLE your_table_name
  ADD quarter_seq AS CAST(SUBSTRING(shipped_planned_fiscal_quarter, 1, 4) AS INT)*4 + CAST(RIGHT(shipped_planned_fiscal_quarter, 1) AS INT) PERSISTED;
  

内容的提问来源于stack exchange，提问作者Brunda M