SQL Server中如何根据银行财年结束日期计算财季

嘿，我来帮你搞定这个根据银行自定义财年分配季度的需求！你的核心痛点是要基于每个银行自己的财年结束日期，把财务记录对应到正确的财季，而不是默认的日历季度对吧？

先理清楚你的数据和需求：

你的表结构与测试数据 #

BankInfo（银行财务数据） #

create table dbo.BankInfo ( id int, asofdate date, Assets int )
insert into dbo.BankInfo Values(1,'2018-01-31',100)
insert into dbo.BankInfo Values(1,'2017-10-31',200)
insert into dbo.BankInfo Values(1,'2017-07-31',300)
insert into dbo.BankInfo Values(1,'2017-04-30',400)
insert into dbo.BankInfo Values(1,'2017-01-31',40)
insert into dbo.BankInfo Values(1,'2016-10-31',20)
insert into dbo.BankInfo Values(2,'2016-12-31',100)
insert into dbo.BankInfo Values(2,'2017-03-31',200)
insert into dbo.BankInfo Values(2,'2017-06-30',300)
insert into dbo.BankInfo Values(2,'2017-09-30',400)
insert into dbo.BankInfo Values(2,'2017-12-31',300)
insert into dbo.BankInfo Values(2,'2016-03-31',400)

yearenddate（各银行财年结束日） #

create table dbo.yearenddate ( id int, enddate date )
insert into dbo.yearenddate values(1,'2018-01-31')
insert into dbo.yearenddate values(2,'2017-06-30')

你期望的输出 #

需要为每条记录分配qtr字段，财年结束日对应Q4，往前每3个月依次是Q3、Q2、Q1，示例输出如下：

create table dbo.outputqtr ( id int, asofdate date, Assets int, qtr smallint )
insert into dbo.outputqtr Values(1,'2018-01-31',100,4)
insert into dbo.outputqtr Values(1,'2017-10-31',200,3)
insert into dbo.outputqtr Values(1,'2017-07-31',300,2)
insert into dbo.outputqtr Values(1,'2017-04-30',400,1)
insert into dbo.outputqtr Values(1,'2017-01-31',40,4)
insert into dbo.outputqtr Values(1,'2016-10-31',20,3)
insert into dbo.outputqtr Values(2,'2016-12-31',100,2)
insert into dbo.outputqtr Values(2,'2017-03-31',200,3)
insert into dbo.outputqtr Values(2,'2017-06-30',300,4)
insert into dbo.outputqtr Values(2,'2017-09-30',400,1)
insert into dbo.outputqtr Values(2,'2017-12-31',300,2)
insert into dbo.outputqtr Values(2,'2016-03-31',400,3)

你之前尝试用DENSE_RANK的思路没问题，但分区条件不对，导致没能正确按财年分组计算。下面给你两种可行的解决方案：

方案一：基于日期差直接计算季度 #

这个方法更通用，不管你的数据是不是严格按季度上报都能用。核心是先找到每条记录所属财年的结束日期，再通过月份差映射到季度：

WITH BankFiscalContext AS (
    SELECT 
        b.id,
        b.asofdate,
        b.Assets,
        -- 找到当前记录对应的最近财年结束日（不晚于当前记录日期）
        MAX(y.enddate) OVER (
            PARTITION BY b.id 
            ORDER BY b.asofdate 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS fiscal_year_end
    FROM dbo.BankInfo b
    LEFT JOIN dbo.yearenddate y 
        ON b.id = y.id 
        AND y.enddate <= b.asofdate
)
SELECT 
    id,
    asofdate,
    Assets,
    -- 根据月份差计算季度：财年结束日为Q4，往前每3个月递减1
    CASE 
        WHEN DATEDIFF(month, asofdate, fiscal_year_end) BETWEEN 0 AND 2 THEN 4
        WHEN DATEDIFF(month, asofdate, fiscal_year_end) BETWEEN 3 AND 5 THEN 3
        WHEN DATEDIFF(month, asofdate, fiscal_year_end) BETWEEN 6 AND 8 THEN 2
        WHEN DATEDIFF(month, asofdate, fiscal_year_end) BETWEEN 9 AND 11 THEN 1
    END AS qtr
FROM BankFiscalContext
ORDER BY id, asofdate DESC;

方案二：利用窗口函数按财年组排名 #

如果你的数据是严格按季度上报的（每个季度一条记录），这个方法更简洁：

WITH BankFiscalGroups AS (
    SELECT 
        b.id,
        b.asofdate,
        b.Assets,
        y.enddate,
        -- 按财年分组：以财年结束日为基准，每12个月为一个财年
        DENSE_RANK() OVER (
            PARTITION BY b.id 
            ORDER BY DATEADD(year, -DATEDIFF(month, y.enddate, b.asofdate)/12, y.enddate)
        ) AS fiscal_year_group
    FROM dbo.BankInfo b
    LEFT JOIN dbo.yearenddate y ON b.id = y.id
)
SELECT 
    id,
    asofdate,
    Assets,
    -- 在每个财年组内，按日期倒序排名得到季度
    DENSE_RANK() OVER (
        PARTITION BY id, fiscal_year_group 
        ORDER BY asofdate DESC
    ) AS qtr
FROM BankFiscalGroups
ORDER BY id, asofdate DESC;

为什么你的原查询不行？ #

你原查询里的DENSE_RANK按MONTH(asofdate)分区，这会把不同年份但同月的记录分到一组，显然不符合财年的逻辑。我们需要的是按财年区间分组，而不是日历月份，所以必须先关联到财年结束日，再基于财年区间来计算排名或直接映射季度。

测试这两种方案都能得到你想要的输出结果，可以根据你的数据特点选择其中一种。

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