select distinct o.opportunityId from tbl_SPL_Opportunities o inner join tbl_SPL_Stages s on s.stageid = o.stageid and s.ownerId = o.ownerId and s.ownerType = o.ownerType -- 637,638,942,639,640 AND s.stageId IN ( 637,638,942,639,640) inner join tbl_SPL_OpportunityTypes t on t.typeCode = o.typeCode left outer join tbl_CRM_NoSaleReasons nsr on nsr.ownerId = o.ownerId and nsr.ownerType = o.ownerType and nsr.reasonId = o.nosalereasonid left outer join tbl_CRM_Campaigns cc on cc.campaignId = o.campaignId and cc.ownerId = o.ownerId and cc.ownerType = o.ownerType inner JOIN tbl_CDR_Distributors cdr ON cdr.fsl_tablecode = o.ownerType and cdr.ownerId = o.ownerId and cdr.cdr_recordId = o.partnerId AND cdr.crmActive = 'Y' inner JOIN tbl_TER_Territories ter ON ter.territoryId = cdr.cdr_territoryId AND ter.ownerId = cdr.ownerId AND ter.fsl_tablecode = cdr.fsl_tablecode AND ter.ownerId = 2004 left outer join tbl_CDR_Classifications cls on cls.ownerId = cdr.ownerId and cls.ownerType = cdr.fsl_tablecode and cls.classificationId = cdr.classificationId LEFT OUTER JOIN tbl_CDR_Distributors rcdr on rcdr.cdr_recordId = case when o.cdrDistributorId is not null then o.cdrDistributorId else -1 end and rcdr.ownerId = o.ownerId and rcdr.fsl_tablecode = o.ownerType /* left outer join tbl_TER_BusinessPlans tbp on tbp.timeframeYear = cc.timeframeYear and tbp.timeframePeriod = cc.timeframePeriod and tbp.territoryId = ter.territoryId */ LEFT OUTER JOIN tbl_PRD_Products prd ON prd.prodid = o.subjectId AND prd.ownerId = o.ownerId AND prd.fsl_tablecode = o.ownerType LEFT OUTER JOIN tblManufacturers mfr on mfr.mfr_id = o.ownerId LEFT OUTER JOIN tbl_FSPro_members om on om.fspro_userid = o.salesPersonUserId LEFT OUTER JOIN tbl_FSPro_members cm on cm.fspro_userId = cdr.mfr_bsr_id LEFT OUTER JOIN tbl_FSPro_members oc on oc.fspro_userid = case when o.contactId is not null then o.contactId else cdr.primaryContactId end and oc.ownerId = o.ownerId and oc.fsl_tablecode = o.ownerType -- 2004, MFR WHERE o.ownerId = 2004 AND o.ownerType = 'MFR' AND ( ( -- {ts '2010-07-04 00:00:00'} o.availableDate >= {ts '2010-07-04 00:00:00'} -- {ts '2011-07-02 23:59:59'} AND o.availableDate <= {ts '2011-07-02 23:59:59'} ) OR ( o.durationUnit = 'YR' and s.statusCode = 'C' -- {ts '2010-07-04 00:00:00'} AND dateadd( year, 1, o.availableDate ) >= {ts '2010-07-04 00:00:00'} -- {ts '2011-07-02 23:59:59'} AND dateadd( year, 1, o.availableDate ) <= {ts '2011-07-02 23:59:59'} )) and prd.crmActive = 'Y' -- parentPartnerID = -- parentDistributorID = -- 3792 AND ter.territoryId in ( 3792 ) -- CDR AND o.partnerType = 'CDR' AND datepart( yy, case when s.statusCode = 'C' or s.statusCode = 'I' then o.availableDate when s.statusCode = 'A' then getDate() when s.statusCode = 'R' then o.availableDate else coalesce( o.updateDate, o.createDate ) end ) + 3 > datepart( yy, getdate() ) and o.opportunityId in ( select osd.opportunityId from tbl_SPL_OpportunitySubjectDetails osd inner join tbl_PRD_SKus s on s.skuId = osd.subjectDetailId and s.ownerId = osd.ownerId and s.fsl_tablecode = osd.ownerType inner join tbl_ORG_AttributeLinks oal on oal.child_attrId = s.skuId and oal.child_attrTypeId = 60 and oal.mstr_attrId in ( 12197,12198 ) and oal.mstr_attrTypeId = 29 where osd.ownerId = 2004 and osd.ownerType = 'MFR' and osd.subjectDetailType = 'SKU' union select o2.opportunityId from tbl_SPL_Opportunities o2 inner join tbl_ORG_AttributeLinks oal on oal.child_attrId = o2.subjectId and oal.child_attrTypeId = 59 and oal.mstr_attrId in ( 12197,12198 ) and oal.mstr_attrTypeId = 29 where o2.ownerId = 2004 and o2.ownerType = 'MFR' and o2.subjectType = 'PRD' union select osd.opportunityId from tbl_SPL_OpportunitySubjectDetails osd inner join tbl_ORG_AttributeLinks oal on oal.child_attrId = osd.subjectDetailId and oal.child_attrTypeId = 59 and oal.mstr_attrId in ( 12197,12198 ) and oal.mstr_attrTypeId = 29 where osd.ownerId = 2004 and osd.ownerType = 'MFR' and osd.subjectDetailType = 'PRD' ) -- arbitrary comment to suppress editor warnings due to > sign