How can I update a value in a table using a condition on values in another table in Sql server

Question

We have 2 tables in my project, WorkOrders and WorkOrderTrackings and WorkOrderTrackings has a Foreign Key WorkOrderId that references Id in WorkOrders (so you know what work order a tracking record belongs to). The records in WorkOrderTrackings represent the "steps" a work order goes through from start to finish. They are created once our engineering department releases the work order to the plant floor for building. So any work order can have 1 to many work order trackings.

Now, WorkOrderTrackings has a StageNum field that states the step# for the step in order from start to finish. has a DateTime? StartDate and DateTime? CompleteDate fields. When they start a step, the startDate gets set, when they finish, the completeDate gets set. When it has finished 1 step and has not yet started the next one, the previous step completedate has a value, but the next step startdate does not.

So currently, to determine what step a work order X is at, we use the linq expression:

var trackingsForX=db.WorkOrderTrackings.Where(wot=>wot.WorkOrderId==X.Id);
var currentTrackingForX=trackingsForX.OrderByDescending(t=>t.StageNum)
                         .Where(t=>t.StartDate.HasValue && !t.CompleteDate.HasValue)
                         .FirstOrDefault() ??
                    trackingsForX.OrderBy(t=>t.StageName)
                         .Where(t=>!t.CompleteDate.HasValue)
                         .FirstOrDefault()

this selects the most recent tracking step for the work order by returning the first tracking record found with a startdate value but no completedate when searching the trackings from last to first stagenum, or, if that returns null, by finding the first record with no completedate value by searching from first step to last step.

We dont want to do this every time, so we are adding a field CurrentTrackingStepId to WorkOrders that will be a foreign key to the most recent tracking record for the work order. As a new step starts, we will update the CurrentTrackingStepId for the work order to point to that step. This way, we can always access the current step of a work order by just looking up the work order. However, now I need to make a sql script that we can run that will automatically set CurrentTrackingStepId for all existing Work orders to the current tracking Id for that order.

How would I write a sql script to accomplish this?

EDIT: Update with sample db data


dbo.WorkOrderTrackings:

ID      WO-Id StageNum          StartDate                CompleteDate
551874  53781   1       2020-08-28 5:13:34 PM       2020-08-28 5:13:45 PM    
551875  53781   2       2020-08-28 7:01:35 PM       2020-08-28 7:01:35 PM    
551878  53781   5       2020-08-28 7:01:35 PM       NULL    
551879  53781   6       NULL                        NULL    
551880  53781   7       NULL                        NULL    
551881  53781   8       NULL                        NULL    
551882  53781   9       NULL                        NULL
551883  53781   10      NULL                        NULL    
551884  53781   11      NULL                        NULL
551885  53781   12      NULL                        NULL
551886  53781   13      NULL                        NULL
551887  53781   14      NULL                        NULL
551888  53781   15      NULL                        NULL
551889  53781   16      NULL                        NULL
551890  53781   17      NULL                        NULL
551891  53803   1       2020-08-28 6:33:18 PM       2020-08-28 6:33:18 PM   
551892  53803   2       2020-08-28 6:33:18 PM       2020-08-31 8:07:09 AM   
551893  53803   3       2020-08-31 8:07:17 AM       2020-08-31 8:07:24 AM   
551894  53803   4       NULL                        NULL
551895  53803   5       NULL                        NULL
551896  53803   6       NULL                        NULL
551897  53803   7       NULL                        NULL
551908  53803   18      NULL                        NULL
551909  53803   19      NULL                        NULL
551910  53803   20      NULL                        NULL
551911  53803   21      NULL                        NULL
551912  53803   22      NULL                        NULL
551913  53803   23      NULL                        NULL
551914  53803   24      NULL                        NULL
551915  53803   25      NULL                        NULL
551916  53803   26      NULL                        NULL


dbo.WorkOrders:

Id       OrderNumber
53803   139107-00
53781   139094-00

So if you go by the data above, if I wanted the current tracking step for order 139107-00, it should return WorkOrderTracking record with Id 551894 (because this is the record immediately after the most recent completed one). If I want the current tracking step for order 139094-00, it should return WorkOrderTracking record with Id 551878 (because this is the step it has started but not finished yet).

So, from the data above, I want to set the new CurrentTrackingStepId field in WorkOrders to the Id of the most recent tracking record for that order in WorkOrderTrackings that has a StartDate value but no CompleteDate value, OR, if that returns null, I want the Id of the record for the next StageNum after the last one that has both a StartDate value and a CompleteDate value. Does that make sense?

I don't really have any scripts I've already tried because I can't get my head around how to write it. I realize I need an Update for dbo.WorkOrders to set CurrentTrackingStepId=...something. That something is going to need to have a select statement to get the Id from the WorkOrderTrackings table of the correct record. It will likely need an EXIST statement to check if there is a record with a start date but no complete date.

user_3391334 · Answer

Assuming your StageNum values are sequential, you could do the following:

/* Table and data mock-up */

DECLARE @WorkOrders table ( Id int, OrderNumber varchar(10), CurrentStage int );
INSERT INTO @WorkOrders ( Id, OrderNumber ) VALUES
    ( 53803, '139107-00' ), ( 53781, '139094-00' );

DECLARE @WorkOrderTrackings table (
    ID int, [WO-id] int, StageNum int, StartDate datetime, CompleteDate datetime
);
INSERT INTO @WorkOrderTrackings VALUES
( 551891, 53803, 1, '2020-08-28 6:33:18 PM', '2020-08-28 6:33:18 PM' ),   
( 551892, 53803, 2, '2020-08-28 6:33:18 PM', '2020-08-31 8:07:09 AM' ),   
( 551893, 53803, 3, '2020-08-31 8:07:17 AM', '2020-08-31 8:07:24 AM' ),
( 551894, 53803, 4, NULL, NULL ),
( 551895, 53803, 5, NULL, NULL ),
( 551896, 53803, 6, NULL, NULL ),
( 551897, 53803, 7, NULL, NULL );

/* Update the current stage of the WorkOrder based on the last completed StageNum + 1 */

UPDATE @WorkOrders
SET
    CurrentStage = wt.CurrentStageID
FROM @WorkOrders wo
OUTER APPLY (

    SELECT ID AS CurrentStageID FROM @WorkOrderTrackings wt
    WHERE
        wt.[WO-id] = wo.Id AND wt.StageNum = (
            SELECT ( MAX( StageNum ) + 1 ) FROM @WorkOrderTrackings x WHERE x.[WO-id] = wt.[WO-id]
                AND x.CompleteDate IS NOT NULL
        )

) AS wt
WHERE
    wo.[OrderNumber] = '139107-00';

/* Show the updated WorkOrders resultset */

SELECT * FROM @WorkOrders ORDER BY Id;

Returns

+-------+-------------+--------------+
|  Id   | OrderNumber | CurrentStage |
+-------+-------------+--------------+
| 53781 | 139094-00   | NULL         |
| 53803 | 139107-00   | 551894       |
+-------+-------------+--------------+

UPDATE:

I like the approach, but notice that StageNum isn't contiguous (1,2,5,6,7 in the first series).

This example uses LEAD as in Gert's approach.

/* Update the current stage of the WorkOrder based on the last stage completed */

UPDATE @WorkOrders
SET
    CurrentStage = wt.CurrentStageID
FROM @WorkOrders wo
OUTER APPLY (

    SELECT TOP 1
        LEAD( ID, 1, NULL ) OVER ( PARTITION BY [WO-id] ORDER BY ID, StageNum ) AS CurrentStageID 
    FROM @WorkOrderTrackings wt
    WHERE
        wt.[WO-id] = wo.Id AND wt.ID >= (
            SELECT MAX( ID ) FROM @WorkOrderTrackings x WHERE x.[WO-id] = wt.[WO-id]
                AND x.CompleteDate IS NOT NULL
        )
    ORDER BY
        wt.ID, wt.StageNum

) AS wt
WHERE
    wo.[OrderNumber] = '139107-00';

Returns

+-------+-------------+--------------+
|  Id   | OrderNumber | CurrentStage |
+-------+-------------+--------------+
| 53781 | 139094-00   | NULL         |
| 53803 | 139107-00   | 551894       |
+-------+-------------+--------------+

user_861716 · Answer

SQL Server's window function LEAD gives access to data in other rows in one SELECT statement. In your case, a statement like...

SELECT *
FROM
(
    SELECT *
    ,LEAD(ID, 1) OVER (PARTITION BY [WO-Id] ORDER BY StageNum) [NextId]
    ,LEAD(StageNum, 1) OVER (PARTITION BY [WO-Id] ORDER BY StageNum) [NextStageNum]
    ,LEAD(StartDate, 1) OVER (PARTITION BY [WO-Id] ORDER BY StageNum) [NextStartDate]
    ,LEAD(CompleteDate, 1) OVER (PARTITION BY [WO-Id] ORDER BY StageNum) [NextCompleteDate]
    FROM dbo.WorkOrderTrackings
) d
WHERE CompleteDate IS NOT NULL
AND [NextCompleteDate] IS NULL

...returns:

ID     WO-id StageNum StartDate           CompleteDate        NextId NextStageNum NextStartDate       NextCompleteDate
------ ----- -------- ------------------- ------------------- ------ ------------ ------------------- ----------------
551875 53781 2        2020-08-28 19:01:35 2020-08-28 19:01:35 551878 5            2020-08-28 19:01:35 NULL
551893 53803 3        2020-08-31 08:07:17 2020-08-31 08:07:24 551894 4            NULL                NULL

Just to show you what it does. The first LEAD statement basically says, "return the first-next ID in a group of identical WO-ids when ordered by StageNum". This PARTITION BY part is important to prevent NextId from spilling over from a next group of orders.

For you it's enough to return [WO-Id], NextId from this query and remove the redundant LEAD statements, to get the data you need:

UPDATE wo
SET CurrentStage = nextStage.NextId
FROM dbo.WorkOrders wo
INNER JOIN
(
    SELECT [WO-Id], NextId
    FROM
    (
        SELECT *
        ,LEAD(ID, 1) OVER (PARTITION BY [WO-Id] ORDER BY StageNum) [NextId]
        ,LEAD(CompleteDate, 1) OVER (PARTITION BY [WO-Id] ORDER BY StageNum) [NextCompleteDate]
        FROM dbo.WorkOrderTrackings
    ) d
    WHERE CompleteDate IS NOT NULL
    AND [NextCompleteDate] IS NULL
    AND NextId IS NOT NULL
) nextStage
ON wo.Id = nextStage.[WO-id]

How can I update a value in a table using a condition on values in another table in Sql server

2 Answers