select coalesce(SUM(AnnualCaseAmt),0) as annualObjCasesSold, coalesce(sum((AnnualCaseAmt) * (commissionrate * averageUnitPrice)),0) as annualObjCommSold from ( select distinct isd.InteractionID, isd.salesubjectid, isd.AnnualCaseAmt, coalesce(prd.commissionrate,0) commissionrate, coalesce(averageUnitPrice,0) averageUnitPrice from tbl_crm_interactionsaledetails isd with (nolock) inner join tbl_CRM_Interactions int with (nolock) on int.InteractionID = isd.interactionid and int.ownerId = and int.ownerType = and int.DispositionUserId = and int.InteractionDate >= and int.InteractionDate <= and int.Disposition <> '?' inner join tbl_CRM_InteractionParticipants ip with (nolock) on ip.InteractionID = int.InteractionID and ip.ownerId = int.ownerId and ip.ownerType = int.ownerType inner join tbl_BCRM_ObjectivetrgLink otl with (nolock) on otl.linkid = ip.participantid and otl.ownerId = ip.ownerId and otl.ownerType = ip.ownerType inner join tbl_BCRM_Objectives obj with (nolock) on obj.ObjectiveID = otl.objectiveid and obj.Ready = 'Y' and obj.ownerId = otl.ownerId and obj.ownerType = otl.ownerType inner join tbl_BCRM_ObjectiveStfLink osl with (nolock) on osl.ObjectiveID = otl.objectiveid and osl.Quota > 0 and osl.ownerID = otl.ownerId and osl.ownerType = otl.ownerType inner join tbl_PRD_SKUs sku with (nolock) on sku.skuid = isd.salesubjectid and sku.ownerID = isd.ownerId and sku.FSL_TableCode = isd.ownerType left outer join tbl_PRD_Products prd with (nolock) on prd.ProdID = sku.prodid and prd.ownerID = sku.ownerId and prd.FSL_TableCode = sku.FSL_TableCode inner join tbl_BCRM_ObjectivePrdLink opl with (nolock) on opl.ObjectiveID = otl.objectiveid and ( ( ( opl.skuid = isd.salesubjectid ) OR ( opl.categoryid = sku.ProdID and sku.ProdID > 0) OR ( opl.categoryid = -1 and opl.mfrid = sku.mfrid) ) AND isd.salesubjectid NOT IN ( select skuid from tbl_bcrm_objectiveprdexclusions with (nolock) where objectiveid = opl.objectiveid ) ) where isd.salesubjecttype = 'sku' and isd.disposition = 'std' ) mytable select coalesce(SUM(osl.quota),0) as QTDQuota, coalesce(SUM(case when osl.QuotaAchieved > osl.quota then osl.quota else osl.QuotaAchieved end),0) as QTDAchievement from tbl_BCRM_ObjectiveStfLink osl with (nolock) inner join tbl_BCRM_Objectives obj with (nolock) on obj.ObjectiveID = osl.ObjectiveID and obj.ownerId = osl.ownerId and obj.ownerType = osl.ownerType and ( UnpublishDate >= and UnpublishDate <= ) and obj.ready = 'Y' where osl.FSPro_UserID = and osl.ownerId = and osl.ownerType = and osl.quota > 0 select coalesce(SUM(osl.quota),0) as HTDQuota, coalesce(SUM(case when osl.QuotaAchieved > osl.quota then osl.quota else osl.QuotaAchieved end),0) as HTDAchievement, COUNT(distinct obj.objectiveid) as HTDObjectiveAssignments from tbl_BCRM_ObjectiveStfLink osl with (nolock) inner join tbl_BCRM_Objectives obj with (nolock) on obj.ObjectiveID = osl.ObjectiveID and obj.ownerId = osl.ownerId and obj.ownerType = osl.ownerType and ( UnpublishDate >= and UnpublishDate <= ) and obj.ready = 'Y' where osl.FSPro_UserID = and osl.ownerId = and osl.ownerType = and osl.quota > 0 select COUNT(distinct obj.objectiveid) as HTDCompletedObjectives from tbl_BCRM_ObjectiveStfLink osl with (nolock) inner join tbl_BCRM_Objectives obj with (nolock) on obj.ObjectiveID = osl.ObjectiveID and obj.ownerId = osl.ownerId and obj.ownerType = osl.ownerType and ( UnpublishDate >= and UnpublishDate <= ) and obj.ready = 'Y' where osl.FSPro_UserID = and osl.ownerId = and osl.ownerType = and osl.QuotaAchieved >= osl.Quota and osl.quota > 0 select coalesce(sum( case when (osl.quota = 0) OR (osl.QuotaAchieved > osl.Quota) then 1 else cast(osl.quotaachieved as decimal) / cast(osl.quota as decimal) end ) / COUNT(*), 0) as objAchievementPercentHTD from tbl_BCRM_ObjectiveStfLink osl with (nolock) inner join tbl_BCRM_Objectives obj with (nolock) on obj.ObjectiveID = osl.ObjectiveID and obj.ownerId = osl.ownerId and obj.ownerType = osl.ownerType and ( UnpublishDate >= and UnpublishDate <= ) and obj.ready = 'Y' where osl.FSPro_UserID = and osl.ownerId = and osl.ownerType = and osl.quota > 0