select distinct ter.territoryId
from tbl_TER_Territories ter with (nolock)
inner join tbl_TER_TeamMembers tt with (nolock)
on tt.territoryId = ter.territoryId
and tt.ownerId = ter.ownerId
and tt.ownertype = ter.fsl_tablecode
where ter.ownerId =
and ter.fsl_tablecode =
and tt.teamMemberUserId =
select s.skuId
from tbl_BCRM_UserItemFavorites uif with (nolock)
inner join tbl_PRD_Skus s with (nolock)
on s.ownerid = uif.ownerId
and s.fsl_tablecode = uif.ownerType
and s.skuId = uif.skuId
where uif.ownerId =
and uif.ownerType =
and uif.fspro_userId =
and s.discontinued <>
--->
SELECT interactionAttributeId
FROM tbl_CRM_InteractionAttributes with (nolock)
where ownerType =
and ownerId =
and name like '%cold%'
SELECT interactionAttributeId
FROM tbl_CRM_InteractionAttributes with (nolock)
where ownerType =
and ownerId =
and name like '%cupping%'
with partners as ( select opr.operatorId as partnerId, 'OPR' partnerType, opr.ownerId as ownerId, opr.fsltablecode as ownerType, opr.classificationId as classificationId
from tbl_OPR_CLientOperators opr with (nolock)
where opr.ownerId =
and opr.fsltablecode =
and opr.crmactive = 'Y'
and opr.territoryId in ( )
union
select cdr.cdr_recordId as partnerId, 'CDR' partnerType, cdr.ownerId as ownerId, cdr.fsl_tablecode as ownerType, 0 as classificationId
from tbl_CDR_Distributors cdr with (nolock)
where cdr.ownerId =
and cdr.fsl_tablecode =
and cdr.crmActive = 'Y'
)
select p.partnerId, p.partnerType, i.interactionDate, i.interactionPurposeId, ia.interactionAttributeId, ia.name, cls.abbreviation
from tbl_CRM_Interactions i with (nolock)
inner join partners p with (nolock)
on i.partnerId = p.partnerId
and i.ownerId = p.ownerId
and i.ownerType = p.ownerType
left outer join tbl_OPR_Classifications cls with (nolock)
on cls.classificationId = p.classificationId
and cls.ownerId = p.ownerId
and cls.ownerType = p.ownerType
and p.partnerType = 'OPR'
left outer join tbl_CRM_InteractionAttributes ia with (nolock)
on ia.interactionAttributeId = i.interactionAttributeId
and ia.ownerId = i.ownerId
and ia.ownerType = i.ownerType
where i.ownerId =
and i.ownerType =
And i.dispositionUserId =
and i.interactionDate between and
and i.disposition <> '?'
with partners as ( select opr.operatorId as partnerId, 'OPR' partnerType, opr.ownerId as ownerId, opr.fsltablecode as ownerType, opr.classificationId as classificationId
from tbl_OPR_CLientOperators opr with (nolock)
where opr.ownerId =
and opr.fsltablecode =
and opr.crmactive = 'Y'
and opr.territoryId in ( )
union
select cdr.cdr_recordId as partnerId, 'CDR' partnerType, cdr.ownerId as ownerId, cdr.fsl_tablecode as ownerType, 0 as classificationId
from tbl_CDR_Distributors cdr with (nolock)
where cdr.ownerId =
and cdr.fsl_tablecode =
and cdr.crmActive = 'Y'
)
select sku.skuId,prdForSku.bgtCatId, i.disposition, p.partnerId, p.partnerType, i.interactionDate, i.interactionPurposeId
from tbl_CRM_Interactions i with (nolock)
inner join partners p with (nolock)
on i.partnerId = p.partnerId
and i.ownerId = p.ownerId
and i.ownerType = p.ownerType
inner join tbl_CRM_InteractionSaleDetails isd with (nolock)
on isd.interactionId = i.interactionId
and isd.ownerId = i.ownerId
and isd.ownerType = i.ownerType
left outer join tbl_PRD_Skus sku with (nolock)
on sku.skuId = isd.saleSubjectId
and sku.ownerId = isd.ownerId
and sku.fsl_TableCode = isd.ownerType
and isd.saleSubjectType = 'SKU'
inner join tbl_PRD_Products prdForSku with (nolock)
on prdForSku.prodId = sku.prodId
and prdForSku.ownerId = sku.ownerId
and prdForSku.fsl_tablecode = sku.fsl_tableCode
left outer join tbl_PRD_Products prd with (nolock)
on prd.prodId = isd.saleSubjectId
and prd.ownerId = isd.ownerId
and prd.fsl_TableCode = isd.ownerType
and isd.saleSubjectType = 'PRD'
where i.ownerId =
and i.ownerType =
and i.partnerType = 'OPR'
and i.dispositionUserId =
and i.interactionDate between and
--and isd.disposition in ('FU','NS','STD','NA')
--and i.disposition <> '?'
and case when isd.saleSubjectType = 'SKU' then
case when sku.bgtCatOverride = 'Y' then sku.bgtCatId else prdForSku.bgtCatID end
else
case when isd.saleSubjectType = 'PRD' then prd.bgtCatID end
end in ()
select partnerId, interactionDate, partnerType, interactionAttributeId, abbreviation
from qmd_salesCalls
where interactionDate between and
select partnerId, interactionDate
from qmd_salesCalls
WHERE abbreviation = 'FRET'
and partnerType = 'OPR'
select * from qmd_salesCalls
where interactionAttributeId =
select * from qmd_coldCalls
where interactionDate between and
select partnerId, interactionDate, partnerType
from qmd_salesCalls
where interactionAttributeId =
select * from qmd_salesCallsLastMonth
where interactionAttributeId =
with partners as ( select opr.operatorId as partnerId, 'OPR' partnerType, opr.ownerId as ownerId, opr.fsltablecode as ownerType, opr.companyName as partnerName, opr.classificationId as classificationId
from tbl_OPR_CLientOperators opr with (nolock)
where opr.ownerId =
and opr.fsltablecode =
and opr.crmactive = 'Y'
and opr.territoryId in ( )
union
select cdr.cdr_recordId as partnerId, 'CDR' partnerType, cdr.ownerId as ownerId, cdr.fsl_tablecode as ownerType, cdr.cdr_dstname as partnerName, 0 as classificationId
from tbl_CDR_Distributors cdr with (nolock)
where cdr.ownerId =
and cdr.fsl_tablecode =
and cdr.crmActive = 'Y'
)
select p.partnerId,p.partnerType, p.partnerName,
case when sku.bgtCatOverride = 'Y' then sku.bgtCatId else prd.bgtCatID end as bgtCategoryId,spl.typeCode,
sum(spl.caseAmt) as annualCaseAmt
from partners p with (nolock)
inner join tbl_SPL_Opportunities spl with (nolock)
on spl.partnerId = p.partnerId
and spl.ownerId = p.ownerId
and spl.ownerType = p.ownerType
inner join tbl_SPL_Stages stg with (nolock)
on stg.stageId = spl.stageId
and stg.ownerId = spl.ownerId
and stg.ownerType = spl.ownerType
and stg.statusCode = 'C'
inner join tbl_spl_opportunitySubjectDetails d with (nolock)
on d.opportunityId = spl.opportunityId
and d.ownerId = spl.ownerId
and d.ownerType = spl.ownerType
and d.subjectDetailType = 'SKU'
inner join tbl_PRD_Skus sku with (nolock)
on sku.skuId = d.subjectDetailId
and sku.ownerId = d.ownerId
and sku.fsl_tablecode = d.ownerType
inner join tbl_PRD_Products prd with (nolock)
on prd.prodId = sku.prodId
and prd.ownerId = sku.ownerId
and prd.fsl_TableCode = sku.fsl_tableCode
and prd.inactive = 0
and prd.crmActive = 'Y'
left outer join tbl_OPR_Classifications cls with (nolock)
on cls.classificationId = p.classificationId
and cls.ownerId = p.ownerId
and cls.ownerType = p.ownerType
and p.partnerType = 'OPR'
where spl.ownerId =
and spl.ownerType =
and spl.salesPersonUserId =
and spl.typeCode in ('NEW','INC')
and case when sku.bgtCatOverride = 'Y' then sku.bgtCatId else prd.bgtCatID end in ()
and spl.dateClosed between and
group by p.partnerId, p.partnerType, p.partnerName, case when sku.bgtCatOverride = 'Y' then sku.bgtCatId else prd.bgtCatID end, spl.typeCode
select BgtCategoryId from qmd_beverageBudgetCategories
where bgtCategoryShortDesc = 'BR R&G'
select BgtCategoryId from qmd_beverageBudgetCategories
where bgtCategoryShortDesc = 'LIQ'
select partnerId,partnerType, partnerName
from qmd_soldOpportunities
where bgtCategoryId =
and annualCaseAmt >= 120
select partnerId,partnerType,partnerName
from qmd_soldOpportunities
where bgtCategoryId =
and annualCaseAmt >= 120
select distinct operatorId
from qmd_soldOpportunitiesLastMonth
where bgtCategoryId =
select distinct operatorId
from qmd_soldOpportunitiesLastMonth
where bgtCategoryId =
--->