select * from qmd_stages where statusCode = 'A' or statusCode = 'I' select territoryName, territoryId, teamMemberId, teamMemberUserId, firstName, lastName, fspro_userid from qmd_territoryTeam order by territoryName, lastName, firstName select territoryName, territoryId, teamMemberId, teamMemberUserId, firstName, lastName, fspro_userid from qmd_teamMembers union select territoryName, territoryId, teamMemberId, teamMemberUserId, firstName, lastName, fspro_userid from qmd_territoryTeam order by territoryName, lastName, firstName select territoryName, territoryId, teamMemberId, teamMemberUserId, firstName, lastName, fspro_userid, brokerRep from qmd_teamMembers order by territoryName, brokerRep, lastName, firstName select distinct salesPersonUserId from tbl_SPL_Opportunities where stageId in ( ) and ownerId = and ownerType = and salesPersonUserId in ( ) select territoryName, territoryId, teamMemberId, teamMemberUserId, firstName, lastName, fspro_userid, brokerRep from qmd_teamMembers where teamMemberUserId in ( ) order by territoryName, brokerRep, lastName, firstName select spl.partnerId, spl.partnerType, opr.territoryId, opr.companyName, sum( case when stg.statusCode = 'A' and spl.availableDate < getDate() then 1 else 0 end ) pastDue, sum( spl.caseAmt ) cases, sum( spl.weightAmt ) lbs, sum( spl.incomeAmt ) dollars, max( spl.createDate ) mostRecent, count(*) opportunities from tbl_SPL_Opportunities spl inner join tbl_OPR_ClientOperators opr on opr.operatorId = spl.partnerId inner join tbl_SPL_Stages stg on stg.stageId = spl.stageId where spl.ownerId = and spl.ownerType = and spl.stageId in ( ) and spl.partnerType = 'OPR' and opr.crmActive = 'Y' and spl.salesPersonUserId = and opr.territoryId = and spl.salesPersonUserId = 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 > #year( now() )# group by spl.partnerType, spl.partnerId, opr.territoryId, opr.companyName order by companyName select spl.partnerId, spl.partnerType, cdr.cdr_territoryId territoryId, cdr.cdr_dstName companyName, sum( case when stg.statusCode = 'A' and spl.availableDate < getDate() then 1 else 0 end ) pastDue, sum( spl.caseAmt ) cases, sum( spl.weightAmt ) lbs, sum( spl.incomeAmt ) dollars, max( spl.createDate ) mostRecent, count(*) opportunities from tbl_SPL_Opportunities spl inner join tbl_CDR_Distributors cdr on cdr.cdr_recordId = spl.partnerId inner join tbl_SPL_Stages stg on stg.stageId = spl.stageId where spl.ownerId = and spl.ownerType = and spl.stageId in ( ) and spl.partnerType = 'CDR' and cdr.crmActive = 'Y' and spl.salesPersonUserId = and cdr._cdrterritoryId = and spl.salesPersonUserId = 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 > #year( now() )# group by spl.partnerType, spl.partnerId, cdr.cdr_territoryId, cdr.cdr_dstname order by companyName select #_columnList#, upper( companyName ) as companyNameSort from qmd_leadAccounts_OPR union select #_columnList#, upper( companyName ) as companyNameSort from qmd_leadAccounts_CDR order by companyNameSort