SQL Server Update Field Max Value Subset of Data

Question

I'm trying to update a filed based on a subset of data within the table. If an assembly requires 3 components that have varying arrival dates, but can only be assembled once all three have arrived, how do I update the completed field? The Complete field would need to be the Max of the DateAdd between the like assembly, but I don't know how to define the WHERE statement to compare based on assembly.

Sample Data

SN      Assembly  Arrival       ProcessingDays
1000    A1        2017-07-03    2               
1001    A1        2017-06-02    2
1002    A1        2017-05-01    2
1003    A2        2017-12-15    2  
1004    A3        2017-07-03    2
1005    A3        2017-06-02    2
1006    A3        2017-05-01    2

Desired Output

SN      Assembly  Arrival       ProcessingDays  Complete
1000    A1        2017-07-03    2               2017-07-05
1001    A1        2017-06-02    2               2017-07-05
1002    A1        2017-05-01    2               2017-07-05
1003    A2        2017-12-15    2               2017-12-17
1004    A3        2017-09-03    2               2017-09-05
1005    A3        2017-08-02    2               2017-09-05
1006    A3        2017-07-01    2               2017-09-05

Answer 1

I don't know if you really want to update your table or just generate the Complete column, but the following CTE should be on the right track. The trick baked into the cake with your question is that the number of processing days for the most recent record of each assembly also has to be identified, in addition to the max date. For this, I use a two step CTE below.

WITH cte1 AS (
    SELECT SN, Assembly, Arrival, ProcessingDays,
        ROW_NUMBER() OVER (PARTITION BY Assembly ORDER BY Arrival DESC) rn
    FROM yourTable
),
cte2 AS (
    SELECT t1.SN, t1.Assembly, t1.Arrival, t1.ProcessingDays, t1.Complete,
        DATEADD(dd, t2.ProcessingDays, t2.Arrival) AS NewComplete
    FROM yourTable t1
    INNER JOIN cte1 t2
        ON t1.Assembly = t2.Assembly
    WHERE
        t2.rn = 1
)

CTEs are useful in so many ways, including making it easy to update a table using complex logic, but in an easy way. Now we can update your Complete column as follows:

UPDATE cte2
SET Complete = NewComplete;

This is a blanket update, which is what your question seems to imply what you want, but you are free to add a WHERE clause to the above update if that makes sense.