declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @targetType varchar(3) = 'OPR'; declare @targetId int = ; declare @salesRepId int = ; with linkedObjectives as ( select 'LINKED' as linkStatus, 1 as linkStatusKey, obj.ObjectiveID, obj.ObjName, obj.TotalQuota, null quota, obj.QuotaAchieved, (select COUNT(distinct targetid) from tbl_BCRM_ObjectiveTrgLink with (nolock) where ObjectiveID = otl.ObjectiveID) targetCount, obj.publishDate, obj.unpublishDate, case when getDate() between obj.publishDate and obj.unpublishDate then datediff( day, getDate(), obj.unpublishDate ) else cast( null as int ) end as daysRemaining, case when obj.objOwnerType = 'INT' then 'Internal' else mfr.mfr_name end as objOwnerName from tbl_BCRM_ObjectiveTrgLink otl with (nolock) inner join tbl_BCRM_Objectives obj with (nolock) on obj.ObjectiveID = otl.ObjectiveID and GETDATE() <= obj.unpublishdate and obj.ready = 'y' left outer join tblManufacturers mfr with (nolock) on mfr.mfr_id = obj.objOwnerId and 'MFR' = obj.objOwnerType where otl.ownerid = @ownerId and otl.OwnerType = @ownerType and otl.TargetType = @targetType and otl.TargetID = @targetId ) and GETDATE() <= obj.unpublishdate and obj.TargetType = @targetType and obj.Ready = 'y' and obj.allowRepTargeting = 1 and ( obj.territoryid = 0 or obj.territoryid = opr.territoryid ) and not exists ( select * from tbl_BCRM_ObjectiveTrgLink with (nolock) where ObjectiveID = obj.ObjectiveID and TargetType = @targetType and TargetID = @targetId) and ( obj.oprpriorityrestrict = 0 OR obj.oprpriorityrestrict = opr.classificationid ) and ( not exists ( select restrictid from tbl_bcrm_objrestrictions with (nolock) where ownerid = obj.ownerid and ownertype = obj.ownertype and objectiveid = obj.objectiveid and restricttype = 'SEG' ) OR exists ( select restrictid from tbl_bcrm_objrestrictions sq_obr with (nolock) inner join tbl_opr_clientoperators sq_opr with (nolock) on sq_opr.operatorid = opr.operatorid inner join tbl_opr_clientsegments sq_seg with (nolock) on sq_seg.clientsegid = sq_opr.oprsegment where sq_obr.ownerid = obj.ownerid and sq_obr.ownertype = obj.ownertype and sq_obr.objectiveid = obj.objectiveid and sq_obr.restricttype = 'SEG' and ( sq_obr.restrictid = sq_seg.clientsegid OR sq_obr.restrictid = sq_seg.ParentClientSegId ) ) ) and ( obj.oprdistributorrestrict = 0 OR obj.oprdistributorrestrict = opr.distrib1 ) and ( obj.opraffiliationrestrict = 0 OR ( select count(*) from tbl_crm_affiliations with (nolock) where ownerid = obj.ownerid and ownertype = obj.ownertype and orgid = obj.opraffiliationrestrict and orgtype = 'opr' and partnertype = 'opr' and partnerid = opr.operatorid ) > 0 ) and ( obj.oprtyperestrict is null OR obj.oprtyperestrict = opr.oprtype ) and ( ( obj.mfroprtyperestrict is null or obj.ObjOwnerType <> 'mfr' ) OR ( obj.mfroprtyperestrict = ( select focusaccount from tbl_BRO_PrincipalOperators po with (nolock) inner join tbl_BRO_PrincipalTerritories pt with (nolock) on pt.principalTerritoryId = po.principalTerritoryId inner join tbl_BRO_Principals p with (nolock) on p.principalid = pt.principalid and p.mfrid = obj.ObjOwnerId where po.ownerid = obj.ownerid and po.ownertype = obj.ownertype and po.operatorid = opr.operatorid ) ) ) ) ---> , data as ( select linkStatusKey, linkStatus, objectiveId, objName, totalQuota, quota, quotaAchieved, targetCount, publishDate, unpublishDate, daysRemaining, objOwnerName, case when totalQuota > 0 then quotaAchieved / cast( totalQuota as float ) when quotaAchieved > 0 then 1.0 else cast( null as float ) end achievement from linkedObjectives ) #forJSON( 'data', attributes.datastore, 'linkStatusKey,objName' )#