SQL Add Lead Time Day to MAX Ordered Date

Pretty sure you can greatly simplify all these subqueries into a single query. You really don't need a separate query for each column. Something like this should be what you are looking for.

For the lead time you want to add you can simply use DATEADD.

Select m.OrderID
    , LatestExpectedDate = Max(m.MaterialExpectedDate)
    , LatestReceivedDate = Max(m.MaterialReceivedDate)
    , LatestOrderedDate = Max(m.MaterialOrderedDate)
from Material m
where m.OrderID = *SomeGivenOrder*
group by m.OrderID

with data as (
    select *, first_value(LeadTime) over (order by OrderedDate desc) as last_LeadTime
    from Material
    where OrderID = ? and FloorStock = 0 and MaterialAvailable = 0
)
select
    dateadd(day, min(last_LeadTime), max(MaterialOrderedDate)) as OrderDate,
    max(MaterialExpectedDate) as ExpectedDate,
    max(MaterialReceivedDate) as ReceivedDate
from data;

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=0af7cb9c156545f710fb169e2dbceb0c

This an approach can still be salvaged to accommodate the slight change in requirement to order on the date expression. If the data is large then it may still be beneficial to avoid the scalar subquery.

While I don't know the data there would appear to be some potential for two or more rows to tie for the ranking by order date plus lead time. In those instances I'm not sure what meaning to attach to the returned lead time value or whether to favor one over another.

with data as (
    select *,
        row_number() over (order by dateadd(day, LeadTime, OrderedDate) desc,
                                    LeadTime desc) as rn
    from Material
    where OrderID = ? and FloorStock = 0 and MaterialAvailable = 0
)
select
    min(case when rn = 1 then dateadd(day, LeadTime, OrderedDate) end) as OrderedDate,
    min(case when rn = 1 then LeadTime end) as SelectedLeadTime,
    max(ExpectedDate) as ExpectDate,
    max(ReceivedDate) as ReceivedDate
from data;

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=df29081d9b0a769c55175c80aa692f72

What I thought was the correct answer to the problem did not work when I tested the SQL with a larger material table. The problem I was getting was that the incorrect Ordered Date and/or Lead Time were being reported for certain items. This problem had to do with the order of material items and what Ordered Date/Leadtime was being returned.

What I am now testing (based on Sean Lange's suggestion... and it appears so far to give the correct results) is as follows:

Select m.OrderID
    , LatestExpectedDate = Max(m.MaterialExpectedDate)
    , LatestReceivedDate = Max(m.MaterialReceivedDate)
    , LatestOrderedDate  = Max(DateAdd(Day, m.LeadTime, m.MaterialOrderedDate))
    , (SELECT Top (1) m1.LeadTime FROM OpMaterial m1 WHERE OrderID=Some_OrderID Order By DateAdd(Day, m1.LeadTime, m1.MaterialOrderedDate) desc ) As SelectedLeadTime
    , (SELECT Top (1) m2.MaterialOrderedDate FROM OpMaterial m2 WHERE OrderID=Some_OrderID Order By DateAdd(Day, m2.LeadTime, m2.MaterialOrderedDate) desc ) As SelectedOrderedDate
    from OpMaterial m
where m.OrderID = Some_OrderID and m.MaterialAvailable=0 and m.FloorStock=0
group by m.OrderID

The second sub query is there for testing purposes only so I can verify the ordered date and lead time are giving me the correct results. It will be removed in the final code.