select distinct o.territoryId
from tbl_IMPORT_Operators i with (nolock)
inner join tbl_OPR_ClientOperators o with (nolock)
on o.operatorId = i.fseOperatorId
and o.ownerId = i.ownerId
and o.fsltablecode = i.ownerType
where i.ownerId =
and i.ownerType =
and i.eai_batchId =
and not exists ( select * from tbl_GEO_Locations l with (nolock)
where l.ownerId = i.ownerId
and l.ownerType = i.ownerType
and l.partnerId = i.fseOperatorId
and l.partnerType = 'OPR' )
select t.territoryId
from tbl_TER_Territories t with (nolock)
inner join tbl_TER_TerritoryLevels l with (nolock)
on l.levelId = t.levelId
and l.ownerId = t.ownerId
and l.fsl_tablecode = t.fsl_tablecode
and l.brokerLevel = 1
where t.ownerId =
and t.fsl_tablecode =
Data Science Toolkit no longer suppored
SELECT o.ownerId, o.fsltablecode as ownerType, 'OPR' as partnerType, o.operatorId as partnerId, o.companyName partnerName,
o.address, o.address2, o.city, o.state, o.zipcode,
g.source, '' result
FROM tbl_OPR_ClientOperators o with (nolock)
LEFT JOIN tbl_GEO_locations g with (nolock)
on g.partnerId = o.operatorId
and g.partnerType = 'OPR'
and g.ownerId = o.ownerId
and g.ownerType = o.fsltablecode
WHERE o.ownerId =
AND o.fsltablecode =
and nullif(o.address, '') is not null
and nullif(o.city, '' ) is not null
and nullif(o.state, '' ) is not null
and exists ( select * from tbl_LST_PartnerListItems pli with (nolock)
where pli.listId in ( )
and pli.partnerId = o.operatorId
)
and o.territoryId = ( )
and exists ( select * from tbl_IMPORT_Operators eai with (nolock)
where eai.ownerId = o.ownerId
and eai.ownerType = o.fsltablecode
and eai.eai_batchId in ( )
and eai.fseOperatorId = o.operatorId )
and ( nullif(g.source,'') is null or g.source = 'Fail' )
order by partnerName
select top 2000 'OPR' as partnerType, o.ownerId, o.fsltablecode as ownerType, o.address, o.address2, o.city, o.state, o.zipcode, o.operatorId as partnerId, o.companyName as partnerName, '' result, g.source
from tbl_OPR_ClientOperators o with (nolock)
inner join tbl_GEO_locations g with (nolock)
on g.partnerId = o.operatorId
and g.ownerId = o.ownerId
and g.ownerType = o.fsltablecode
and g.partnerType = 'OPR'
where g.source != 'GOOG'
and g.source != 'google'
and g.source != 'Fail'
order by partnerName
Processed #qmd_partners.recordCount# records @ #lsnumberformat( _avgTimeEach, '0.0' )# seconds each
select * from qmd_partners where result = 'Fail'
#_cfqueryResult.sql#
SELECT top #int(arguments.maxRows)# o.ownerId, o.fsltablecode as ownerType, 'OPR' as partnerType, o.operatorId as partnerId, o.companyName partnerName,
o.address, o.address2, o.city, o.state, o.zipcode,
'' source, '' result
FROM tbl_OPR_ClientOperators o with (nolock)
inner join ( select distinct m.ownerId, m.fsl_tablecode
from tbl_FSPro_members m with (nolock)
where datediff( day, lastLoginDate, getDate() ) <= 2
union
select 0 as ownerId, 'CHO' as fsl_tablecode
) m
on ( m.ownerId = o.ownerId
and m.fsl_tablecode = o.fsltablecode )
or ( o.fsltablecode = 'CHO' and m.fsl_tablecode = 'CHO' )
WHERE nullif(o.address, '') is not null
and nullif(o.city, '' ) is not null
and nullif(o.state, '' ) is not null
and o.crmActive = 'Y'
and not exists ( select * from tbl_GEO_Locations l with (nolock)
where l.ownerId = o.ownerId
and l.ownerType = o.fsltablecode
and l.partnerId = o.operatorId
and l.partnerType = 'OPR' )
order by o.operatorId
SELECT top #int(arguments.maxRows)# 0 as ownerId, '*' as ownerType, 'DST' as partnerType, dst.dstId as partnerId, dst.dstName partnerName,
dst.dstAddr1 as address, dst.dstAddr2 as address2, dst.dstCity as city, dst.dstState as state, dst.dstZip as zipcode,
'' source, '' result
FROM tbl_DST_Distributors dst with (nolock)
WHERE nullif(dst.dstAddr1, '') is not null
and nullif(dst.dstCity, '' ) is not null
and nullif(dst.dstState, '' ) is not null
and not exists ( select * from tbl_GEO_Locations l with (nolock)
where l.ownerId = 0
and l.ownerType = '*'
and l.partnerId = dst.dstId
and l.partnerType = 'DST' )
order by dst.dstId
SELECT top #int(arguments.maxRows)# 0 as ownerId, '*' as ownerType, 'CHO' as partnerType, cho.cho_Id as partnerId, cho.cho_Name partnerName,
cho.cho_address as address, cho.cho_address2 as address2, cho.cho_city as city, cho.cho_state as state, cho.cho_ZipCode as zipcode,
'' source, '' result
FROM tbl_CHO_Operators cho with (nolock)
WHERE nullif(cho.cho_address, '') is not null
and nullif(cho.cho_City, '' ) is not null
and nullif(cho.cho_State, '' ) is not null
and not exists ( select * from tbl_GEO_Locations l with (nolock)
where l.ownerId = 0
and l.ownerType = '*'
and l.partnerId = cho.cho_id
and l.partnerType = 'CHO' )
order by cho.cho_id