Reputation: 33
How can I update a value in a table using a condition on values in another table in Sql server
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.
Upvotes: 2
Views: 98
Answers (2)
Reputation: 6706
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
StageNumisn'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 |
+-------+-------------+--------------+
Upvotes: 2
Reputation: 109119
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]
Upvotes: 1
Related Questions
- Update only the given values (Entity Framework)
- How to Update Column based on another table column value
- Update table column based on comparison of columns from 2 table (SQL)
- How to update a table with values from another table based on different conditions
- Update Operation in LINQ to SQL
- update one table depend on the value of another table
- How to update multiple sql rows where table 1's ID is equal to table 2's ID
- Update two columns values in a table based on another column value in different table
- Update row in Entity Framework based on input data
- update a field value according to another in ef