WITH MendixOrdersWithPrevious AS (
	SELECT *,
		LAG(CASE 
				WHEN ActualResourceName IS NOT NULL THEN ActualResourceName
				ELSE ResourceName
			END
		) OVER (PARTITION BY OrderNo ORDER BY OpNo) AS PreviousResource,
		CASE 
			WHEN ActualResourceName IS NOT NULL THEN ActualResourceName
			ELSE ResourceName
		END AS ResourceUsage
	FROM [FAI-MendixDB].dbo.MendixOrders
),
SubsequentResources AS (
	SELECT
		P.PartNo,
		P.Product,
		P.OpNo,
		P.OperationName AS fromOperation,
		P.Plant,
		P.Revisions,
		RG.Name AS ResourceGroup,
		R.Name AS ResourceName,
		R1.Name AS SubsequentResource,
		P1.OperationName AS SubsequentOperation
	FROM 
		[FAI-ASDB-DEV].UserData.Products P
		INNER JOIN [FAI-ASDB-DEV].UserData.ResourceGroups RG
			ON RG.ResourceGroupsId = P.ResourceGroup
		INNER JOIN [FAI-ASDB-DEV].UserData.ResourceGroupsResources RGR
			ON RGR.ResourceGroupsId = RG.ResourceGroupsId
		INNER JOIN [FAI-ASDB-DEV].UserData.Resources R
			ON R.ResourcesId = RGR.Resources
		INNER JOIN [FAI-ASDB-DEV].UserData.ProductsResourceData PRD
			ON PRD.ProductsId = P.ProductsId
			AND PRD.ResourceData = R.ResourcesId
		LEFT JOIN [FAI-ASDB-DEV].UserData.ResourceGroupsResources RGR1
			ON RGR1.ResourceGroupsId = PRD.SetSubsequentResourceGroup
		LEFT JOIN [FAI-ASDB-DEV].UserData.Resources R1
			ON R1.ResourcesId = RGR1.Resources
		LEFT JOIN [FAI-ASDB-DEV].UserData.Products P1
			ON P.PartNo = P1.PartNo
			AND P.__seq__Products + 1 = P1.__seq__Products
			AND P.Revisions = P1.Revisions
			AND P.Plant = P1.Plant
),
MendixOrdersSubsequentOperation AS (
	SELECT
		MO.OrderNo,
		MO.PartNo,
		MO.OperationName,
		MO.ResourceUsage,
		MO.Plant,
		CASE WHEN MO.PreviousResource IS NULL AND MSFG.FromResource IS NOT NULL THEN MSFG.FromResource
			ELSE MO.PreviousResource
			END AS PreviousResource

	FROM MendixOrdersWithPrevious MO
	LEFT JOIN SubsequentResources SR
		ON SR.PartNo = MO.PartNo
		AND MO.OperationName = SR.fromOperation
		AND MO.ResourceUsage = SR.ResourceName
		AND MO.Plant = SR.Plant
	LEFT JOIN [FAI-MendixDB].dbo.MendixSFGMaterial MSFG
		ON MSFG.ToOrderNo=MO.OrderNo
		AND MSFG.ToOpName=MO.OperationName
)
--select * from MendixOrdersSubsequentOperation

SELECT DISTINCT
	MOS.OrderNo,
	MOS.PartNo,
	MOS.OperationName,
	--MOS.ResourceUsage CurrentResource,
	MOS.PreviousResource PreviousResourceOperation,
	CASE WHEN SR.SubsequentResource IS NULL THEN SR1.ResourceName
		ELSE SR.SubsequentResource
		END AS ResourcesList
FROM 
	MendixOrdersSubsequentOperation MOS
	LEFT JOIN SubsequentResources SR
		ON SR.PartNo=MOS.PartNo
		AND SR.Plant=MOS.Plant
		AND SR.ResourceName=MOS.PreviousResource
		AND SR.SubsequentOperation=MOS.OperationName

	LEFT JOIN SubsequentResources SR1
		ON SR1.PartNo=MOS.PartNo
		AND SR1.fromOperation=MOS.OperationName
		AND SR1.Plant=MOS.Plant
		
WHERE MOS.OrderNo=100154769 
ORDER BY OrderNo