SELECT l.Rank, l.BrokerLevel, t.parentTerritoryId, t.territoryId, t.name, case when l.brokerLevel = 1 then coalesce( b.broker_Name, b.broker_Name, 'Unassigned') else coalesce( mbr.lastName, mbr.lastName, 'Unassigned') end managerName, 0 as unassignedCount, count(distinct opr.operatorID) operatorCount FROM tbl_TER_Territories t INNER JOIN tbl_TER_TerritoryLevels l ON l.levelId = t.levelid AND l.ownerId = t.ownerId AND l.FSL_TableCode = t.FSL_TableCode LEFT OUTER JOIN tbl_FSPro_Members mbr ON mbr.ownerId = t.ownerId AND mbr.FSL_TableCode = t.FSL_TableCode AND mbr.FSPro_UserId = t.primaryManager LEFT OUTER JOIN ( SELECT opr.ownerID, opr.FSLTableCode, opr.operatorID, opr.oprcompanyType, opr.independentYN, opr.distrib1, opr.territoryID, opr.oprsegment, opr.mfr_bsr_id FROM tbl_OPR_ClientOperators opr INNER JOIN tbl_CRM_Participants p ON p.ownerId = opr.ownerId AND p.ownerType = opr.FSLTableCode AND p.operatorId = opr.operatorId AND p.marketingActivityId = WHERE opr.ownerID = AND opr.FSLTableCode = AND opr.oprcompanytype IN (1,2) AND opr.independentYN = 0 AND opr.oprcompanytype IN (0) AND opr.independentYN = 0 AND opr.oprcompanytype IN (0,1,2) AND opr.independentYN = 0 AND (opr.oprcompanytype IN (1,2) OR opr.independentYN = 1) AND (opr.oprcompanytype IN (0) OR opr.independentYN = 1) AND opr.independentYN = 1 AND opr.Distrib1 = AND opr.OPRsegment = ) opr ON opr.territoryId = t.territoryId AND opr.ownerId = t.ownerId AND opr.FSLTableCode = t.FSL_TableCode LEFT OUTER JOIN tbl_SMA_BrokerOfficeLink sma ON sma.territoryid = opr.territoryid AND sma.mfr_id = opr.ownerID LEFT OUTER JOIN tblBrokers b ON b.broker_id = sma.brokerid WHERE t.OwnerID = AND t.FSL_TableCode = AND t.territoryId IN ( ) GROUP BY l.rank, l.brokerLevel, t.parentTerritoryId, t.territoryId, t.name, mbr.lastname, b.broker_Name ORDER BY l.rank ASC, t.name ASC