with x as ( select a.ownerId, a.ownerType, a.partnerId, mq.orgId, count(*) cc from tbl_CRM_Affiliations a inner join tbl_CRM_MembershipQualifications mq on mq.membershipQualificationId = a.sourceId inner join tbl_OPR_ClientOperators opr on opr.operatorId = mq.partnerId and opr.ownerId = mq.ownerId and opr.fsltablecode = mq.ownertype where 1 = 1 and a.partnerType = 'OPR' and a.sourceType = 'AMQ' and a.affiliationStatus = 'A' group by a.ownerId, a.ownerType, a.partnerId, mq.orgId having count(*) > 1 ) select go.companyName as groupOwner, go.operatorId groupOwnerOperatorId, sg.operatorId subGroupOperatorId, sg.companyName as subgroup, m.operatorId, m.companyName memberName, m.address, m.city, m.state, m.zipCode, a.partnerAffiliateNbr from tbl_CRM_Affiliations a inner join x on x.partnerId = a.partnerId and x.ownerId = a.ownerId and x.ownerType = a.ownerType inner join tbl_CRM_MembershipQualifications mq on mq.membershipQualificationId = a.sourceId and mq.ownerId = a.ownerId and mq.ownerType = a.ownerType inner join tbl_OPR_ClientOperators sg on sg.operatorId = mq.partnerId and sg.ownerId = mq.ownerId and sg.fsltablecode = mq.ownerType inner join tbl_OPR_ClientOperators go on go.operatorId = mq.orgId and go.ownerId = mq.ownerId and go.fsltablecode = mq.ownerType inner join tbl_OPR_ClientOperators m on m.operatorId = a.partnerId and m.ownerId = a.ownerId and m.fsltablecode = a.ownerType where a.partnerType = 'OPR' and a.ownerId = and a.ownerType = order by groupOwner, memberName, subGroup

Member	Groups
#htmleditformat( groupOwner )#
Member	Groups
#htmleditformat( memberName )# #htmleditformat( address )# #htmleditformat( city )# #htmleditformat( State )# #htmleditformat( zipcode )#	#htmleditformat( subgroup )#

select a.affiliationId, goo.companyName as MemberGroup, a2.partnerAffiliateNbr as MemberGroupMemberNbr, a2.affiliationDate MemberGroupMemberDate, a2.affiliationStatus MemberGroupMemberStatus, mq.membershipQualification, opr.companyName as subGroup, a.affiliationDate SubGroupDate, a.affiliationStatus SubGroupStatus, a.deaffiliationDate subGroupEndDate from tbl_CRM_Affiliations a inner join tbl_CRM_MembershipQualifications mq on mq.membershipQualificationId = a.sourceId and 'AMQ' = a.sourceType and mq.ownerId = a.ownerId and mq.ownerType = a.ownerType and mq.orgId = inner join tbl_OPR_ClientOperators goo on goo.operatorId = mq.orgId and goo.ownerId = mq.ownerId and goo.fsltablecode = mq.ownerType inner join tbl_OPR_ClientOperators opr on opr.operatorId = mq.partnerId and opr.ownerId = mq.ownerId and opr.operatorId = mq.partnerId left outer join tbl_CRM_Affiliations a2 on a2.ownerId = a.ownerId and a2.ownerType = a.ownerType and a2.partnerId = a.partnerId and a2.partnerType = a.partnerType and a2.orgId = mq.orgId and a2.orgType = mq.orgType where a.ownerId = and a.ownerType = and a.partnerId = and a.partnerType = 'OPR' and a.affiliationStatus = 'A' order by a.affiliationDate desc update tbl_CRM_Affiliations set deaffiliationDate = , affiliationStatus = 'I' where affiliationId = select a.affiliationId, goo.companyName as MemberGroup, a2.partnerAffiliateNbr as MemberGroupMemberNbr, a2.affiliationDate MemberGroupMemberDate, a2.affiliationStatus MemberGroupMemberStatus, mq.membershipQualification, opr.companyName as subGroup, a.affiliationDate SubGroupDate, a.affiliationStatus SubGroupStatus, a.deaffiliationDate subGroupEndDate from tbl_CRM_Affiliations a inner join tbl_CRM_MembershipQualifications mq on mq.membershipQualificationId = a.sourceId and 'AMQ' = a.sourceType and mq.ownerId = a.ownerId and mq.ownerType = a.ownerType and mq.orgId = inner join tbl_OPR_ClientOperators goo on goo.operatorId = mq.orgId and goo.ownerId = mq.ownerId and goo.fsltablecode = mq.ownerType inner join tbl_OPR_ClientOperators opr on opr.operatorId = mq.partnerId and opr.ownerId = mq.ownerId and opr.operatorId = mq.partnerId left outer join tbl_CRM_Affiliations a2 on a2.ownerId = a.ownerId and a2.ownerType = a.ownerType and a2.partnerId = a.partnerId and a2.partnerType = a.partnerType and a2.orgId = mq.orgId and a2.orgType = mq.orgType where a.ownerId = and a.ownerType = and a.partnerId = and a.partnerType = 'OPR' -- and a.affiliationStatus = 'A' order by a.affiliationDate desc