Thanks for all your responses.

I wanted to avoid creating a temp table for this process so I have opted to use the

SELECT LocationId, MaterialId, Duration

FROM (

SELECT LocationId, MaterialId,...