delete
from tbl_CRM_AffiliationHistory
where ( year = and month <= )
or year <
select a.affiliationId, a.ownerId, a.ownerType, a.orgId, a.orgType, a.partnerId, a.partnerType
from tbl_OPR_CLientOperators cho with (nolock)
inner join tbl_OPR_ClientOperators member with (nolock)
on member.fsl_choId = cho.fsl_choId
inner join tbl_CRM_Affiliations a with (nolock)
on a.ownerId = member.ownerId
and a.ownerType = member.fsltablecode
and a.affiliationStatus = 'A'
and a.partnerId = member.operatorId
and a.partnerType = 'OPR'
inner join tbl_OPR_ClientOperators memberGroup with (nolock)
on memberGroup.operatorId = a.orgId
and memberGroup.ownerId = a.ownerId
and memberGroup.fsltablecode = a.ownerType
and memberGroup.fsl_choId = cho.ownerId
where cho.fsltablecode = 'CHO'
and cho.fsl_choId > 0
and cho.crmActive = 'N'
and cho.oprCompanyType = '0'
update tbl_CRM_Affiliations
set affiliationStatus = 'I', deaffiliationDate =
where affiliationId =
and ownerId =
and ownerType =
select #_year# as year, #_month# as month, a.ownerId, a.ownerType, a.orgId, rtrim( ltrim( a.orgType )) as orgType, a.partnerId, a.partnerType, a.partnerAffiliateNbr, rtrim( ltrim( a.affiliationType )) as affiliationType, a.affiliationDate, a.affiliationStatus, a.membershipQualificationId, a.sourceId, a.sourceType
from tbl_CRM_Affiliations a with (nolock)
left outer join (
select ownerId, ownerType, orgId, orgType, partnerId, partnerType, count(*) dupCount
from tbl_CRM_Affiliations with (nolock)
group by ownerId, ownerType, orgId, orgType, partnerId, partnerType
having count(*) > 1
) dups
on dups.ownerId = a.ownerId
and dups.ownerType = a.ownerType
and dups.orgId = a.orgId
and dups.orgType = a.orgType
and dups.partnerId = a.partnerId
and dups.partnerType = a.partnerType
where a.ownerType in ( 'MFR', 'BRO' )
and a.partnerType = 'OPR'
and a.orgType = 'OPR'
and exists ( select * from tbl_OPR_ClientOperators opr with (nolock) where opr.ownerId = a.ownerId and opr.fsltablecode = a.ownertype and opr.operatorId = a.partnerId )
and dups.partnerId is null
and a.ownerId > 0
select year, month, ownerId, ownerType, orgId, orgType, partnerId, partnerType, partnerAffiliateNbr, rtrim( ltrim( affiliationType )) as affiliationType, affiliationDate, affiliationStatus, membershipQualificationId, sourceId, sourceType
from tbl_CRM_AffiliationHistory
where month =
and year =
insert into tbl_CRM_AffiliationHistory ( year, month, ownerId, ownerType, orgId, orgType, partnerId, partnerType, partnerAffiliateNbr, affiliationType, affiliationDate, affiliationStatus, membershipQualificationId, sourceId, sourceType )
#PreserveSingleQuotes( _currentSQL )#
#PreserveSingleQuotes( _currentSQL )#
insert into tbl_CRM_AffiliationHistory ( year, month, ownerId, ownerType, orgId, orgType, partnerId, partnerType, partnerAffiliateNbr, affiliationType, affiliationDate, affiliationStatus, membershipQualificationId, sourceId, sourceType, historyDate )
values (
, ,
, ,
, ,
, ,
, ,,
, ,
, ,
)
update tbl_CRM_AffiliationHistory
set partnerAffiliateNbr = ,
affiliationType = ,
affiliationStatus = ,
affiliationDate = ,
sourceId = ,
sourceType = ,
membershipQualificationId = ,
historyDate =
where ownerId =
and ownerType =
and partnerId =
and partnerType =
and orgId =
and orgType =
and month =
and year =
#lsnumberformat( _updatesProcessed )# Updates Processed