count on number of doc types and doc date of last document in that type

[Mike_Kroner1]

Hello,

Would anyone happen to have a query to display all document types, show the count for each document type,  display the last date that document was stored and finally which lifecycles that doc type is tied to?

Something like this

employee w2|4123|09/13/2020|HR

and so on

Thanks for all the help,

Mike

[Larissa_Armand · Accepted solution]

Hi Mike,

Relationships between document types and life cycles are stored in two different tables depending on whether it's a Unity life cycle (WORKITEMTYPELC) or not (ITEMTYPELC). Depending on what type(s) of life cycles you are using, you may want to adjust. This is also for Oracle db so may require slight adjustment to syntax. 

select trim(D.ITEMTYPENAME) as DocumentTypeName, count(I.ITEMNUM) as DocumentCount, to_char(max(I.DATESTORED), 'DD-MON-YYYY') as LastDateUsed,
trim(LU.LIFECYCLENAME) as UnityLifeCycle, trim(LX.LIFECYCLENAME) as LegacyLifeCycle
from ITEMDATA I
left outer join DOCTYPE D on I.ITEMTYPENUM = D.ITEMTYPENUM
left outer join WORKITEMTYPELC U on U.ITEMTYPENUM = I.ITEMTYPENUM
left outer join ITEMTYPELC X on X.ITEMTYPENUM = I.ITEMTYPENUM
left outer join LIFECYCLE LU on U.LCNUM = LU.LCNUM
left outer join LIFECYCLE LX on X.LCNUM = LX.LCNUM
where LU.WFCONTENTTYPE in ('0', '1')
group by D.ITEMTYPENAME, trim(LU.LIFECYCLENAME), trim(LX.LIFECYCLENAME)
order by D.ITEMTYPENAME
;

[Kent_Sullivan]

I turned a query similar to this into a WorkView Application.  with the filters there you can also group by doctype group and see last document uploaded and the count of documents uploaded within the last month.

It is a Unity script against a SQL database.  If you are interested in the details feel free to let me know.