with topOpps as ( select top #_topCount# partnerId, partnerType, partnerIdTk, partnerName, sum( cases ) cases, sum( lbs ) lbs, sum( dollars ) dollars, sum( equivUnits) equivUnits from tbl_DW_Opportunities#_tableSuffix# o with (nolock) where statusCode= 'A' and partnerType in ( ) and partnerType in ( 'OPR', 'CDR' ) and ownerId = and ownerType = and partnerTerritoryId in ( ) -- all territory visibility mode and effectiveRepId in ( ) and ( 1 = 2 or territoryPath like ) and budgetCategoryId in ( ) and effectiveRepId in ( ) and mfr_id in ( ) and typeCOde in ( ) and (1=2 or o.clientSegPath like ) o.clientSegPath like and o.effectiveRepOwnerType = and ( 1 = 2 or o.productSetTags like ) group by partnerId, partnerIdTk, partnerType, partnerName order by #_uom# desc ) select * from topOpps order by #_uom# desc for json auto, include_null_values