SQL Server中基于10月1日起始财年的财季与财年计算需求

云上老周

2026-8-5

解决方案

要适配10月1日起始的财年规则，你需要调整季度和年份的计算逻辑，替换原查询中基于自然年的datepart计算：

修改后的完整查询

WITH cte AS (
    SELECT 
        CASE_DSCR,
        DATE_IMPOSED,
        AMENDED_ACTN_CD_DSCR,
        -- 计算财季：10-12月为Q1，1-3月为Q2，4-6月为Q3，7-9月为Q4
        CASE 
            WHEN MONTH(DATE_IMPOSED) IN (10,11,12) THEN 1
            WHEN MONTH(DATE_IMPOSED) IN (1,2,3) THEN 2
            WHEN MONTH(DATE_IMPOSED) IN (4,5,6) THEN 3
            WHEN MONTH(DATE_IMPOSED) IN (7,8,9) THEN 4
        END AS FiscalQuarter,
        -- 计算财年：10月及以后的日期归属下一年财年
        CASE 
            WHEN MONTH(DATE_IMPOSED) >= 10 THEN YEAR(DATE_IMPOSED) + 1
            ELSE YEAR(DATE_IMPOSED)
        END AS FiscalYear
    FROM dbo.TblCaseDetail
    WHERE DATE_IMPOSED BETWEEN '2018-10-01' AND '2021-06-30'
        AND casetype = 'Felony'
        AND Conviction = 1
)
SELECT 
    CASE_DSCR,
    AMENDED_ACTN_CD_DSCR,
    'Quarter ' + CAST(FiscalQuarter AS VARCHAR(1)) + ' FY' + CAST(FiscalYear AS VARCHAR(4)) AS "Quarter"
FROM cte
ORDER BY DATE_IMPOSED;