declare @ownerId int = ; declare @ownerType varchar(3) = ; 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; select @startMonth startMonth, @endMonth endMonth declare @cdr_recordId int = ; declare @skuId int = declare @relationshipType varchar(10) = ; declare @startMonth datetime = ; declare @endMonth datetime = ; declare @distributorList table ( cdr_recordId int ); if @relationshipType = 'MARKET' insert into @distributorList( cdr_recordId ) select cdr_recordId from tbl_DW_CRMDistributors#_tableSuffix# where territoryId in ( select territoryId from tbl_DW_CRMDistributors#_tableSuffix# with (nolock) where cdr_recordId = @cdr_recordId ) else insert into @distributorList( cdr_recordId ) select cdr_recordId from tbl_DW_CRMDistributors#_tableSuffix# where parentCdrRecordId in ( select parentCdrRecordId from tbl_DW_CRMDistributors#_tableSuffix# with (nolock) where cdr_recordId = @cdr_recordId ); with distributorSales as ( select @relationshipType as relationshipType, ds.cdr_recordId, ds.cdr_dstName, ds.buyingGroupId, case when ph.brandOwnerType = 'BYG' then ph.brandOwnerId else 0 end as skuBuyingGroupId, ds.skuId, ds.cases, ds.lbs, ds.dollars 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.cdr_recordId in ( select cdr_recordId from @distributorList ) and ds.skuId = @skuId ) , summary as ( select cdr_recordId, cdr_dstName, sum( cases ) cases, sum( lbs ) lbs, sum( dollars ) dollars from distributorSales with (nolock) group by cdr_recordId, cdr_dstName ) , ranked as ( select *, row_number() over ( order by [#_objectParams.rankBy#] desc ) as rank from summary with (nolock) ) , data as ( select * from ranked where rank <= #_objectParams.top# ) #forJSON( 'data', attributes.datastore, 'rank' )#