SELECT '_' AS _ ,effectiveRepId ,MAX(effectiveRepFirstName + ' ' + effectiveRepLastName) AS effectiveSalesRepFullName ,territoryId ,MAX(territoryPath) AS territoryPath ,MAX(territoryName) AS territoryName ,regionName ,MAX(CASE WHEN regionName IS NULL THEN territoryPath ELSE LEFT( territoryPath, LEN(territoryPath) - LEN('/') - LEN(territoryName) ) END ) AS regionPath ,mfr_id ,MAX(mfr_name) AS mfr_name ,SUM( #_uom# ) ytdOpportunityVolume ,SUM( cases ) cases ,SUM( lbs ) lbs ,SUM( dollars ) dollars ,SUM( equivUnits ) equivUnits ,COUNT(*) AS opportunities FROM tbl_DW_Opportunities#_tableSuffix# WITH (nolock) WHERE estOrderDate >= AND estOrderDAte <= /*{NEW,INCREMENTAL} and SOLD*/ AND statusCode = 'C' AND typeCode IN ('NEW','INC') and partnerTerritoryId in ( ) and effectiveRepId in ( ) and ( 1 = 2 or territoryPath like ) and staffOpportunity = and effectiveRepId in ( ) and mfr_id in ( ) and ( 1 = 2 or productHierarchyPath like ) and budgetCategoryId in ( ) and (1=2 or clientSegPath like ) clientSegPath like and effectiveRepOwnerType = GROUP BY #_viewBy# /*the valid _viewBy's are hard enforced in the SELECT statement cfif's*/ ORDER BY ytdOpportunityVolume DESC ,effectiveSalesRepFullName ASC ,territoryName ASC ,regionName ASC ,mfr_name ASC FOR JSON AUTO