<cfquery name="qmd_test" datasource="FSLibrary_RPC">
with mostRecentId as ( select max(g1.messageid) messageid, message
from tbl_GS1_BatchMessages g1 with (nolock)
where g1.ownerType = 'MFR'
and g1.messageCode = 'UNRESOLVED BRAND'
and g1.createdate between '2022-12-28 00:00:00.000' and '2023-01-04 23:59:59.000'
group by message ),

itemCount as ( select distinct GTIN, message, ownerid
from tbl_GS1_BatchMessages g2 with (nolock)
where g2.ownerType = 'MFR'
and g2.messageCode = 'UNRESOLVED BRAND'
and g2.createdate between '2022-12-28 00:00:00.000' and '2023-01-04 23:59:59.000' )

select
--distinct
mostRecentId.messageid as pkid, CONVERT(nvarchar,g.createDate, 101) IssueDate, g.messagecode, g.message, g.clientName mfr_name, itemCount.GTIN
from tbl_GS1_BatchMessages g with (nolock)
inner join mostRecentId
on g.messageid = mostRecentId.messageid
inner join itemCount
on g.message = itemCount.message
and g.ownerid = itemCount.ownerid
where g.ownerType = 'MFR'
and g.createdate between '2022-12-28 00:00:00.000' and '2023-01-04 23:59:59.000'
and g.messageCode = 'UNRESOLVED BRAND'
--group by mostRecentId.messageid, CONVERT(nvarchar,g.createDate, 101), g.messageCode, g.message, g.clientName
</cfquery>

<cfquery name="qmd_test" dbtype="query">
	select distinct pkid, issueDate, messagecode, message, mfr_name, count( GTIN ) issueCount
	 from qmd_test
	 group by pkId, issueDate, messageCode, message, mfr_name
</cfquery>

<cfdump var="#qmd_test#">