SQL query showing workflow history on items in a specific life cycle

[Cindy_Hockensm1]

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'

[George_Sialmas]

@Cindy Hockensmith I personally don't understand what you are after. Can you please clarify what results the sql query should return so that we can help?

Respectfully,

George

[Cindy_Hockensm1]

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.  

[George_Sialmas]

@Cindy Hockensmith thanks for providing additional information, it helped me understand what data you would the sql query to return.

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

[Kevin_Perron]

@Cindy Hockensmith Find one document that has gone through the lifecycle as you described, get its itemnum (Doc Handle) and then trace it through hsi.wflog.  Maybe start working it out in a Test or lower environment.  This helps you understand the logging of the information in the table if you are not familiar.  You can right click and look at the document history and workflow transitions to see it in the UI also. 

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.  

SELECT 

rtrim(id.itemname) as 'Document'

, lcs.statename as 'Queue'

, rtrim(lc.lifecyclename) as 'Workflow'

, id.itemnum as 'Doc Handle'

, datediff (DD,wf.entrytime, getdate()) as 'Days in Queue'

FROM hsi.wflog wf

left JOIN hsi.lcstate lcs ON wf.statenum = lcs.statenum

INNER JOIN hsi.lifecycle lc ON wf.lcnum = lc.lcnum

inner join hsi.itemdata id on wf.itemnum = id.itemnum

WHERE  (wf.exittime = '1964-01-01') and lc.lcnum = 'yourLC_Num'

group by 

lcs.statename

, id.itemname

, lc.lifecyclename

, id.itemnum

, datediff (DD,wf.entrytime, getdate())

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