I am trying to cleanup an application’s reports and archived reports. Hyland’s OnBase is our archiving solution. I am trying to determine the most-recent date on which any ItemNum
was viewed. Determining the most-recent date that a report type was viewed can help me determine if any of these 400+ reports are useful, or if they can be disabled.
Some important columns:
ItemTypeGroupNum
– The application’s report section in OnbaseItemTypeNum
– Correlates to type of report coming out of our applicationItemNum
– A individual timestamped report
Example:
ItemTypeGroupNum
= 161 – The application’s block of reports within OnBaseItemTypeNum
= 5681 – Report group title = G/L File MaintenanceItemNum
= 250727772 – G/L File Maintenance – 6/25/2024
My 1st attempt and the resulting replies to review a similar, albeit different post here.
Next came attempts to use partition by
and over
. My SQL skills aren’t that sharp so this is possibly due to user error.
Next came an attempt to use outer apply
and that did not succeed, I referenced this post.
My most recent attempt:
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
The results I am getting are including multiple rows for the same ItemTypeNum
but different ItemNum
, with the most-recent accessed date for that individual ItemNum
:
ItemTypeNum | ItemName | MaxLogDate |
---|---|---|
5257 | Report Title 1 – 01/01/2024 | 01/02/2024 |
5257 | Report Title 1 – 05/16/2024 | 06/11/2024 |
5681 | G/L File Maintenance – 04/20/2024 | 05/15/2024 |
5681 | G/L File Maintenance – 03/08/2024 | 06/01/2024 |
5370 | Exception Report – 02/20/2024 | NULL |
What I am wanting to have returned would be only the 2nd, 4th, and 5th rows from the above table, the rows that have the max date for that group of ItemTypeNum
:
|ItemTypeNum|ItemName|MaxLogDate|
|–|—|—|
|5257|Report Title 1 – 05/16/2024|06/11/2024|
|5681|G/L File Maintenance – 03/08/2024|06/01/2024|
|5370|Exception Report – 02/20/2024|NULL|
OnBase database diagram