SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DROP FUNCTION IF EXISTS dbo.fn_GetSagaELTSLTLevels;
GO

CREATE FUNCTION [dbo].[fn_GetSagaELTSLTLevels](@PIT datetime, @ChildId int)
RETURNS TABLE						
AS
RETURN
(
	with parents as 
	(
	  select Entity_Start_Date AS EffDate
			, NULL AS ParentID
			, Entity_Id AS ChildID
			, 1 As Depth
			, CASE WHEN CAST(pay_grade as varchar) = '3' THEN Entity_Id ELSE NULL END AS Level3
			, CASE WHEN CAST(pay_grade as varchar) = '4' THEN Entity_Id ELSE NULL END AS Level4
			, CASE WHEN CAST(pay_grade as varchar) = '5' THEN Entity_Id ELSE NULL END AS Level5

	  from DT_JOB
	  WHERE @PIT BETWEEN Entity_Start_Date AND ISNULL(Entity_End_Date, '31 dec 2499')
	  AND NOT EXISTS (SELECT Ndx FROM LNK_JOB_JOB_VIRTUAL WHERE Right_Join = Entity_Id)
	  union all 
	  select Start_Date AS EffDate
			, Left_Join
			, Right_Join
			, parents.Depth + 1
			, CASE WHEN CAST(pay_grade as varchar) = '3' THEN Entity_Id ELSE parents.Level3 END AS Level3
			, CASE WHEN CAST(pay_grade as varchar) = '4' THEN Entity_Id ELSE parents.Level4 END AS Level4
			, CASE WHEN CAST(pay_grade as varchar) = '5' THEN Entity_Id ELSE parents.Level5 END AS Level5
	  from LNK_JOB_JOB_VIRTUAL
		inner join parents on Left_Join = [ChildID]
		 AND @PIT BETWEEN LNK_JOB_JOB_VIRTUAL.Start_Date AND ISNULL(LNK_JOB_JOB_VIRTUAL.End_Date, '31 dec 2499')
		inner join DT_JOB on Entity_Id = Right_Join
		AND @PIT BETWEEN Entity_Start_Date AND ISNULL(Entity_End_Date, '31 dec 2499')
	)

	SELECT ROW_NUMBER() OVER (PARTITION BY [ChildID] ORDER BY EffDate) AS RowNum
		, [ParentID] 
		, [ChildID] 
		, [Depth] 
		, CASE WHEN Level4 = [ChildID] THEN Level5 ELSE Level4 END AS ELT
		, CASE WHEN Level3 = [ChildID] THEN NULL ELSE Level3 END AS SLT
	FROM parents
	WHERE ([ChildID] = @ChildId OR @ChildId IS NULL)
	)


GO