OnBase Query Request: Does anyone have a query that shows the document types that have not been used? If so please share:

[Melika_Anderson]

Query Request: Anyone has a query that shows the document types that have not been used? If so please share:

Still learning the DB and what data is where.

[Roger_Linhart]

Hi Melika.

Do you mean Doc Types that contain zero documents?

[Marcus_Santodon · Accepted solution]

This will do the trick:

SELECT DT.itemtypenum, DT.itemtypename

FROM hsi.doctype DT

WHERE DT.itemtypenum not in ( SELECT DISTINCT hsi.itemdata.itemtypenum from hsi.itemdata)

[Ansley_Ingram]

I would not recommend running either of the above queries in a production environment as it will likely cause a performance issue due to the size of the table and the lack of indexes to support the first query. 

[Marcus_Santodon]

Just to be clear, I believe you are referring to Gabe's post and not mine. My query runs in under one second in our production environment,