Just for S and G's I thought I'd throw in a solution. sorry for not commenting it.

DECLARE @NumberOfMonths INT
        , @CurrentDate DATE;

SELECT @NumberOfMonths = 24,...

Hope this works for you, any questions be sure to ask:

WITH BaseMaterial AS (
SELECT
  [elwu].[Material] AS MasterMaterial
  , [elwu].[MaterialDescription]
  , [mwu].[ID]
 ...

Question now, do we start with TableB ([dbo].[EndLvlWhereUsed]) and use it to query...

Nope, still confused since a simple inspection of the data still shows everything you need is still in the record where ID = 3 and Material = YHE30B21S.  Not seeing...

I am confused.  Your expected results are already in the second table, [dbo].[EndLvlWhereUsed] as well being in the table [dbo].[MaterialWhereUsed] in the data row with ID = 3.
So please,...

It also comes down to the DBA controlling when the database is grown instead of relying on auto-growth to handle expanding the databases.  This is one of the reasons production...