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; with distributorSalesRaw as ( select * from tbl_DW_DistributorSales#_tableSuffix# with (nolock) where salesPeriodType = 'MONTH' and discontinued = 'N' and coalesce( privateLabelType, '' ) != 'DST' and customerType = 'DISTRIBUTOR' and purchasingMethod in ( 'DIRECT', 'INDIRECT' ) -- and salesPeriod between '01/01/2022' and '12/01/2022' and salesPeriod between @startMonth and @endMonth and territoryId in ( ) ), distributorSales as ( select * from distributorSalesRaw where #applyFilter( attributes.datastore.filter )# ), _topSkus as ( select top #val( attributes.datastore.objectparams.topSkus )# sku + ' - ' + skuDesc as skuFull, skuId, sum( lbs ) as lbs, sum( cases ) cases, sum( dollars ) as dollars from distributorSales group by sku, skuId, skuDesc order by [#attributes.datastore.objectparams.uom#] desc ), _topSkus as ( select top #val( attributes.datastore.objectparams.topSkus )# ph.sku + ' - ' + ph.skuDesc as skuFull, ph.skuId, sum( coalesce( ds.lbs, 0.0 ) ) as lbs, sum( coalesce( ds.cases, 0.0 ) ) cases, sum( coalesce( ds.dollars, 0.0 ) ) as dollars from tbl_DW_ProductHierarchy#_tableSuffix# ph left outer join distributorSales ds on ds.skuId = ph.skuId where 1 = 1 and ph.productHierarchyPath like and ph.mfr_id = group by ph.sku, ph.skuId, ph.skuDesc order by [#attributes.datastore.objectparams.uom#] desc, skuFull ), _notBuyingRaw as ( select ds1.cdr_recordId, floor( 10191817 * sqrt( cdr_recordId * log10( cdr_recordId ))) as cdr_recordId_tk, ds1.cdr_dstName, ds1.cdr_dstCity, ds1.cdr_dstState, ts.skuId, sum( ds1.lbs ) lbs, sum( ds1.cases ) as cases, sum( ds1.dollars ) as dollars from distributorSales ds1 left outer join _topSkus ts on 1 = 1 where not exists ( select * from distributorSales ds2 where ds2.skuId = ts.skuId and ds2.cdr_recordId = ds1.cdr_recordId ) group by ds1.cdr_recordId, ds1.cdr_dstName, ds1.cdr_dstCity, ds1.cdr_dstState, ts.skuId ), _notBuyingRanked as ( select *, row_number() over( partition by skuId order by lbs desc ) as notBuying_rank from _notBuyingRaw ), _notBuying as ( select * from _notBuyingRanked where notBuying_rank <= #val( attributes.datastore.objectparams.topNotBuying )# ), _result as ( select ts.skuId, ts.skuFull, ts.lbs, ts.cases, ts.dollars, nb.cdr_recordId notBuying_cdr_recordId, nb.cdr_recordId_tk notBuying_cdr_recordId_tk, nb.cdr_dstName notBuying_cdr_dstName, nb.lbs as notBuying_lbs, nb.cases as notBuying_cases, nb.dollars as notBuying_dollars, nb.notBuying_rank, nb.cdr_dstCity notBuying_cdr_dstCity, nb.cdr_dstState notBuying_cdr_dstState from _topSkus ts left outer join _notBuying nb on nb.skuId = ts.skuId ) select * from _result order by [#attributes.datastore.objectparams.uom#] desc, [notBuying_#attributes.datastore.objectparams.uom#] desc for json auto, include_null_values