declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @cdr_recordId int = ; declare @startMonth datetime; declare @endMonth datetime; select top 1 @endMonth = fiscalStartMonth, @startMonth = dateadd( month, -11, fiscalStartMonth ) from tbl_CRM_FiscalPeriods with (nolock) where ownerId = @ownerId and ownerType = @ownerType and periodType = 'M' and percentComplete > 0 order by fiscalStartMonth desc; declare @buyingGroupId int = coalesce(( select top 1 buyingGroupId from tbl_DW_DistributorSales#_tableSuffix# with (nolock) where cdr_recordId = @cdr_recordId ), 0 ); with distributorSalesRaw as ( select ds.*, case when ph.brandOwnerType = 'BYG' then ph.brandOwnerId else 0 end as skuBuyingGroupId from tbl_DW_DistributorSales#_tableSuffix# ds with (nolock) inner join tbl_DW_ProductHierarchy#_tableSuffix# ph with (nolock) on ph.skuId = ds.skuId and ph.ownerId = ds.ownerId and ph.ownerType = ds.ownerType where ds.salesPeriodType = 'MONTH' and ds.discontinued = 'N' and coalesce( ds.privateLabelType, '' ) != 'DST' and coalesce( ds.privateLabelType, '' ) != 'OPR' and ds.customerType = 'DISTRIBUTOR' and purchasingMethod in ( 'DIRECT', 'INDIRECT' ) and ds.salesPeriod between @startMonth and @endMonth and ds.territoryId = ( select cdr_territoryId from tbl_CDR_Distributors cdr with (nolock) where cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType and cdr.cdr_recordId = @cdr_recordId ) and ( ds.cdr_recordId = @cdr_recordId or ds.cdr_recordId in ( select scdr.cdr_recordId from tbl_CDR_Distributors cdr with (nolock) inner join tbl_DST_Distributors dst with (nolock) on dst.dstId = cdr.fsl_dstId inner join tbl_DST_Distributors sdst with (nolock) on sdst.dstParentCo = nullif( dst.dstParentCo, 0 ) inner join tbl_CDR_Distributors scdr on scdr.ownerId = cdr.ownerId and scdr.fsl_tablecode = cdr.fsl_tablecode and scdr.fsl_dstId = sdst.dstId where cdr.ownerId = @ownerId and cdr.fsl_tablecode = @ownerType and cdr.cdr_recordId = @cdr_recordId ) ) and 1 = 2 and territoryId in ( ) ), distributorSales as ( select * from distributorSalesRaw with (nolock) where #applyFilter( attributes.datastore.filter )# ), distributorSalesSummary as ( select cdr_recordId, buyingGroupId, skuBuyingGroupId, cdr_dstName, sku + ' - ' + skuDesc as skuFull, skuId, MFRAbbrev_Name, sum( lbs ) as lbs, sum( cases ) cases, sum( dollars ) as dollars from distributorSales with (nolock) group by cdr_recordId, cdr_dstName, sku, skuId, skuDesc, buyingGroupId, skuBuyingGroupId, MFRAbbrev_Name ), buyingSkus as ( select distinct skuId from distributorSalesSummary with (nolock) where cdr_recordId = @cdr_recordId and ( coalesce( lbs, 0 ) > 0 or coalesce( cases, 0 ) > 0 ) ), result as ( select top #val( attributes.datastore.objectparams.top )#100000 v.MFRAbbrev_Name, v.skuFull, v.skuId, sum( v.lbs ) as lbs, sum( v.cases ) cases, sum( v.dollars ) as dollars from distributorSalesSummary as v with (nolock) where cdr_recordId != @cdr_recordId and skuId not in ( select skuId from buyingSkus with (nolock) ) and ( v.skuBuyingGroupId = 0 or coalesce( v.skuBuyingGroupId, 0 ) = @buyingGroupId ) group by skuFull, skuId, MFRAbbrev_Name order by [#attributes.datastore.objectparams.uom#] desc ) select * from result order by [#attributes.datastore.objectparams.uom#] desc for json auto, include_null_values