编写SQL查询筛选提交最新月份数据的客户近39个月记录

需求明确 #

每月客户向数据库提交数据，我们需要筛选出提交了最新月份（最大YYYYMM）数据的客户，并提取这些客户近39个月（当前对应201911-202301）的所有记录；未提交最新月份数据的客户需排除。

现有代码的错误 #

原SQL存在两个核心问题：

• 手动指定起始月份：tkpr.YYYYMM >= '201911' 是硬编码，后续月份更新时需要手动修改，不够灵活
• 筛选逻辑语法错误且逻辑不符需求：AND tkpr.YYYYMM IN MAX(tkpr.YYYYMM) 语法错误（聚合函数MAX不能直接用于IN子句），而且这个逻辑会只保留最新月份的单条记录，而非提交了最新月份的客户的所有近39个月数据

修正后的SQL #

WITH LatestData AS (
    -- 获取全局最新的YYYYMM月份
    SELECT MAX(YYYYMM) AS latest_ym
    FROM [REPORTS].[TKPR_SUMMARY_USD] tkpr
    JOIN [REPORTS].[CUSTOMER] cust ON tkpr.cust_ID = cust.cust_ID
    JOIN [EXTRACTS_CONFIGS].[ATS_PRRODUCT] prod ON tkpr.product_ID = prod.product_ID
    JOIN [EXTRACTS_CONFIGS].[ATS_TITLE] ti ON tkpr.TITLE_ID = ti.TITLE_ID
    WHERE ti.TITLE_GROUP = 'US'
        AND prod.product_GROUP = 'US'
        AND cust.MAIN_OFFICE_COUNTRY = 'United States'
        AND cust.EXCLUDE_cust = 'F' 
        AND cust.IMPLEMENTATION_STATUS_ID = '3'
),
EligibleCustomers AS (
    -- 获取在最新月份有提交记录的客户ID
    SELECT DISTINCT tkpr.cust_ID
    FROM [REPORTS].[TKPR_SUMMARY_USD] tkpr
    JOIN [REPORTS].[CUSTOMER] cust ON tkpr.cust_ID = cust.cust_ID
    JOIN LatestData ld ON tkpr.YYYYMM = ld.latest_ym
    WHERE cust.MAIN_OFFICE_COUNTRY = 'United States'
        AND cust.EXCLUDE_cust = 'F' 
        AND cust.IMPLEMENTATION_STATUS_ID = '3'
)
SELECT  
    prod.product AS 'product',
    cust.customer_NAME AS 'customer',
    ti.TITLE AS 'Title',
    tkpr.TIME_TYPE AS 'Time Type',
    tkpr.CONTRACT AS 'Contract',
    tkpr.YEAR AS 'Year',
    tkpr.YYYYMM AS 'Month (YYYYMM)',
    SUM(tkpr.FTE) AS 'Sum of FTE (RAW data)',
    SUM(tkpr.WORKED_AMOUNT_WP) AS 'Sum of Worked Amount Wp',    
    SUM(tkpr.WORKED_HOURS_WP) AS 'Sum of Worked Hours Wp'
FROM [REPORTS].[TKPR_SUMMARY_USD] tkpr
JOIN [REPORTS].[CUSTOMER] cust ON tkpr.cust_ID = cust.cust_ID 
JOIN [EXTRACTS_CONFIGS].[ATS_PRRODUCT] prod ON tkpr.product_ID = prod.product_ID
JOIN [EXTRACTS_CONFIGS].[ATS_TITLE] ti ON tkpr.TITLE_ID = ti.TITLE_ID
JOIN EligibleCustomers ec ON tkpr.cust_ID = ec.cust_ID
JOIN LatestData ld ON 1=1
WHERE   
    ti.TITLE_GROUP = 'US'
    AND prod.product_GROUP = 'US'
    -- 自动计算近39个月的起始月份，无需手动修改
    AND tkpr.YYYYMM >= FORMAT(DATEADD(MONTH, -38, CAST(CONCAT(LEFT(ld.latest_ym,4), '-', RIGHT(ld.latest_ym,2), '-01') AS DATE)), 'yyyyMM')
    AND cust.MAIN_OFFICE_COUNTRY = 'United States'
    AND cust.EXCLUDE_cust = 'F' 
    AND cust.IMPLEMENTATION_STATUS_ID = '3'
GROUP BY 
    prod.product,
    cust.customer_NAME,
    ti.TITLE,
    tkpr.TIME_TYPE,
    tkpr.CONTRACT,
    tkpr.YEAR,
    tkpr.YYYYMM
ORDER BY 
    cust.customer_NAME ASC, 
    tkpr.YYYYMM DESC

关键逻辑说明 #

• CTE LatestData：先获取符合业务过滤条件的全局最新月份，确保后续计算的起始月基于这个最新值
• CTE EligibleCustomers：筛选出在最新月份有提交记录的客户ID，确保只保留符合要求的客户
• 自动计算起始月份：通过DATEADD(MONTH, -38, ...)计算最新月份往前推38个月（包含当前月共39个月），再转成yyyyMM格式，避免手动硬编码
• 关联符合条件的客户：通过EligibleCustomers确保只查询提交了最新月份数据的客户的所有近39个月记录

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