Selecting count of documents and sum of pages

[David_Geller5]

Good Morning All.

I'm struggling with the best method to capture the sum of the number of pages per per document type.   The docs all come in from DIP. Here is my simplified SQL for getting the count of documents, but I need to also have a column for the number of pages.  For this example, we will assume all pages are image and single page tif.

select

rtrim(dt.itemtypename) [Doc Type],

count(*) [Number of docs]

-- need to insert here for number of pages

from hsi.itemdata id

inner join hsi.doctype dt on dt.itemtypenum=id.itemtypenum

inner join hsi.wflog wfl on wfl.itemnum=id.itemnum

where wfl.statenum=179 and wfl.statenumto=181

group by rtrim(dt.itemtypename)

Ignoring date ranges,  no lock / set isolation....

how would I show the number of pages for the group of doc counts?

Thanks!!!

[David_Juhlin]

@David Geller  - I don't have the specific SQL for you, but you essentially need to do this:

• Join the hsi.itemdatapage table on the itemnum.
• Use max(docrevnum) to make sure you have the current revision.
• Select max(itempagenum) and add 1 to get the page count (since page 1 is itempagenum=0)

Test using numberpages and/or physicalpagenum, but I think using max(itempagenum)+1 should give you the desired/expected page count.