09-10-2024 10:35 AM
Hi! I'm working on a report that lists all the current items in Cert Completed LC queue, when they entered the Cert Completed LC Queue, when the items initially entered the Life Cycle (initial queue), and the item name which is the auto-name that displays during searches.
I have this which only shows the current items in the Cert Completed LC queue. I appreciate any suggestions!
Set transaction isolation level read uncommitted
Select * from hsi.wflog
where statenum = 785
and exittime = '1964-01-01 00:00:00.000'
09-10-2024 05:59 PM
Respectfully,
George
09-11-2024 03:42 AM
Items enter a Loan Certification workflow/life cycle. The first queue they hit in the workflow is the Initial Queue. The item will transition to the Cert Complete Queue after a user performs a task. Need a report listing the current items in the Cert Complete Queue with following data: Display name of item (account number & name are included in auto-name), Cert Complete queue entry date, and Initial Queue entry date.
09-11-2024 05:54 AM
Have a go with the query below and let me know if that is what you are after. Please note that in the query below you'll need to add the Lifecycle ID and Queue ID as I don't have that information. Also you can play around with the column labelling as you see fit.
SELECT
RTRIM(tbl1.itemname) AS [Document Name],
MAX(CASE WHEN tbl2.statenum = <Cert Complete queueID> THEN tbl2.entrytime END) AS [Cert Complete Q Entry Date Time],
MAX(CASE WHEN tbl2.statenum = <Initial Q> THEN tbl2.entrytime END) AS [Initial Q Entry Date Time]
FROM hsi.itemdata tbl1
LEFT OUTER JOIN hsi.wflog tbl2 ON tbl1.itemnum = tbl2.itemnum
WHERE tbl2.lcnum = <Loan Certification workflow/life cycle ID>
AND tbl1.status = 0
GROUP BY RTRIM(tbl1.itemname)
Respectfully,
George
09-11-2024 06:02 AM
Select * from hsi.wflog
where itemnum = 'MyExampleDocID'
To get to the Name String of the document you need to join in hsi.itemdata.
Select itemname from hsi.itemdata
where itemnum = 'MyExampleDocID'
Now we have to start getting a little more specific on what we want from the workflow log and potentially join in hsi.lifecycle (if you want the Workflow LC name), hsi.lcstate to get the names of the lifecycle and queues the document moved through.
Here is an example that shows you the datediff function which is what will be needed to calculate time in Queue. This one needs your Lifecyclenum as the parameter to find days in queue for all current docs. It probably would need a little clean up before using... I am just grabbing from a library of previous queries.
datediff(MM, wf.entrytime, wf.exittime) will get time in queue in Min.
Look here for all kinds of helpful queries: https://community.hyland.com/forum/threads/71764-what-is-your-favorite-most-frequently-used-sql-repo...
Good luck,
KP
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.