declare @ownerId int = ; declare @ownerType varchar(3) = ; with pipeline as ( select top #_topCount# o.mfr_id, o.mfr_name, sum( o.cases ) cases, sum( o.lbs ) lbs, sum( o.dollars ) dollars, sum( o.commissionAmt ) commission, count(*) opportunities from tbl_DW_Opportunities#_tableSuffix# o with (nolock) where o.statusCode= 'A' and o.pastDue = 'N' and o.ownerId = @ownerId and o.ownerType = @ownerType and o.partnerTerritoryId in ( ) -- all territory visibility mode and o.effectiveRepId in ( ) and ( 1 = 2 or o.territoryPath like ) and o.budgetCategoryId in ( ) and o.effectiveRepId in ( ) and o.mfr_id in ( ) and o.typeCOde in ( ) group by o.mfr_name, o.mfr_id order by #_uom# desc ) select * from pipeline order by #_uom# desc for json auto, include_null_values