SELECT l.Rank, l.BrokerLevel, t.parentTerritoryId, t.territoryId, t.name, coalesce( mbr.lastName, mbr.lastName, coalesce( sma.brokerName, sma.brokerName, 'Unassigned')) managerName, sum( case when (opr.mfr_bsr_id <> 0 or sma.brokername is null) then 0 else 1 end ) as unassignedCount, count(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 ( SELECT DISTINCT bol.mfr_id as ownerId, 'MFR' as ownerType, bol.territoryId, b.broker_name as brokerName FROM tbl_SMA_BrokerOfficeLink bol INNER JOIN tblBrokers b ON b.broker_id = bol.brokerid WHERE bol.mfr_id = ) as sma ON sma.territoryId = opr.territoryId and sma.ownerId = opr.ownerid and sma.ownerType = opr.FSLTableCode 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, sma.brokerName ORDER BY l.rank ASC, t.name ASC SELECT l.Rank, l.BrokerLevel, t.parentTerritoryId, t.territoryId, t.name, coalesce( mbr.lastName, mbr.lastName, coalesce( sma.brokerName, sma.brokerName, 'Unassigned')) managerName, sum( case when (opr.mfr_bsr_id <> 0 or sma.brokername is null) then 0 else 1 end ) as unassignedCount, count(*) operatorCount FROM tbl_OPR_ClientOperators opr INNER JOIN tbl_TER_Territories t ON t.territoryId = opr.territoryId AND t.ownerId = opr.ownerId AND t.FSL_TableCode = opr.FSLTableCode AND t.territoryId IN ( ) 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 INNER JOIN tbl_CRM_Participants p ON p.ownerId = opr.ownerId AND p.ownerType = opr.FSLTableCode AND p.operatorId = opr.operatorId AND p.marketingActivityId = LEFT OUTER JOIN ( SELECT DISTINCT bol.mfr_id as ownerId, 'MFR' as ownerType, bol.territoryId, b.broker_name as brokerName FROM tbl_SMA_BrokerOfficeLink bol INNER JOIN tblBrokers b ON b.broker_id = bol.brokerid WHERE bol.mfr_id = ) as sma ON sma.territoryId = opr.territoryId and sma.ownerId = opr.ownerid and sma.ownerType = opr.FSLTableCode 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 = GROUP BY l.rank, l.brokerLevel, t.parentTerritoryId, t.territoryId, t.name, mbr.lastname, sma.brokerName ORDER BY l.rank ASC, t.name ASC