基于最新日期的OnBase文档清理SQL单条记录查询求助

我正在清理某应用的报表及归档报表，采用Hyland's OnBase作为归档方案。需要确定每个ItemTypeNum（对应应用报表类型）下任意ItemNum（单份带时间戳的报表）的最近查看日期，以此判断400+报表是否有用或可禁用。

重要字段说明 #

• ItemTypeGroupNum：OnBase中应用的报表分区
• ItemTypeNum：关联应用输出的报表类型
• ItemNum：单份带时间戳的报表

示例 #

• ItemTypeGroupNum = 161：OnBase中应用的报表块
• ItemTypeNum = 5681：报表组标题为G/L File Maintenance
• ItemNum = 250727772：G/L File Maintenance - 6/25/2024

当前问题 #

我尝试过参考相关SQL写法、partition by+over子句、outer apply，均未得到预期结果。最近的SQL代码如下：

SELECT
    dt.itemtypenum, 
    dt.itemtypename,
    tlx.MaxLogDate
FROM 
    hsi.itemdata AS id
INNER JOIN 
    hsi.doctype AS dt ON dt.itemtypenum = id.itemtypenum   /* do I need this join?  */
OUTER APPLY
    (SELECT
         tl.num, MAX(tl.logdate) AS MaxLogDate
     FROM 
         hsi.transactionxlog AS tl
     WHERE
         tl.num = id.itemnum
         -- AND tl.itemnum = 1234
         AND tl.action IN (32, 35)       /* showing only 'view' actions  */
         -- AND tl.logdate >= '2023-06-25'   
     GROUP BY
         tl.logdate, tl.num) tlx

当前结果中同一ItemTypeNum对应多条不同ItemNum的记录，仅展示单份报表的最近访问日期，不符合需求。

期望结果 #

每个ItemTypeNum仅保留对应最大查看日期的记录，示例如下：

通过嵌套CTE分三步实现需求：先统计单份报表的最近查看日期，再按报表类型聚合最大日期，最后关联得到对应记录：

WITH ItemLastView AS (
    -- 统计每单份报表的最近查看日期
    SELECT
        id.itemtypenum,
        id.itemnum,
        id.itemname,
        MAX(tl.logdate) AS LastViewDate
    FROM hsi.itemdata id
    LEFT JOIN hsi.transactionxlog tl 
        ON tl.num = id.itemnum 
        AND tl.action IN (32, 35) -- 仅筛选查看类动作
    GROUP BY id.itemtypenum, id.itemnum, id.itemname
),
TypeMaxView AS (
    -- 按报表类型聚合，获取该类型下的最大查看日期
    SELECT
        itemtypenum,
        MAX(LastViewDate) AS MaxLogDate
    FROM ItemLastView
    GROUP BY itemtypenum
)
-- 关联得到每个类型对应最大查看日期的报表记录
SELECT
    ilm.itemtypenum,
    ilm.itemname AS ItemName,
    tm.MaxLogDate
FROM ItemLastView ilm
JOIN TypeMaxView tm 
    ON ilm.itemtypenum = tm.itemtypenum 
    AND (ilm.LastViewDate = tm.MaxLogDate OR (tm.MaxLogDate IS NULL AND ilm.LastViewDate IS NULL))
DISTINCT -- 若同一类型多个报表共享最大日期，去重保留一条
ORDER BY ilm.itemtypenum;

关键说明 #

1. 原SQL中INNER JOIN doctype是多余的，itemdata表已包含报表名称字段（itemname），无需额外关联。
2. 使用LEFT JOIN确保无查看记录的报表也能被纳入统计，最终显示NULL。
3. 若同一报表类型下有多份报表共享最大查看日期，DISTINCT会保留一条；若需展示所有对应报表，可去掉DISTINCT。

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