with activeUnits as ( select u.ownerId, u.fsltablecode, u.operatorId unitOperatorId, u.companyName unitName, org.companyName memberGroup, org.operatorId memberGroupOperatorId, u.oprParentCo parentOperatorId from tbl_OPR_ClientOperators u with (nolock) inner join tbl_CRM_Affiliations ua with (nolock) on ua.ownerId = u.ownerId and ua.ownerType = u.fsltablecode and ua.partnerId = u.operatorId and ua.partnerType = 'OPR' and ua.affiliationStatus = 'A' and ua.orgType = 'OPR' inner join tbl_OPR_ClientOperators org with (nolock) on org.ownerId = ua.ownerId and org.fsltablecode = ua.ownerType and org.operatorId = ua.orgId where u.oprCompanyType = '0' and u.oprParentCo > 0 ), activeParents as ( select p.ownerId, p.fsltablecode, p.operatorId parentOperatorId, p.companyName parentName, org.companyName memberGroup, org.operatorId memberGroupOperatorId, pa.affiliationId from tbl_OPR_ClientOperators p with (nolock) inner join tbl_CRM_Affiliations pa with (nolock) on pa.ownerId = p.ownerId and pa.ownerType = p.fsltablecode and pa.partnerId = p.operatorId and pa.partnerType = 'OPR' and pa.affiliationStatus = 'A' and pa.orgType = 'OPR' inner join tbl_OPR_ClientOperators org with (nolock) on org.ownerId = pa.ownerId and org.fsltablecode =pa.ownerType and org.operatorId = pa.orgId and org.fsl_choId > 0 where p.oprCompanyType = '1' and p.fsl_choId > 0 and p.fsltablecode in ( 'MFR', 'DST', 'CHO', 'BRO' ) and exists ( select * from tbl_OPR_CLientOperators opru where opru.ownerId = p.ownerId and opru.fsltablecode = p.fsltablecode and opru.oprParentCo = p.operatorId ) ), toCleanup as ( select ap.ownerId, ap.fsltablecode, case when ap.fsltablecode = 'MFR' then ( select mfr_name from tblManufacturers with (nolock ) where mfr_id = ap.ownerId ) when ap.fsltablecode = 'CHO' then ( select cho_name from tbl_CHO_Operators with (nolock ) where cho_id = ap.ownerId ) when ap.fsltablecode = 'DST' then ( select dstName from tbl_DST_Distributors with (nolock ) where dstId = ap.ownerId ) when ap.fsltablecode = 'BRO' then ( select broker_name from tblBrokers with (nolock ) where broker_id = ap.ownerId ) else cast( null as varchar ) end libraryAccount, ap.parentName, ap.parentOperatorId, ap.affiliationId, ap.memberGroupOperatorId, ap.memberGroup, count( distinct au.unitOperatorId ) activeUnits from activeParents ap left outer join activeUnits au on au.ownerId = ap.ownerId and au.fsltablecode = ap.fsltablecode and au.parentOperatorId = ap.parentOperatorId and au.memberGroupOperatorId = ap.memberGroupOperatorId group by ap.ownerId, ap.fsltablecode, ap.parentName, ap.parentOperatorId, ap.affiliationId, ap.memberGroupOperatorId, ap.memberGroup ) select libraryAccount, parentName, memberGroup, activeUnits, fsltablecode, ownerId, parentOperatorId, memberGroupOperatorId, affiliationId from toCleanup where libraryAccount is not null order by libraryAccount, parentName, memberGroup select * from qmd_parentMembershipsToDeactivate where activeUnits = 0 order by libraryAccount, parentName, memberGroup

#lsnumberformat( qmd_parentMembershipsToDeactivate.recordCount )# to deactivate

 update tbl_CRM_Affiliations set affiliationStatus = 'I', deaffiliationDate = , recordSource = ltrim( coalesce( recordSource, '' ) + ' X' ) where ownerId = and ownerType = and affiliationId =

#lsnumberformat( _deactivationCount )# records deactivated