declare @ownerId int = 387;
declare @ownerType varchar(3) = 'BRO';
declare @cutoffDate datetime = '01/01/2023';


with raw as (
	-- get the raw data
	select o.ownerid, o.ownertype, o.objectiveid, o.publishDate, ip.interactionid, isd.saleSubjectId, isd.saleSubjectType
			from tbl_bcrm_objectives o with (nolock)
			inner join tbl_bcrm_objectivetrglink otl with (nolock)
			on otl.objectiveid = o.objectiveid
			and otl.ownerid = o.ownerid
			and otl.ownertype = o.ownertype
			inner join tbl_bcrm_objectiveprdlink opl with (nolock)
			on opl.objectiveid = o.objectiveid
			and opl.ownerid = o.ownerid
			and opl.ownertype = o.ownertype
			inner join tbl_crm_interactionparticipants ip with (nolock)
			on ip.participantid = otl.linkid
			and ip.ownerid = otl.ownerid
			and ip.ownertype = otl.ownertype
			inner join tbl_CRM_interactionSaleDetails isd with (nolock)
			on isd.ownerId = ip.ownerId
			and isd.ownerType = ip.ownerType
			and isd.interactionId = ip.interactionId
			and isd.saleSubjectType = case when nullif( opl.skuId, 0 ) is not null then 'SKU' else 'PRD' end
			and isd.saleSubjectId = coalesce( nullif( opl.skuId, 0 ), opl.categoryId )
			where o.ownerid = @ownerId
			and o.ownertype = @ownerType
			and o.publishdate >= @cutoffDate

)

, ranked as (
	-- rank interaction / product / objective by the objective publish date - newest ranked first
	select  ownerid, ownertype, objectiveid, interactionid, saleSubjectId, saleSubjectType, 
	        row_number() over( partition by interactionId, saleSubjectType, saleSubjectId order by publishDate desc ) ranked
	from raw
)

, rawData as (
	-- just take the top 6, and add in the objective pivot value
	select distinct ownerid, ownertype, objectiveid, interactionid, saleSubjectId, saleSubjectType, 'objectiveId_' + cast( ranked as varchar ) objectivePivot 
	from ranked where ranked <= 6

)


-- pivot objectiveId and add in objective name for up to 6 objectives
SELECT  
    p1.ownerId, p1.ownerType, p1.interactionId, p1.saleSubjectId, p1.saleSubjectType, 
	p1.objectiveId_1,o1.objName objName_1, 
	p1.objectiveId_2,o2.objName objName_2, 
	p1.objectiveId_3,o3.objName objName_3, 
	p1.objectiveId_4,o4.objName objName_4,  
	p1.objectiveId_5,o5.objName objName_5, 
	p1.objectiveId_6,o6.objName objName_6
	
	
FROM  
    ( select ownerid, ownertype, objectiveid, interactionid, saleSubjectId, saleSubjectType, objectivePivot from rawData ) as allData
PIVOT ( avg( objectiveId ) 
FOR  objectivePivot  
IN ( objectiveId_1, objectiveId_2, objectiveId_3, objectiveId_4, objectiveId_5, objectiveId_6 ) 
) AS p1

left outer join tbl_BCRM_Objectives o1 with (nolock)
   on o1.objectiveId= p1.objectiveId_1
   and o1.ownerId = p1.ownerId
   and o1.ownerType = p1.ownerType

left outer join tbl_BCRM_Objectives o2 with (nolock)
   on o2.objectiveId= p1.objectiveId_2
   and o2.ownerId = p1.ownerId
   and o2.ownerType = p1.ownerType
   
 left outer join tbl_BCRM_Objectives o3 with (nolock)
   on o3.objectiveId= p1.objectiveId_3
   and o3.ownerId = p1.ownerId
   and o3.ownerType = p1.ownerType

   left outer join tbl_BCRM_Objectives o4 with (nolock)
   on o4.objectiveId= p1.objectiveId_4
   and o4.ownerId = p1.ownerId
   and o4.ownerType = p1.ownerType

left outer join tbl_BCRM_Objectives o5 with (nolock)
   on o5.objectiveId= p1.objectiveId_5
   and o5.ownerId = p1.ownerId
   and o5.ownerType = p1.ownerType

left outer join tbl_BCRM_Objectives o6 with (nolock)
   on o6.objectiveId= p1.objectiveId_6
   and o6.ownerId = p1.ownerId
   and o6.ownerType = p1.ownerType
order by interactionId, saleSubjectId, saleSubjectType