Category Archives for "SQL"

From the Toolbox: Querying Doc Type counts

Over more than a decade working as an ImageNow consultant and architect, I’ve accumulated a number of tools and tricks that help get the job done quickly and precisely. Some folks like to hoard knowledge, but my preference is to share.

In that spirit, the following is a really simple SQL query to pull the number of documents for all the Document Types in your ImageNow environment.  You can exclude the WHERE clause to run the query across all drawers.

--Set your database, usually INOW or INOW6

USE INOW

SELECT C.DOC_TYPE_NAME, COUNT(*)
FROM            inuser.IN_DRAWER A INNER JOIN
inuser.IN_DOC B ON A.DRAWER_ID = B.DRAWER_ID INNER JOIN
inuser.IN_DOC_TYPE C ON B.DOC_TYPE_ID = C.DOC_TYPE_ID
WHERE        (A.DRAWER_NAME = 'Medical Records')
GROUP BY C.DOC_TYPE_NAME
ORDER BY DOC_TYPE_NAME

Remember to run the query first in a TEST environment* to validate the logic and understand execution time. This script was tested on 7.1.5 but should be backward and forward compatible.

*ecmIQ assumes no risk for use of this or other information available on this site