Sorry, I was not clear enough in the post. The issue is the grouping to determine file size cannot use rev.docrevnum or rev.revisionnum as they are not populated in EP3 at least.
This only leaves the rev.docrevdate for groupings.
I reformatted the date for the grouping down to the minute and that is producing the correct results however this is prone to issues to say the least.
If anyone knows how the revisions are truly ordered, why/how to populate the rev.docrevnum or rev.revisionnum in EP3 or if there is another table that needs to be join that would make a big difference here.
Thanks, Kevin
select distinct i.itemnum, i.itemname, ft.filetypestr, dt.isdocrevisionable, FORMAT (rev.docrevdate, 'yyyy-MM-dd HH:mm') as doc_revdate, rev.docrevnum, rev.revisionnum,
-- rev.docrevdate,
sum(idp.filesize) FileSize, sum(idp.numberpages) NumPages
-- select *
from hsi.itemdata i, hsi.revnotetable rev, hsi.itemdata id, hsi.itemdatapage idp, hsi.filetype ft, hsi.doctype dt
where 1=1
and i.itemnum = id.itemnum
and i.itemtypenum = dt.itemtypenum
and idp.filetypenum = ft.filetypenum
and id.itemnum = idp.itemnum
-- and i.itemnum = rev.itemnum
--
and idp.itemnum = rev.itemnum and idp.filetypenum = rev.filetypenum and idp.docrevnum = rev.docrevnum
--
and i.itemnum in (
1434663, 1434534,
1434605, 1434606, 1434607, 1434608, 1434609
)
group by i.itemnum, i.itemname, ft.filetypestr, dt.isdocrevisionable, FORMAT (rev.docrevdate, 'yyyy-MM-dd HH:mm'), rev.docrevnum, rev.revisionnum
order by i.itemnum, FORMAT (rev.docrevdate, 'yyyy-MM-dd HH:mm')
