Jointure SQL dans un CTE récursif avec parameters

J'essaie d'utiliser une requête SQL pour créer des rapports côté client pour mon entreprise. Il existe 3 tables que j'aimerais joindre set. Une des tables peut nécessiter un CTE car j'ai besoin de parcourir de manière récursive une table et de returnner une ligne. Voici comment les arrays sont structurés (simplement).

Je veux une table de sortie qui, pour chaque WorkOrder, affiche la dernière tâche terminée dans DataCollection (y compris son heure) et l'opération suivante dans la list des tâches. J'ai pensé qu'un CTE est peut-être le seul moyen de parcourir chaque rangée de façon récursive et de déterminer quelle tâche est la suivante. (En vérifiant si l'Op complété existe dans la colonne PreOp). Si la cellule terminée n'existe pas en tant que preOp, elle doit par défaut être au MAX (Op) (la dernière tâche).

CREATE TABLE [dbo].[WorkOrder]( [WorkOrderID][int] NOT NULL PRIMARY KEY, [Column1] [nvarchar](20), [Column2] [nvarchar](20) ) INSERT INTO WorkOrder VALUES(1,'x','y'); INSERT INTO WorkOrder VALUES(2,'x','y'); INSERT INTO WorkOrder VALUES(3,'x2','y2'); CREATE TABLE [dbo].[DataCollection]( [DataCollection][int] NOT NULL PRIMARY KEY, [WorkOrderID][int] NOT NULL FOREIGN KEY REFERENCES WorkOrder(WorkOrderID), [CellTask] [nvarchar](20), [TimeCompleted] [DateTime] ) INSERT INTO DataCollection VALUES(1,1,'cella','2016-08-09 00:00:00'); INSERT INTO DataCollection VALUES(2,1,'cellb','2016-08-10 00:00:00'); INSERT INTO DataCollection VALUES(3,1,'cellc','2016-08-11 00:00:00'); INSERT INTO DataCollection VALUES(4,2,'cella','2016-08-09 00:00:00'); INSERT INTO DataCollection VALUES(5,2,'cellb','2016-08-10 00:00:00'); CREATE TABLE [dbo].[TaskListing]( [TaskListingID][int] NOT NULL PRIMARY KEY, [WorkOrderID][int] NOT NULL FOREIGN KEY REFERENCES WorkOrder(WorkOrderID), [Op][nvarchar](20) NOT NULL, [preOP][nvarchar](20), [CellTask][nvarchar](20) NOT NULL, [Completed][bit] NOT NULL ) INSERT INTO TaskListing VALUES(1,1,'10',NULL,'cella',0); INSERT INTO TaskListing VALUES(2,1,'20','10','cellb',0); INSERT INTO TaskListing VALUES(3,1,'30',NULL,'cellc',1); INSERT INTO TaskListing VALUES(4,1,'40','10,30','celld',0); INSERT INTO TaskListing VALUES(5,2,'10',NULL,'cella',1); INSERT INTO TaskListing VALUES(6,2,'20','10','cellb',1); INSERT INTO TaskListing VALUES(7,2,'30','20','cellc',0); 

La table de sortie représentera, pour chaque WorkOrder, la dernière cellule terminée (depuis la table DataCollection, la colonne TimeCompleted) et la cellule suivante du workflow (en examinant les lignes de la table TaskListing pour le WorkOrderID donné et en recherchant un ligne contenant la tâche terminée en tant que 'PreOp'). Si elle ne peut pas find la tâche terminée comme preOp pour une autre ligne, elle doit être la dernière par défaut.

La partie de la requête avec laquelle j'ai le plus de problèmes est de remplir la colonne NextTaskCell. J'ai besoin d'écrire une requête qui regarde toutes les tâches pour un WorkOrderID donné (dans la Table TaskListing) et en fonction de la tâche terminée, détermine quelle est la tâche suivante. Je trouve qu'il est difficile de charger à la fois un WorkOrderID et une CellTask ​​puis de find une instance de lui-même dans la colonne PreOp.

• C # insère des données de datatable à la database SQL Server
• Pourquoi le server Sql 2014 fonctionne-t-il très lentement lors de la vérification des dates?
• Est-il possible de définir l'indicateur de table ROWLOCK à partir d'un niveau d'isolation de transaction dans MSSQL?
• Comment dupliquer des données en sql avec des conditions
• Mettre à jour l'élément de la même table où les valeurs sont null

Table de sortie

 +-------------+-------------------+---------------------+--------------+ | WorkOrderId | LastCompletedCell | CompletedOn | NextTaskCell | |(WorkOrder) | (DataCollection) | (DataCollection) |(TaskListing) | +-------------+-------------------+---------------------+--------------+ | 1 | cellc | 2016-08-11 00:00:00 | celld | | 2 | cellb | 2016-08-10 00:00:00 | cellc | +-------------+-------------------+---------------------+--------------+ 

Je vous remercie d'avance pour votre time. S'il y a d'autres questions, faites-le moi savoir et j'essaierai d'y répondre.

Lien vers SQL Fiddle SQL Fiddle

• SQL Server XQuery.modify "Erreur de syntaxe près de ':', attendu '}'"
• Sauvegardes manquantes SQL Server
• Identifiant principal de search de plusieurs tables ayant un autre champ (unique)
• Sql Server 2016 Performance est terrible en faisant des inserts très simples
• Comment puis-je simplifier cette requête SQL?

 ;WITH mtc AS ( -- most recent completion date/time for a work order SELECT dc.WorkOrderID, TimeCompleted=MAX(dc.TimeCompleted) FROM DataCollection AS dc GROUP BY dc.WorkOrderID ), lop AS ( -- last operation for work order SELECT tl.WorkOrderID, LastOp=MAX(CAST(tl.Op AS INT)) FROM TaskListing AS tl GROUP BY tl.WorkOrderID ) SELECT mtc.WorkOrderID, LastCompletedCell=dc.CellTask, CompletedOn=dc.TimeCompleted, NextTaskCell=ISNULL(tl_next.CellTask,tl_last.CellTask) FROM mtc INNER JOIN DataCollection AS dc ON -- the last completed CellTask dc.WorkOrderID=mtc.WorkOrderID AND dc.TimeCompleted=mtc.TimeCompleted INNER JOIN TaskListing AS tl ON -- Op for CellTask tl.WorkOrderID=mtc.WorkOrderID AND tl.CellTask=dc.CellTask INNER JOIN lop ON lop.WorkOrderID=mtc.WorkOrderID INNER JOIN TaskListing AS tl_last ON -- CellTask for last Op tl_last.WorkOrderID=mtc.WorkOrderID AND tl_last.Op=lop.LastOp LEFT JOIN TaskListing AS tl_next ON -- Look for next CellTask where Op is a PreOp of another CellTask tl_next.WorkOrderID=mtc.WorkOrderID AND ','+tl_next.preOP+',' LIKE '%,'+tl.Op+',%' ORDER BY mtc.WorkOrderId;