基于财年季度参考表的SQL关联查询：仅返回财年内首次服务记录

云上老周

2026-7-3

SQL需求：仅返回每个财年内首次服务的季度记录

问题描述

运行SQL脚本时，输出需包含fiscal_year、Quarter、first_service、ServiceStartDate、ServiceEndDate字段。当前关联Ref_Dates参考表会返回服务日期覆盖的所有季度记录，需要调整为仅返回每个财年内首次出现的服务记录（即使服务日期跨多个财年）。

额外规则：客户每年10月1日视为新服务客户，若ServiceStartDate在某财年Q4，且ServiceEndDate在次年财年Q1或之后，则该客户需同时计入Q4和Q1。

示例

输入：ServiceStartDate=2020-03-14，ServiceEndDate=2020-10-03
当前输出：返回2020财年Q2、Q3、Q4及2021财年Q1的记录
预期输出：仅返回2020财年Q2和2021财年Q1的记录

相关表结构

1. Ref_Dates表（需使用State_前缀字段）

-- 创建Ref_Dates表
CREATE TABLE Ref_Dates (
    [Fiscal_Year] INT NULL,
    [ANL_FYStart] DATE NULL,
    [ANL_FYEnd] DATE NULL,
    [State_FYStart] DATE NULL,
    [State_FYEnd] DATE NULL,
    [Quarter] VARCHAR(2) NULL,
    [ANL_QStart] DATE NULL,
    [ANL_QEnd] DATE NULL,
    [State_QStart] DATE NULL,
    [State_QEnd] DATE NULL
);

-- 插入数据
INSERT INTO Ref_Dates VALUES
(2020, '2019-07-01', '2020-06-30', '2019-10-01', '2020-09-30', 'Q1', '2019-07-01', '2019-09-30', '2019-10-01', '2019-12-31'),
(2020, '2019-07-01', '2020-06-30', '2019-10-01', '2020-09-30', 'Q2', '2019-10-01', '2019-12-31', '2020-01-01', '2020-03-31'),
(2020, '2019-07-01', '2020-06-30', '2019-10-01', '2020-09-30', 'Q3', '2020-01-01', '2020-03-31', '2020-04-01', '2020-06-30'),
(2020, '2019-07-01', '2020-06-30', '2019-10-01', '2020-09-30', 'Q4', '2020-04-01', '2020-06-30', '2020-07-01', '2020-09-30'),
(2021, '2020-07-01', '2021-06-30', '2020-10-01', '2021-09-30', 'Q1', '2020-07-01', '2020-09-30', '2020-10-01', '2020-12-31'),
(2021, '2020-07-01', '2021-06-30', '2020-10-01', '2021-09-30', 'Q2', '2020-10-01', '2020-12-31', '2021-01-01', '2021-03-31'),
(2021, '2020-07-01', '2021-06-30', '2020-10-01', '2021-09-30', 'Q3', '2021-01-01', '2021-03-31', '2021-04-01', '2021-06-30'),
(2021, '2020-07-01', '2021-06-30', '2020-10-01', '2021-09-30', 'Q4', '2021-04-01', '2021-06-30', '2021-07-01', '2021-09-30'),
(2022, '2021-07-01', '2022-06-30', '2021-10-01', '2022-09-30', 'Q1', '2021-07-01', '2021-09-30', '2021-10-01', '2021-12-31'),
(2022, '2021-07-01', '2022-06-30', '2021-10-01', '2022-09-30', 'Q2', '2021-10-01', '2021-12-31', '2022-01-01', '2022-03-31'),
(2022, '2021-07-01', '2022-06-30', '2021-10-01', '2022-09-30', 'Q3', '2022-01-01', '2022-03-31', '2022-04-01', '2022-06-30'),
(2022, '2021-07-01', '2022-06-30', '2021-10-01', '2022-09-30', 'Q4', '2022-04-01', '2022-06-30', '2022-07-01', '2022-09-30'),
(2023, '2022-07-01', '2023-06-30', '2022-10-01', '2023-09-30', 'Q1', '2022-07-01', '2022-09-30', '2022-10-01', '2022-12-31'),
(2023, '2022-07-01', '2023-06-30', '2022-10-01', '2023-09-30', 'Q2', '2022-10-01', '2022-12-31', '2023-01-01', '2023-03-31'),
(2023, '2022-07-01', '2023-06-30', '2022-10-01', '2023-09-30', 'Q3', '2023-01-01', '2023-03-31', '2023-04-01', '2023-06-30'),
(2023, '2022-07-01', '2023-06-30', '2022-10-01', '2023-09-30', 'Q4', '2023-04-01', '2023-06-30', '2023-07-01', '2023-09-30'),
(2024, '2023-07-01', '2024-06-30', '2023-10-01', '2024-09-30', 'Q1', '2023-07-01', '2023-09-30', '2023-10-01', '2023-12-31'),
(2024, '2023-07-01', '2024-06-30', '2023-10-01', '2024-09-30', 'Q2', '2023-10-01', '2023-12-31', '2024-01-01', '2024-03-31'),
(2024, '2023-07-01', '2024-06-30', '2023-10-01', '2024-09-30', 'Q3', '2024-01-01', '2024-03-31', '2024-04-01', '2024-06-30'),
(2024, '2023-07-01', '2024-06-30', '2023-10-01', '2024-09-30', 'Q4', '2024-04-01', '2024-06-30', '2024-07-01', '2024-09-30');

2. Client表

-- 创建Client表
CREATE TABLE [dbo].[Client](
    [document_id] [int] NULL,
    [first_name] [varchar](max) NULL,
    [last_name] [varchar](max) NULL
);
-- 插入数据
INSERT INTO Client VALUES
(10308040, 'Client1fn', 'Client1ln');

3. ProgramEnrollment表

-- 创建ProgramEnrollment表
CREATE TABLE [dbo].[ProgramEnrollment](
    [RecordID] [int] NULL,
    [ServiceStartDate] [date] NULL,
    [ServiceEndDate] [date] NULL,
    [Program] [varchar](max) NULL,
);
-- 插入数据
INSERT INTO ProgramEnrollment
(130085, '2020-03-14', '2020-10-03', 'Program6');

4. Documents表

-- 创建Documents表
CREATE TABLE [dbo].[Documents](
    [ParentID] [int] NULL,
    [RecordID] [int] NULL
);
-- 插入数据
INSERT INTO Documents
(10308040,130085);

已尝试的代码

关联语句

JOIN Ref_Dates ref ON ((pe.ServiceStartDate <= ref.State_QEnd AND pe.ServiceEndDate >= ref.State_QStart) 
     OR (pe.ServiceStartDate <= ref.State_QEnd AND pe.ServiceStartDate >=ref.State_QStart 
         AND (pe.ServiceEndDate IS NULL OR pe.ServiceEndDate = '')))

完整SELECT语句

SELECT DISTINCT cp.document_id AS Client_ProfileID
,ref.Fiscal_Year
,ref.[Quarter]
,MIN(CAST(pe.ServiceStartDate AS DATE)) AS first_service
,CAST(pe.ServiceStartDate AS DATE) AS ServiceStartDate
,CASE
    WHEN pe.ServiceEndDate IS NULL THEN 
        CASE
            WHEN (MONTH(GETDATE()) > 10 OR (MONTH(GETDATE()) = 10 AND DAY(GETDATE()) >= 1)) AND pe.ServiceStartDate < CAST(CAST(YEAR(GETDATE()) AS VARCHAR) + '-10-01' AS DATE) THEN 
                CAST(CAST(YEAR(GETDATE()) AS VARCHAR) + '-10-01' AS DATE)
            ELSE 
                CAST(GETDATE() AS DATE)
        END
    ELSE 
        CAST(pe.ServiceEndDate AS DATE)
END AS ServiceEndDate

FROM Client_Profile AS cp
    JOIN documents docs ON docs.ParentId=cp.document_id
    JOIN ProgramEnrollment pe ON pe.RecordID=docs.RecordId
    JOIN Ref_Dates ref ON ((pe.ServiceStartDate <= ref.State_QEnd AND pe.ServiceEndDate >= ref.State_QStart) 
     OR (pe.ServiceStartDate <= ref.State_QEnd AND pe.ServiceStartDate >=ref.State_QStart 
         AND (pe.ServiceEndDate IS NULL OR pe.ServiceEndDate = '')))


GROUP BY cp.document_id
,ref.Fiscal_Year
,ref.[Quarter]
,pe.ServiceStartDate
,pe.ServiceEndDate

ORDER BY cp.document_id
GO

解决方案

核心思路是通过CTE筛选每个财年内的首次服务季度，同时处理跨10月1日的特殊规则：

WITH ServiceFiscalQuarters AS (
    SELECT
        cp.document_id AS Client_ProfileID,
        pe.ServiceStartDate,
        pe.ServiceEndDate,
        ref.Fiscal_Year,
        ref.[Quarter],
        ref.State_QStart,
        -- 标记财年内的季度顺序，首次出现的季度排名为1
        ROW_NUMBER() OVER (
            PARTITION BY cp.document_id, pe.RecordID, ref.Fiscal_Year 
            ORDER BY ref.State_QStart
        ) AS QuarterRank,
        -- 判断是否是跨10月1日的Q4季度
        CASE 
            WHEN pe.ServiceStartDate <= DATEFROMPARTS(YEAR(ref.State_FYStart), 10, 1) 
                 AND pe.ServiceEndDate >= DATEFROMPARTS(YEAR(ref.State_FYStart), 10, 1)
                 AND ref.Quarter = 'Q4'
            THEN 1
            ELSE 0
        END AS IsCrossFYSwitch
    FROM Client_Profile cp
    JOIN documents docs ON docs.ParentId = cp.document_id
    JOIN ProgramEnrollment pe ON pe.RecordID = docs.RecordId
    JOIN Ref_Dates ref ON (
        (pe.ServiceStartDate <= ref.State_QEnd AND (pe.ServiceEndDate >= ref.State_QStart OR pe.ServiceEndDate IS NULL))
    )
),
FilteredQuarters AS (
    SELECT *
    FROM ServiceFiscalQuarters
    WHERE 
        -- 保留每个财年的首次季度
        QuarterRank = 1
        -- 保留跨财年切换的Q4，且下一年Q1存在
        OR (IsCrossFYSwitch = 1 AND EXISTS (
            SELECT 1
            FROM ServiceFiscalQuarters sq
            WHERE sq.Client_ProfileID = ServiceFiscalQuarters.Client_ProfileID
              AND sq.RecordID = ServiceFiscalQuarters.RecordID
              AND sq.Fiscal_Year = ServiceFiscalQuarters.Fiscal_Year + 1
              AND sq.Quarter = 'Q1'
              AND sq.QuarterRank = 1
        ))
)
SELECT 
    Client_ProfileID,
    Fiscal_Year,
    [Quarter],
    MIN(ServiceStartDate) AS first_service,
    ServiceStartDate,
    -- 复用原结束日期处理逻辑
    CASE
        WHEN ServiceEndDate IS NULL THEN 
            CASE
                WHEN (MONTH(GETDATE()) > 10 OR (MONTH(GETDATE()) = 10 AND DAY(GETDATE()) >= 1)) 
                     AND ServiceStartDate < CAST(CAST(YEAR(GETDATE()) AS VARCHAR) + '-10-01' AS DATE) THEN 
                    CAST(CAST(YEAR(GETDATE()) AS VARCHAR) + '-10-01' AS DATE)
                ELSE 
                    CAST(GETDATE() AS DATE)
            END
        ELSE 
            ServiceEndDate
    END AS ServiceEndDate
FROM FilteredQuarters
GROUP BY 
    Client_ProfileID,
    Fiscal_Year,
    [Quarter],
    ServiceStartDate,
    ServiceEndDate
ORDER BY Client_ProfileID, Fiscal_Year, [Quarter];

逻辑说明

ServiceFiscalQuarters CTE：关联所有

基于财年季度参考表的SQL关联查询：仅返回财年内首次服务记录

问题描述 #

示例 #

相关表结构 #

1. Ref_Dates表（需使用State_前缀字段） #

2. Client表 #

3. ProgramEnrollment表 #

4. Documents表 #

已尝试的代码 #

关联语句 #

完整SELECT语句 #

解决方案 #

逻辑说明 #