SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Person_Provider_Contact]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Person_Provider_Contact]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Person_Provider_Contact]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Contract_Location]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Location]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Contract_Location]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Contract_Contract_Type]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Contract_Type]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Contract_Contract_Type]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Job_Contract]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Job_Contract]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Job_Contract]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Course_Provider]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Course_Provider]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Course_Provider]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Course_Provider_Base]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Course_Provider_Base]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Course_Provider_Base]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Course_Provider_Contact]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Course_Provider_Contact]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Course_Provider_Contact]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Course_Provider_Contact_Base]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Course_Provider_Contact_Base]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Course_Provider_Contact_Base]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Contract_Contract]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Contract]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Contract_Contract]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Job_Location]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Job_Location]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Job_Location]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Job_Department]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Job_Department]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Job_Department]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Job_Contract_Type]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Job_Contract_Type]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Job_Contract_Type]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Course_Course_Type]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Course_Course_Type]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Course_Course_Type]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Session_Course]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Session_Course]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Session_Course]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Session_Location]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Session_Location]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Session_Location]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Contract_Department]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Department]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Contract_Department]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Session_Provider]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Session_Provider]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Session_Provider]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Attendance_Session]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Attendance_Session]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Attendance_Session]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Job_Pay_Grade]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Job_Pay_Grade]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Job_Pay_Grade]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Contract_Manager]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Manager]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Contract_Manager]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Contract_Manager_Base]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Manager_Base]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Contract_Manager_Base]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Job_Job]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Job_Job]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Job_Job]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Contract_Contract_Type_Base]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Contract_Type_Base]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Contract_Contract_Type_Base]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Contract_Location_Base]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Location_Base]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Contract_Location_Base]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping [dbo].[fn_Contract_Department_Base]'
GO
IF EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Department_Base]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
DROP FUNCTION [dbo].[fn_Contract_Department_Base]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Contract_Department_Base]'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Department_Base]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'
CREATE FUNCTION [dbo].[fn_Contract_Department_Base]()  
RETURNS TABLE
RETURN

	--First the contract overides
	SELECT DISTINCT 	Entity_Id AS Left_Join, 
				CAST(Department AS int) AS Right_Join,
				Entity_Start_Date as Start_Date, 
				Entity_End_Date as End_Date
	FROM         DT_CONTRACT WITH (NOLOCK)
	WHERE CAST(ISNULL(Department, 0) AS int) > 0
UNION
	--Now the position defaults
	SELECT DISTINCT DTC.Entity_Id AS Left_Join, VLT.Right_Join,
	(CASE WHEN VLT.Start_Date < Entity_Start_Date THEN Entity_Start_Date ELSE VLT.Start_Date END) as Start_Date, 
	(CASE WHEN ISNULL(VLT.End_Date, ''31 Dec 2299'') > ISNULL(Entity_End_Date, ''31 Dec 2299'') THEN Entity_End_Date ELSE VLT.End_Date END) as End_Date
	FROM dbo.LNK_JOB_DEPARTMENT_VIRTUAL VLT WITH (NOLOCK)
	INNER JOIN DT_CONTRACT DTC WITH (NOLOCK)
	ON VLT.Left_Join = CAST(DTC.Job as int)
	AND CAST(ISNULL(DTC.Department, 0) as int) = 0
	AND VLT.Start_Date <= ISNULL(Entity_End_Date, ''31 Dec 2299'') AND ISNULL(VLT.End_Date, ''31 Dec 2299'') >=  Entity_Start_Date
'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Contract_Location_Base]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Location_Base]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'
CREATE FUNCTION [dbo].[fn_Contract_Location_Base]()  
RETURNS TABLE
RETURN

	--First the contract overides
	SELECT DISTINCT 	Entity_Id AS Left_Join, 
				CAST(Location AS int) AS Right_Join,
				Entity_Start_Date as Start_Date, 
				Entity_End_Date as End_Date
	FROM         DT_CONTRACT WITH (NOLOCK)
	WHERE CAST(ISNULL(Location, 0) AS int) > 0
UNION
	--Now the position defaults
	SELECT DISTINCT DTC.Entity_Id AS Left_Join, VLT.Right_Join,
	(CASE WHEN VLT.Start_Date < Entity_Start_Date THEN Entity_Start_Date ELSE VLT.Start_Date END) as Start_Date, 
	(CASE WHEN ISNULL(VLT.End_Date, ''31 Dec 2299'') > ISNULL(Entity_End_Date, ''31 Dec 2299'') THEN Entity_End_Date ELSE VLT.End_Date END) as End_Date
	FROM dbo.LNK_JOB_LOCATION_VIRTUAL VLT WITH (NOLOCK)
	INNER JOIN DT_CONTRACT DTC WITH (NOLOCK)
	ON VLT.Left_Join = CAST(DTC.Job as int)
	AND VLT.Start_Date <= ISNULL(Entity_End_Date, ''31 Dec 2299'') AND ISNULL(VLT.End_Date, ''31 Dec 2299'') >=  Entity_Start_Date
	AND CAST(ISNULL(DTC.Location, 0) as int) = 0

'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Contract_Department]'
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Department]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'

CREATE FUNCTION [dbo].[fn_Contract_Department] (@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(

	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Left_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Left_Join, ChildSeq, Right_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT Left_Join, Right_Join, Start_Date, End_Date
						, ROW_NUMBER() OVER (PARTITION BY Left_Join ORDER BY Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Left_Join ORDER BY Start_Date) - ROW_NUMBER() OVER (PARTITION BY Left_Join, Right_Join ORDER BY Start_Date) as ChildSeq
					FROM [dbo].fn_Contract_Department_Base() OuterQuery
					WHERE (Left_Join = @Left_Join OR @Left_Join IS NULL)
					AND (Right_Join = @Right_Join OR @Right_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date
		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Left_Join = OuterQuery.Left_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Left_Join = OuterQuery.Left_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Left_Join = OuterQuery.Left_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Left_Join = OuterQuery.Left_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1
		) a

)

'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Contract_Contract_Type_Base]'
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Contract_Type_Base]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'

CREATE FUNCTION [dbo].[fn_Contract_Contract_Type_Base] ()  
RETURNS TABLE
RETURN
	--First the contract overides
	SELECT DISTINCT 	Entity_Id AS Left_Join, 
				CAST(Contract_Type AS int) AS Right_Join,
				Entity_Start_Date as Start_Date, 
				Entity_End_Date as End_Date
	FROM         DT_CONTRACT WITH (NOLOCK)
	WHERE CAST(ISNULL(Contract_Type,0) AS int) > 0
UNION
	--Now the position defaults
	SELECT DISTINCT DTC.Entity_Id AS Left_Join, VLT.Right_Join,
	(CASE WHEN VLT.Start_Date < Entity_Start_Date THEN Entity_Start_Date ELSE VLT.Start_Date END) as Start_Date, 
	(CASE WHEN ISNULL(VLT.End_Date, ''31 Dec 2299'') > ISNULL(Entity_End_Date, ''31 Dec 2299'') THEN Entity_End_Date ELSE VLT.End_Date END) as End_Date
	FROM dbo.LNK_JOB_CONTRACT_TYPE_VIRTUAL VLT WITH (NOLOCK)
	INNER JOIN DT_CONTRACT DTC WITH (NOLOCK)
	ON VLT.Left_Join = CAST(DTC.Job as int)
	AND VLT.Start_Date <= ISNULL(Entity_End_Date, ''31 Dec 2299'') AND ISNULL(VLT.End_Date, ''31 Dec 2299'') >=  Entity_Start_Date
	AND CAST(ISNULL(DTC.Contract_Type, 0) as int) = 0

'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Job_Job]'
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Job_Job]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'

CREATE FUNCTION [dbo].[fn_Job_Job](@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Right_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Right_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Right_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Right_Join, ChildSeq, Left_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT CAST(ISNULL(ManagingJob, 0) AS int) AS Left_Join
							,Entity_Id AS Right_Join
							, Entity_Start_Date AS Start_Date
							, Entity_End_Date AS End_Date
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) - ROW_NUMBER() OVER (PARTITION BY CAST(ManagingJob AS int), Entity_Id ORDER BY Entity_Start_Date) as ChildSeq
					FROM [dbo].[DT_JOB] OuterQuery
					WHERE NOT CAST(ISNULL(ManagingJob, 0) AS int) = 0
					AND  (CAST(ManagingJob AS int) = @Left_Join OR @Left_Join IS NULL)
							AND (Entity_Id = @Right_Join OR  @Right_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
					, OuterQuery.Right_Join
					, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
					, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date

		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Right_Join = OuterQuery.Right_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Right_Join = OuterQuery.Right_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Right_Join = OuterQuery.Right_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Right_Join = OuterQuery.Right_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1

		) a
)
'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Contract_Manager_Base]'
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Manager_Base]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'




CREATE FUNCTION [dbo].[fn_Contract_Manager_Base]()  
RETURNS TABLE
RETURN

	--First the contract overides
	SELECT DISTINCT 	Entity_Id AS Left_Join, 
				CAST(Manager AS int) AS Right_Join,
				Entity_Start_Date as Start_Date, 
				Entity_End_Date as End_Date
	FROM         DT_CONTRACT WITH (NOLOCK)
	WHERE CAST(Manager AS int) > 0

UNION ALL
	--Now the position defaults
	SELECT DISTINCT DTC.Entity_Id AS Left_Join, VLT.Left_Join,
	(CASE WHEN VLT.Start_Date < Entity_Start_Date THEN Entity_Start_Date ELSE VLT.Start_Date END) as Start_Date, 
	(CASE WHEN ISNULL(VLT.End_Date, ''31 Dec 2299'') > ISNULL(Entity_End_Date, ''31 Dec 2299'') THEN Entity_End_Date ELSE VLT.End_Date END) as End_Date
	FROM dbo.fn_Job_Job(NULL, NULL) VLT 
	INNER JOIN DT_CONTRACT DTC WITH (NOLOCK)
	ON VLT.Right_Join = CAST(DTC.Job as int)
	WHERE  VLT.Start_Date <= ISNULL(Entity_End_Date, ''31 Dec 2299'') 
	AND CASE WHEN VLT.End_Date IS NULL THEN ''31 Dec 2299'' ELSE VLT.End_Date END >= Entity_Start_Date
	AND (DTC.Manager = 0 OR DTC.Manager IS NULL) 

'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Contract_Manager]'
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Manager]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'



CREATE FUNCTION [dbo].[fn_Contract_Manager](@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(

	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Left_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - DENSE_RANK() OVER (PARTITION BY Left_Join, ChildSeq, Right_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT Left_Join, Right_Join, Start_Date, End_Date
						, ROW_NUMBER() OVER (PARTITION BY Left_Join ORDER BY Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Left_Join ORDER BY Start_Date) - ROW_NUMBER() OVER (PARTITION BY Left_Join, Right_Join ORDER BY Start_Date) as ChildSeq
					FROM [dbo].[fn_Contract_Manager_Base]() OuterQuery
					WHERE (Left_Join = @Left_Join OR @Left_Join IS NULL)
					AND (Right_Join = @Right_Join OR @Right_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date
		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Left_Join = OuterQuery.Left_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Left_Join = OuterQuery.Left_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Left_Join = OuterQuery.Left_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Left_Join = OuterQuery.Left_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1
		) a

)
'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Job_Pay_Grade]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Job_Pay_Grade]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'
CREATE FUNCTION [dbo].[fn_Job_Pay_Grade] (@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Left_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Left_Join, ChildSeq, Right_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT Entity_Id AS Left_Join
							, CAST(ISNULL(Pay_Grade, 0) AS int) AS Right_Join
							, Entity_Start_Date AS Start_Date
							, Entity_End_Date AS End_Date
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) - ROW_NUMBER() OVER (PARTITION BY Entity_Id, CAST(Pay_Grade AS int) ORDER BY Entity_Start_Date) as ChildSeq
					FROM [dbo].[DT_JOB] OuterQuery
					WHERE NOT CAST(ISNULL(Pay_Grade, 0) AS int) = 0
					AND  (CAST(Pay_Grade AS int) = @Right_Join OR @Right_Join IS NULL)
							AND (Entity_Id = @Left_Join OR  @Left_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date

		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Left_Join = OuterQuery.Left_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Left_Join = OuterQuery.Left_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Left_Join = OuterQuery.Left_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Left_Join = OuterQuery.Left_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1

		) a
			
)
'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Contract_Location]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Location]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'
CREATE FUNCTION [dbo].[fn_Contract_Location](@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(

	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Left_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Left_Join, ChildSeq, Right_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT Left_Join, Right_Join, Start_Date, End_Date
						, ROW_NUMBER() OVER (PARTITION BY Left_Join ORDER BY Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Left_Join ORDER BY Start_Date) - ROW_NUMBER() OVER (PARTITION BY Left_Join, Right_Join ORDER BY Start_Date) as ChildSeq
					FROM [dbo].[fn_Contract_Location_Base]() OuterQuery
					WHERE (Left_Join = @Left_Join OR @Left_Join IS NULL)
					AND (Right_Join = @Right_Join OR @Right_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date
		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Left_Join = OuterQuery.Left_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Left_Join = OuterQuery.Left_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Left_Join = OuterQuery.Left_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Left_Join = OuterQuery.Left_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1
		) a

)
'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Contract_Contract_Type]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Contract_Type]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'


CREATE FUNCTION [dbo].[fn_Contract_Contract_Type] (@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(

	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Left_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Left_Join, ChildSeq, Right_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT Left_Join, Right_Join, Start_Date, End_Date
						, ROW_NUMBER() OVER (PARTITION BY Left_Join ORDER BY Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Left_Join ORDER BY Start_Date) - ROW_NUMBER() OVER (PARTITION BY Left_Join, Right_Join ORDER BY Start_Date) as ChildSeq
					FROM [dbo].fn_Contract_Contract_Type_Base() OuterQuery
					WHERE (Left_Join = @Left_Join OR @Left_Join IS NULL)
					AND (Right_Join = @Right_Join OR @Right_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date
		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Left_Join = OuterQuery.Left_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Left_Join = OuterQuery.Left_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Left_Join = OuterQuery.Left_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Left_Join = OuterQuery.Left_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1
		) a

)

'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Job_Department]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Job_Department]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'
CREATE FUNCTION [dbo].[fn_Job_Department] (@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Left_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Left_Join, ChildSeq, Right_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT Entity_Id AS Left_Join
							, CAST(ISNULL(Department, 0) AS int) AS Right_Join
							, Entity_Start_Date AS Start_Date
							, Entity_End_Date AS End_Date
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) - ROW_NUMBER() OVER (PARTITION BY Entity_Id, CAST(Department AS int) ORDER BY Entity_Start_Date) as ChildSeq
					FROM [dbo].[DT_JOB] OuterQuery
					WHERE NOT CAST(ISNULL(Department, 0) AS int) = 0
					AND   (CAST(Department AS int) = @Right_Join OR @Right_Join IS NULL)
							AND (Entity_Id = @Left_Join OR  @Left_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date

		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Left_Join = OuterQuery.Left_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Left_Join = OuterQuery.Left_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Left_Join = OuterQuery.Left_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Left_Join = OuterQuery.Left_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1

		) a
			
)

'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Job_Location]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Job_Location]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'
CREATE FUNCTION [dbo].[fn_Job_Location](@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Left_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Left_Join, ChildSeq, Right_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT Entity_Id AS Left_Join
							, CAST(ISNULL(Location, 0) AS int) AS Right_Join
							, Entity_Start_Date AS Start_Date
							, Entity_End_Date AS End_Date
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) - ROW_NUMBER() OVER (PARTITION BY Entity_Id, CAST(Location AS int) ORDER BY Entity_Start_Date) as ChildSeq
					FROM [dbo].[DT_JOB] OuterQuery
					WHERE NOT CAST(ISNULL(Location, 0) AS int) = 0
					AND  (CAST(Location AS int) = @Right_Join OR @Right_Join IS NULL)
							AND (Entity_Id = @Left_Join OR  @Left_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date

		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Left_Join = OuterQuery.Left_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Left_Join = OuterQuery.Left_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Left_Join = OuterQuery.Left_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Left_Join = OuterQuery.Left_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1

		) a
			
)

'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Job_Contract]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Job_Contract]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'

CREATE FUNCTION [dbo].[fn_Job_Contract](@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Right_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Right_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Right_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Right_Join, ChildSeq, Left_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT  CAST(ISNULL(Job, 0) AS int) AS Left_Join
							, Entity_Id AS Right_Join
							, Entity_Start_Date AS Start_Date
							, Entity_End_Date AS End_Date
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) - ROW_NUMBER() OVER (PARTITION BY Entity_Id, CAST(Job AS int) ORDER BY Entity_Start_Date) as ChildSeq
					FROM [dbo].[DT_CONTRACT] OuterQuery
					WHERE NOT CAST(ISNULL(Job, 0) AS int) = 0
					AND  (CAST(Job AS int) = @Left_Join OR @Left_Join IS NULL)
							AND (Entity_Id = @Right_Join OR  @Right_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Right_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date

		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Right_Join = OuterQuery.Right_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Right_Join = OuterQuery.Right_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Right_Join = OuterQuery.Right_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Right_Join = OuterQuery.Right_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1

		) a
			
)

'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Job_Contract_Type]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Job_Contract_Type]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'
CREATE FUNCTION [dbo].[fn_Job_Contract_Type] (@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Left_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Left_Join, ChildSeq, Right_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT Entity_Id AS Left_Join
							, CAST(ISNULL(Contract_Type, 0) AS int) AS Right_Join
							, Entity_Start_Date AS Start_Date
							, Entity_End_Date AS End_Date
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) - ROW_NUMBER() OVER (PARTITION BY Entity_Id,CAST(Contract_Type AS int) ORDER BY Entity_Start_Date) as ChildSeq
					FROM [dbo].[DT_JOB] OuterQuery
					WHERE NOT CAST(ISNULL(Contract_Type, 0) as int) = 0
					AND  (CAST(Contract_Type AS int) = @Right_Join OR @Right_Join IS NULL)
							AND (Entity_Id = @Left_Join OR  @Left_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date

		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Left_Join = OuterQuery.Left_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Left_Join = OuterQuery.Left_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Left_Join = OuterQuery.Left_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Left_Join = OuterQuery.Left_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1

		) a
			
)
'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Course_Provider_Contact_Base]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Course_Provider_Contact_Base]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'
CREATE FUNCTION [dbo].[fn_Course_Provider_Contact_Base]()  
RETURNS @retResults  TABLE (Left_Join int, Right_Join int, Start_Date datetime, End_Date datetime)
AS

BEGIN

	INSERT INTO @retResults

	SELECT DISTINCT 
			DT_COURSE.Entity_Id AS Left_Join, 
			ProviderContacts.ProviderContactID AS Right_Join, 
			ProviderContacts.ProviderSD AS Start_Date, 
			ProviderContacts.ProviderED AS End_Date
	
	FROM DT_COURSE
	INNER JOIN 
	
	(SELECT DTP.Entity_Id as ProviderContactID, DTP.Entity_Start_Date AS ProviderSD, DTP.Entity_End_Date AS ProviderED, CAST(DTP.CoursesTrained as nvarchar(2000)) AS CoursesTrained
	      FROM DT_PROVIDER_CONTACT DTP
	) ProviderContacts
	
	ON    ''%_CS_'' + LTRIM(RTRIM(CAST(ProviderContacts.CoursesTrained as nvarchar(2000)))) + ''_CS_%'' 
	      LIKE 
	      ''%_CS_'' + LTRIM(RTRIM(CAST(DT_COURSE.Entity_Id as nvarchar(2000)))) + ''_CS_%''
	
	OR    LTRIM(RTRIM(CAST(ProviderContacts.CoursesTrained as nvarchar(2000))))
	      = 
	      LTRIM(RTRIM(CAST(DT_COURSE.Entity_Id as nvarchar(2000))))

	RETURN

END


'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Course_Provider_Contact]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Course_Provider_Contact]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'
CREATE FUNCTION [dbo].[fn_Course_Provider_Contact] (@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Left_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Left_Join, ChildSeq, Right_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT Left_Join
							, Right_Join
							, Start_Date
							, End_Date
						, ROW_NUMBER() OVER (PARTITION BY Left_Join ORDER BY Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Left_Join ORDER BY Start_Date) - ROW_NUMBER() OVER (PARTITION BY Left_Join, Right_Join ORDER BY Start_Date) as ChildSeq
					FROM [dbo].fn_Course_Provider_Contact_Base() OuterQuery
					WHERE (Right_Join = @Right_Join OR @Right_Join IS NULL)
							AND (Left_Join = @Left_Join OR @Left_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date

		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Left_Join = OuterQuery.Left_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Left_Join = OuterQuery.Left_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Left_Join = OuterQuery.Left_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Left_Join = OuterQuery.Left_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1
		) a
			
)
'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Course_Provider_Base]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Course_Provider_Base]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'
CREATE FUNCTION [dbo].[fn_Course_Provider_Base] ()  
RETURNS @retResults  TABLE (Left_Join int, Right_Join int, Start_Date datetime, End_Date datetime)
AS

BEGIN

	INSERT INTO @retResults

	SELECT DISTINCT DT_COURSE.Entity_Id AS Left_Join, Providers.ProviderID AS Right_Join, Providers.ProviderSD AS Start_Date, Providers.ProviderED AS End_Date
	
	FROM DT_COURSE
	INNER JOIN 
	
	(SELECT DTP.Entity_Id as ProviderID, DTP.Entity_Start_Date AS ProviderSD, DTP.Entity_End_Date AS ProviderED, CAST(DTP.CoursesTrained as nvarchar(2000)) AS CoursesTrained
	      FROM DT_PROVIDER DTP
	) Providers
	
	ON    ''%_CS_'' + LTRIM(RTRIM(CAST(Providers.CoursesTrained as nvarchar(2000)))) + ''_CS_%'' 
	      LIKE 
	      ''%_CS_'' + LTRIM(RTRIM(CAST(DT_COURSE.Entity_Id as nvarchar(2000)))) + ''_CS_%''
	
	OR    LTRIM(RTRIM(CAST(Providers.CoursesTrained as nvarchar(2000))))
	      = 
	      LTRIM(RTRIM(CAST(DT_COURSE.Entity_Id as nvarchar(2000))))

	UNION
	--courses specified in the Provider Contacts
	SELECT a.Left_Join,  LPPC.Left_Join AS Right_Join, a.Start_Date, a.End_Date
	FROM fn_Course_Provider_Contact(NULL, NULL) a
	INNER JOIN LNK_PROVIDER_PROVIDER_CONTACT LPPC
	ON LPPC.Right_Join = a.Right_Join

	RETURN

END


'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Person_Provider_Contact]'
GO
SET ANSI_NULLS ON
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Person_Provider_Contact]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'


CREATE FUNCTION [dbo].[fn_Person_Provider_Contact] (@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Right_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Right_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Right_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Right_Join, ChildSeq, Left_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT CAST(ISNULL(InternalTrainer, 0) AS int) AS Left_Join
							, Entity_Id AS Right_Join
							, Entity_Start_Date AS Start_Date
							, Entity_End_Date AS End_Date
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) - ROW_NUMBER() OVER (PARTITION BY Entity_Id, CAST(InternalTrainer AS int) ORDER BY Entity_Start_Date) as ChildSeq
					FROM [dbo].[DT_PROVIDER_CONTACT] OuterQuery
					WHERE NOT CAST(ISNULL(InternalTrainer, 0) AS int) = 0
					AND  (CAST(InternalTrainer AS int) = @Left_Join OR @Left_Join IS NULL)
							AND (Entity_Id = @Right_Join OR  @Right_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date

		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Right_Join = OuterQuery.Right_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Right_Join = OuterQuery.Right_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Right_Join = OuterQuery.Right_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Right_Join = OuterQuery.Right_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1

		) a
			
)

'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Session_Course]'
GO
SET ANSI_NULLS OFF
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Session_Course]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'


CREATE FUNCTION [dbo].[fn_Session_Course] (@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Left_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Left_Join, ChildSeq, Right_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT Entity_Id AS Left_Join
							, CAST(ISNULL(Title, 0) AS int) AS Right_Join
							, Entity_Start_Date AS Start_Date
							, Entity_End_Date AS End_Date
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) - ROW_NUMBER() OVER (PARTITION BY Entity_Id, CAST(Title AS int) ORDER BY Entity_Start_Date) as ChildSeq
					FROM [dbo].[DT_SESSION] OuterQuery
					WHERE NOT CAST(ISNULL(Title,0) AS int) = 0
					AND  (CAST(Title AS int) = @Right_Join OR @Right_Join IS NULL)
							AND (Entity_Id = @Left_Join OR  @Left_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date

		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Left_Join = OuterQuery.Left_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Left_Join = OuterQuery.Left_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Left_Join = OuterQuery.Left_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Left_Join = OuterQuery.Left_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1
		) a
			
)



'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Session_Location]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Session_Location]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'

CREATE FUNCTION [dbo].[fn_Session_Location] (@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Left_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Left_Join, ChildSeq, Right_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT Entity_Id AS Left_Join
							, CAST(ISNULL(Location, 0) AS int) AS Right_Join
							, Entity_Start_Date AS Start_Date
							, Entity_End_Date AS End_Date
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) - ROW_NUMBER() OVER (PARTITION BY Entity_Id, CAST(Location AS int) ORDER BY Entity_Start_Date) as ChildSeq
					FROM [dbo].[DT_SESSION] OuterQuery
					WHERE NOT CAST(ISNULL(Location, 0) AS int) = 0
					AND  (CAST(Location AS int) = @Right_Join OR @Right_Join IS NULL)
							AND (Entity_Id = @Left_Join OR  @Left_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date

		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Left_Join = OuterQuery.Left_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Left_Join = OuterQuery.Left_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Left_Join = OuterQuery.Left_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Left_Join = OuterQuery.Left_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1
		) a
			
)
'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Session_Provider]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Session_Provider]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'
CREATE FUNCTION [dbo].[fn_Session_Provider] (@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Left_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Left_Join, ChildSeq, Right_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT Entity_Id AS Left_Join
							, CAST(ISNULL(Provider, 0) AS int) AS Right_Join
							, Entity_Start_Date AS Start_Date
							, Entity_End_Date AS End_Date
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) - ROW_NUMBER() OVER (PARTITION BY Entity_Id, CAST(Provider AS int) ORDER BY Entity_Start_Date) as ChildSeq
					FROM [dbo].[DT_SESSION] OuterQuery
					WHERE NOT CAST(ISNULL(Provider, 0) AS int) = 0
					AND  (CAST(Provider AS int) = @Right_Join OR @Right_Join IS NULL)
							AND (Entity_Id = @Left_Join OR  @Left_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date

		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Left_Join = OuterQuery.Left_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Left_Join = OuterQuery.Left_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Left_Join = OuterQuery.Left_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Left_Join = OuterQuery.Left_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1
		) a
			
)
'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Contract_Contract]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Contract_Contract]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'


CREATE FUNCTION [dbo].[fn_Contract_Contract](@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Right_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Right_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Right_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Right_Join, ChildSeq, Left_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT CAST(ISNULL(Manager, 0) AS int) AS Left_Join
							, Entity_Id AS Right_Join
							, Entity_Start_Date AS Start_Date
							, Entity_End_Date AS End_Date
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) - ROW_NUMBER() OVER (PARTITION BY CAST(Manager AS int), Entity_Id ORDER BY Entity_Start_Date) as ChildSeq
					FROM [dbo].[DT_CONTRACT] OuterQuery
					WHERE  (CAST(Manager AS int) = @Left_Join OR @Left_Join IS NULL)
							AND (Entity_Id = @Right_Join OR  @Right_Join IS NULL)
					AND NOT CAST(ISNULL(Manager, 0) AS int) = 0
			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date

			FROM OuterQuery
			LEFT OUTER JOIN OuterQuery t2
				ON t2.Right_Join = OuterQuery.Right_Join
				AND t2.ChildGrp = OuterQuery.ChildGrp - 1
				AND t2.ChildRowDESC = 1 
			LEFT OUTER JOIN OuterQuery t3
				ON t3.Right_Join = OuterQuery.Right_Join
				AND t3.ChildGrp = OuterQuery.ChildGrp
				AND t3.ChildRowASC = 1
			LEFT OUTER JOIN OuterQuery t4
				ON t4.Right_Join = OuterQuery.Right_Join
				AND t4.ChildGrp = OuterQuery.ChildGrp + 1
				AND t4.ChildRowASC = 1
			LEFT OUTER JOIN OuterQuery t5
				ON t5.Right_Join = OuterQuery.Right_Join
				AND t5.ChildGrp  = OuterQuery.ChildGrp
				AND t5.ChildRowDESC = 1

		) a
)
'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Course_Course_Type]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Course_Course_Type]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'

CREATE FUNCTION [dbo].[fn_Course_Course_Type] (@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Left_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Left_Join, ChildSeq, Right_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT Entity_Id AS Left_Join
							, CAST(ISNULL(CourseType, 0) AS int) AS Right_Join
							, Entity_Start_Date AS Start_Date
							, Entity_End_Date AS End_Date
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) - ROW_NUMBER() OVER (PARTITION BY Entity_Id, CAST(CourseType AS int) ORDER BY Entity_Start_Date) as ChildSeq
					FROM [dbo].[DT_COURSE] OuterQuery
					WHERE NOT CAST(ISNULL(CourseType, 0) AS int) = 0
					AND  (CAST(CourseType AS int) = @Right_Join OR @Right_Join IS NULL)
							AND (Entity_Id = @Left_Join OR  @Left_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date

		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Left_Join = OuterQuery.Left_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Left_Join = OuterQuery.Left_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Left_Join = OuterQuery.Left_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Left_Join = OuterQuery.Left_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1
		) a
			
)
'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Course_Provider]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Course_Provider]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'
CREATE FUNCTION [dbo].[fn_Course_Provider] (@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Left_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Left_Join, ChildSeq, Right_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT Left_Join
							, Right_Join
							, Start_Date
							, End_Date
						, ROW_NUMBER() OVER (PARTITION BY Left_Join ORDER BY Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Left_Join ORDER BY Start_Date) - ROW_NUMBER() OVER (PARTITION BY Left_Join, Right_Join ORDER BY Start_Date) as ChildSeq
					FROM [dbo].fn_Course_Provider_Base() OuterQuery
					WHERE (Right_Join = @Right_Join OR @Right_Join IS NULL)
							AND (Left_Join = @Left_Join OR @Left_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date

		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Left_Join = OuterQuery.Left_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Left_Join = OuterQuery.Left_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Left_Join = OuterQuery.Left_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Left_Join = OuterQuery.Left_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1
		) a
			
)
'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating [dbo].[fn_Attendance_Session]'
GO
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_Attendance_Session]') AND (type = 'IF' OR type = 'FN' OR type = 'TF'))
EXEC sp_executesql N'
CREATE FUNCTION [dbo].[fn_Attendance_Session] (@Left_Join int, @Right_Join int)  
RETURNS TABLE 
AS
RETURN 
(
	WITH 
	OuterQuery AS
	(
		SELECT Left_Join, Right_Join, Start_Date, End_Date
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date) AS ChildRowASC
				, ROW_NUMBER() OVER (PARTITION BY Left_Join, ChildGrp ORDER BY Start_Date DESC) AS ChildRowDESC
				, DENSE_RANK() OVER (PARTITION BY Left_Join ORDER BY ChildGrp) as ChildGrp
		FROM (

			SELECT Left_Join, Right_Join, Start_Date, End_Date
					, ParentRows - RANK() OVER (PARTITION BY Left_Join, ChildSeq, Right_Join ORDER BY Start_Date) as ChildGrp
			FROM  (    
					SELECT Entity_Id AS Left_Join
							, CAST(Session AS int) AS Right_Join
							, Entity_Start_Date AS Start_Date
							, Entity_End_Date AS End_Date
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) AS ParentRows
						, ROW_NUMBER() OVER (PARTITION BY Entity_Id ORDER BY Entity_Start_Date) - ROW_NUMBER() OVER (PARTITION BY Entity_Id, CAST(Session AS int) ORDER BY Entity_Start_Date) as ChildSeq
					FROM [dbo].[DT_ATTENDANCE] OuterQuery
					WHERE NOT CAST(ISNULL(Session, 0) AS int) = 0
					AND  (CAST(Session AS int) = @Right_Join OR @Right_Join IS NULL)
							AND (Entity_Id = @Left_Join OR  @Left_Join IS NULL)

			) f2
		) f1
	)
	SELECT ROW_NUMBER() OVER (ORDER BY Left_Join, Start_Date) AS Ndx
			, Left_Join
			, Right_Join
			, Start_Date
			, End_Date
	FROM
		(SELECT DISTINCT OuterQuery.Left_Join
						, OuterQuery.Right_Join
						, COALESCE(DATEADD(d, 1, t2.End_Date), t3.Start_Date, OuterQuery.Start_Date) AS Start_Date
						, COALESCE(DATEADD(d, -1, t4.Start_Date), t5.End_Date) AS End_Date

		FROM OuterQuery
		LEFT OUTER JOIN OuterQuery t2
			ON t2.Left_Join = OuterQuery.Left_Join
			AND t2.ChildGrp = OuterQuery.ChildGrp - 1
			AND t2.ChildRowDESC = 1 
		LEFT OUTER JOIN OuterQuery t3
			ON t3.Left_Join = OuterQuery.Left_Join
			AND t3.ChildGrp = OuterQuery.ChildGrp
			AND t3.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t4
			ON t4.Left_Join = OuterQuery.Left_Join
			AND t4.ChildGrp = OuterQuery.ChildGrp + 1
			AND t4.ChildRowASC = 1
		LEFT OUTER JOIN OuterQuery t5
			ON t5.Left_Join = OuterQuery.Left_Join
			AND t5.ChildGrp  = OuterQuery.ChildGrp
			AND t5.ChildRowDESC = 1
		) a
			
)
'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
SET ANSI_NULLS ON
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
	PRINT 'The database update failed'
END
GO