SQL Server 2016 modifying column based on other record

Question

I have an issue calculating the start date that a product can start being assembled based on when it arrives and when there is the capacity to begin. This is assuming only one product/part can be assembled at a time at a given location. There is no specific order that parts have to arrive to begin assembly.

Data Available:

Product  Part  HardwareArrival Location    AssemblyDays 
A        1A    2018-01-01      Facility A  12          
A        2A    2018-01-02      Facility A  12            
A        3A    2018-01-03      Facility A  12             
B        1A    2018-01-04      Facility B  9              
B        2A    2018-01-05      Facility B  9              
B        3A    2018-01-06      Facility B  9             

Desired Result:

Product  Part  HardwareArrival Location    AssemblyDays   StartOfAssembly
A        1A    2018-01-01      Facility A  12             2018-01-01
A        2A    2018-01-02      Facility A  12             2018-01-13
A        3A    2018-01-03      Facility A  12             2018-01-25
B        1A    2018-01-04      Facility B  9              2018-01-04
B        2A    2018-01-05      Facility B  9              2018-01-13
B        3A    2018-01-06      Facility B  9              2018-01-22

Answer 1

You can do it using a recursive CTE:

;WITH RnCTE AS (
    -- Assign a row number to each part within each 'Location' slice
    SELECT Product, Part, HardwareArrival, Location, AssemblyDays,
           ROW_NUMBER() OVER (PARTITION BY Location ORDER BY HardwareArrival) AS rn
    FROM mytable
), StartOfAssemblyCTE AS (
    -- Anchor member: get the first part to be assembled for each 'Location'
    SELECT Product, Part, HardwareArrival, Location, AssemblyDays, 
           rn AS level,
           HardwareArrival AS StartOfAssembly
    FROM RnCTE
    WHERE rn = 1

    UNION ALL

    -- Recursive member: Get next start date
    SELECT t1.Product, t1.Part, t1.HardwareArrival, t1.Location, t1.AssemblyDays, 
           t1.rn AS level,
           -- If hardware arrival for current part is later than 'start-of-assembly'
           -- calculated based on AssemblyDays and preceding start date, 
           -- then select hardware arrival date as starting date
           CASE 
              WHEN x.StartOfAssembly < t1.HardwareArrival THEN t1.HardwareArrival
              ELSE StartOfAssembly 
           END AS StartOfAssembly 
    FROM RnCTE AS t1
    JOIN StartOfAssemblyCTE AS t2 ON t1.Location = t2.Location AND t1.rn = t2.level + 1
    CROSS APPLY (SELECT DATEADD(dd, 
                                t1.AssemblyDays, 
                                t2.StartOfAssembly)) AS x(StartOfAssembly)
    WHERE rn = level + 1
)
SELECT Product, Part, HardwareArrival, Location, AssemblyDays, StartOfAssembly
FROM StartOfAssemblyCTE
ORDER BY Location, HardwareArrival

Demo here