SSRS 2012：实现每周应用快照的表关联问题求助

问题描述 #

当前查询仅能匹配主表周初日期与子表接收日期所属周完全一致的申请，无法生成2024年4月1日、4月8日这类周初日期的快照——我需要快照包含周初之前接收且截至周初仍在处理中的申请，即满足：

• 接收日期 ≤ 周初日期
• 结果日期为空 或 结果日期 > 周初日期

核心问题出在最后的表连接逻辑：原查询用Sub.WB = Main.Week做硬关联，只保留了接收周与快照周相同的申请，完全没覆盖“早于快照周接收且未办结”的场景。

修改方案 #

1. 移除子表中用于关联的WB字段（无需再用接收周做匹配）
2. 将表连接改为交叉连接+条件筛选，让每个周初快照都关联所有符合“未办结且接收早于/等于当前快照周”的申请
3. 修正原查询中GROUP BY的冗余/错误字段（原#Sub的GROUP BY包含未在SELECT中出现的rqia_directoratename，属于语法问题）

修改后的完整SQL代码 #

-- 生成周初日期表（保留原逻辑）
SELECT DATEADD(dd, 0 - (@@DATEFIRST + 5 + DATEPART(dw, dt)) % 7, CONVERT(date, dt, 103)) AS Week
INTO #Main
FROM RQIA_Custom_Calendar_View
WHERE CONVERT(date, dt, 103) >= DATEADD(year, DATEDIFF(month, 90, CURRENT_TIMESTAMP) / 12, 90)
OPTION (maxrecursion 0);

-- 生成申请数据子表（移除WB字段，修正GROUP BY）
SELECT 
    FilteredAccount.rqia_servicetypeidname AS ServiceType,
    FilteredAccount.rqia_subtypeidname AS ServiceSubtype,
    FilteredAccount.name AS RQIAService,
    'Variation Application' AS ApplicationType,
    Filteredrqia_variation.rqia_name AS ApplicationID,
    CONVERT(date, Filteredrqia_variation.rqia_applicationreceiveddate, 103) AS DateReceived,
    CONVERT(date, Filteredrqia_variation.rqia_dateofoutcome, 103) AS OutcomeDate
INTO #Sub
FROM FilteredAccount
INNER JOIN Filteredrqia_variation 
    ON FilteredAccount.name = Filteredrqia_variation.rqia_serviceidname
WHERE 
    Filteredrqia_variation.statuscodename NOT LIKE '%Withdrawn'
    AND Filteredrqia_variation.statuscodename NOT LIKE '%Refused'
    AND Filteredrqia_variation.statuscodename NOT LIKE '%Returned'
GROUP BY 
    FilteredAccount.rqia_servicetypeidname,
    FilteredAccount.rqia_subtypeidname,
    FilteredAccount.name,
    Filteredrqia_variation.rqia_name,
    CONVERT(date, Filteredrqia_variation.rqia_applicationreceiveddate, 103),
    CONVERT(date, Filteredrqia_variation.rqia_dateofoutcome, 103);

-- 生成快照查询：交叉连接后筛选符合条件的记录
SELECT 
    Main.Week,
    Sub.ApplicationType,
    Sub.ApplicationID,
    Sub.DateReceived,
    Sub.OutcomeDate,
    'Yes' AS Ongoing  -- 筛选后均为符合条件的记录，直接标记为在办
FROM #Main AS Main
CROSS JOIN #Sub AS Sub
WHERE 
    Sub.DateReceived <= Main.Week
    AND (Sub.OutcomeDate IS NULL OR Sub.OutcomeDate > Main.Week)
GROUP BY 
    Main.Week,
    Sub.ApplicationType,
    Sub.ApplicationID,
    Sub.DateReceived,
    Sub.OutcomeDate
ORDER BY 
    Main.Week,
    Sub.ApplicationID;

-- 清理临时表
DROP TABLE #Main;
DROP TABLE #Sub;

补充说明（保留空周快照） #

如果需要显示“无在办申请的周初日期”，可将CROSS JOIN改为LEFT JOIN调整逻辑：

SELECT 
    Main.Week,
    Sub.ApplicationType,
    Sub.ApplicationID,
    Sub.DateReceived,
    Sub.OutcomeDate,
    CASE 
        WHEN Sub.ApplicationID IS NOT NULL THEN 'Yes'
        ELSE 'No' 
    END AS Ongoing
FROM #Main AS Main
LEFT JOIN #Sub AS Sub
    ON Sub.DateReceived <= Main.Week
    AND (Sub.OutcomeDate IS NULL OR Sub.OutcomeDate > Main.Week)
GROUP BY 
    Main.Week,
    Sub.ApplicationType,
    Sub.ApplicationID,
    Sub.DateReceived,
    Sub.OutcomeDate
ORDER BY 
    Main.Week,
    Ongoing DESC;

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