SELECT cdr.cdr_dstcode, cdr.fsl_tablecode, cdr.ownerid, cdr.cdr_recordid, cdr.cdr_dstname, cdr.cdr_dstcity, cdr.cdr_dststate, 0 operatorCount, cdr.focusAccount, cdr.cdr_priority, cdr.classificationId, cdr.mfr_bsr_id, cls.name, dstpr.dstparentco as parentCrdRecordId, dstpr.dstcompanytype, case when bsr.fspro_userid is not null then bsr.firstName else case when rm.fspro_userid is not null then rm.firstName else pm.firstName end end cmFirstName, case when bsr.fspro_userid is not null then bsr.lastName else case when rm.fspro_userid is not null then rm.lastName else pm.lastName end end cmLastName, case when bsr.fspro_userid is not null then bsr.email else case when rm.fspro_userid is not null then rm.email else pm.email end end cmEmail, coalesce( bsr.fspro_userid, coalesce( rm.fspro_userid, pm.fspro_userid )) cmUserId , case when cdr.cdr_priority = '*' then 1 when cdr.cdr_priority = 'A' then 2 when cdr.cdr_priority = 'B' then 3 when cdr.cdr_priority = 'C' then 4 when cdr.cdr_priority = 'D' then 5 else 6 end sortOrder FROM tbl_CDR_Distributors cdr INNER JOIN tbl_TER_Territories t ON t.territoryid = cdr.cdr_territoryid AND t.ownerid = cdr.ownerid AND t.fsl_tablecode = cdr.fsl_tablecode left outer join tbl_FSPro_Members pm on pm.fspro_userId = t.primaryManager and pm.ownerId = t.ownerId and pm.fsl_tablecode = t.fsl_tablecode left outer join tbl_FSpro_Members bsr on bsr.fspro_userid = cdr.mfr_bsr_id left outer join tbl_SMA_BrokerOfficeLink sma on sma.territoryId = t.territoryId and sma.primaryOffice = 'Y' left outer join tbl_SEC_MemGrpLink rml on rml.partnerId = t.ownerId and rml.partnerTyp = t.fsl_tablecode and rml.brokerOfficeId = sma.brokerOfficeID and rml.groupId = left outer join tbl_FSpro_Members rm on rm.fspro_userid = rml.fspro_userId left outer join tbl_CDR_Classifications cls on cdr.classificationID = cls.classificationID left outer join tbl_DST_Distributors dstpr on dstpr.dstid = cdr.fsl_dstId inner join ( select distinct p.partnerid as distrribId, p.ownerId, p.ownerType from tbl_CRM_Participants p inner join tbl_CRM_Interactions i on i.participantId = p.participantId and i.ownerId = p.ownerId and i.ownerType = p.ownerType and i.disposition = and i.disposition = '?' inner join tbl_CRM_InteractionSaleDetails #isdAlias# on #isdAlias#.interactionId = i.interactionId and #isdAlias#.saleSubjectId = and #isdAlias#.disposition in ( 'STD', 'LTO') and #isdAlias#.disposition = and #isdAlias#.nosalereasonId = and i.disposition <> '?' where p.ownerId = and p.ownerType = and p.partnerType = 'CDR' and p.marketingActivityId = ) p on p.distrribId = cdr.cdr_recordId and p.ownerid = cdr.ownerId and p.ownerType = cdr.fsl_tableCode WHERE cdr.fsl_tablecode = AND cdr.ownerid = AND cdr.crmactive = 'Y' AND cdr.cdr_dstcompanytype <> 'O' and cdr.cdr_dstcompanytype <> 'S' AND ( cdr.mfr_bsr_id = OR cdr.cdr_territoryid IN ( ) ) AND cdr.classificationid = AND cdr.cdr_recordId not in ( select stk.cdr_recordId from tbl_CDR_Stocking stk where stk.ownerId = and stk.ownerType = and stk.volumeQty > 0 and stk.volumetime = 'M' and stk.skuId in ( select sku.skuid from tbl_PRD_Skus sku where sku.ownerId = and sku.FSL_TableCode = and sku.bgtCatID = and sku.skuid = and sku.ProdID = and sku.ProdID in ( select prd.prodId from tbl_PRD_Products prd where prd.ownerId = and prd.FSL_TableCode = and prd.categoryid = ) group by sku.skuid ) group by stk.cdr_recordId having SUM(stk.volumeqty) >= having SUM(stk.volumeqty) <= ) and ( cdr.cdr_priority in ( ) ) and ( cdr.lastInteractionDate is not null and datediff( dd, cdr.lastInteractionDate, getDate() ) <= ) ( cdr.lastInteractionDate is null or datediff( dd, cdr.lastInteractionDate, getDate() ) > ) ( cdr.lastInteractionDate is null or datediff( dd, cdr.lastInteractionDate, getDate() ) > 365 ) and cdr.lastInteractionDate is null and cdr.cdr_recordId in ( select i.partnerId from tbl_CRM_interactions i inner join tbl_CRM_InteractionSaleDetails isd on isd.interactionId = i.interactionId and isd.saleSubjectType = 'SKU' inner join tbl_PRD_Skus s on s.skuId = isd.saleSubjectId and s.ownerId = i.ownerId and s.fsl_tablecode = i.ownerType and s.mfrId = where i.ownerId = and i.ownerType = and i.partnerType = 'CDR' and i.dispositionUserId = ) and cdr.cdr_recordId in ( select i.partnerId from tbl_CRM_interactions i where i.ownerId = and i.ownerType = and i.partnerType = 'CDR' and i.disposition = '?' and i.disposition <> '?' ) AND cdr.mfr_bsr_id = and cdr.cdr_territoryId in () and cdr.cdr_territoryId in ( ) and cdr.cdr_territoryId in ( ) AND ( cdr.cdr_dstparentco IN () OR cdr.cdr_recordId in ( select cdrb.cdr_recordId from tbl_CDR_Distributors cdrb inner join tbl_DST_Distributors dstb on dstb.dstid = cdrb.fsl_dstid inner join tbl_DST_Distributors dstp on dstp.dstid = dstb.dstparentco inner join tbl_CDR_Distributors cdrp on cdrp.fsl_dstid = dstp.dstid and cdrp.ownerid = cdrb.ownerid and cdrp.fsl_tablecode = cdrb.fsl_tablecode and cdrp.cdr_recordid IN () where cdrb.ownerid = and cdrb.fsl_tablecode = ) OR cdr.cdr_recordID IN () ) AND (( cdr.cdr_dstparentco = 0 or cdr.cdr_dstparentco is null ) and cdr.cdr_dstcompanytype = 'B' ) and cdr.mfr_bsr_id in ( ) and coalesce( cdr.mfr_bsr_id, 0 ) = 0 AND ( cdr.cdr_dstcode = OR cdr.cdr_dstname like OR cdr.cdr_dstaddress1 like OR cdr.cdr_dstaddress2 like OR cdr.cdr_dstcity like OR cdr.cdr_dststate = OR cdr.cdr_dstzip like OR cdr.foodservicerewardsId = ) AND cdr.cdr_recordId in ( ) AND cdr.cdr_recordId in ( select spl.partnerId from tbl_SPL_Opportunities spl inner join tbl_SPL_Stages stg on stg.stageId = spl.stageId inner join tbl_WRK_Tasks tsk on tsk.subjectId = spl.opportunityId and tsk.subjectType = 'SPL' and tsk.ownerId = spl.ownerId and tsk.ownerType = spl.ownerType and tsk.disposition in ( 'A', '?' ) and tsk.dueDate <= { fn now() } and tsk.dueDate <= dateadd( day, #_daysAhead#, getDate() ) and tsk.taskTemplateId = where spl.partnerType = 'CDR' and spl.ownerId = and spl.ownerType = and spl.stageId in ( ) and spl.availableDate between and and datepart( yy, case when stg.statusCode = 'C' or stg.statusCode = 'I' then spl.availableDate when stg.statusCode = 'A' then getDate() when stg.statusCode = 'R' then spl.availableDate else coalesce( spl.updateDate, spl.createDate ) end ) + 3 > datepart( yy, getdate() ) ) select h.cdr_recordId, sum( h.dollarAmt ) annualDollars, sum( h.caseAmt ) annualCases, sum( h.weightAmt ) annualLbs from tbl_CDR_InvoiceHistory h inner join tbl_CDR_Distributors cdr on cdr.cdr_recordId = h.cdr_recordId and cdr.crmActive = 'Y' where h.ownerId = and h.ownerType = and h.fiscalYear = group by h.cdr_recordId order by annualLbsannualCasesannualDollars DESC select *, upper( cdr_dstName ) as nameSort from qmd_list order by ranking #attributes.sortDirection#,sortOrder #attributes.sortDirection#,nameSort #attributes.sortDirection#,nameSort select * from qmd_list where row between AND