SQL Add Lead Time Day to MAX Ordered Date

I have a Material table and I want to retrieve the latest Received Date, Expected Date and Order Date + Lead Time Days for a given order. So far I have the following that retrieves the MAX three dates, but I don't know how to add the lead time to the Ordered Date as well as retrieve the lead time for the MAX Ordered Date:

Select Top (1) Material.OrderID, LatestExpectedDate, LatestReceivedDate, LatestOrderedDate
from Material
left outer join (Select T1.OrderID, Max(T1.MaterialExpectedDate) as LatestExpectedDate from Material T1 where T1.FloorStock=0 and T1.MaterialAvailable=0
  Group by T1.OrderID) Tbl1 on Material.OrderID=Tbl1.OrderID
left outer join (Select T2.OrderID, Max(T2.MaterialReceivedDate) as LatestReceivedDate from Material T2 where T2.FloorStock=0 and T2.MaterialAvailable=0
  Group by T2.OrderID) Tbl2 on Material.OrderID=Tbl2.OrderID
left outer join (Select T3.OrderID, Max(T3.MaterialOrderedDate) as LatestOrderedDate from Material T3 where T3.FloorStock=0 and T3.MaterialAvailable=0
  Group by T3.OrderID) Tbl3 on Material.OrderID=Tbl3.OrderID

where Material.OrderID=*SomeGivenOrder*

I want to both retrieve the Lead Time for the latest MaterialOrderedDate as well as add the Lead Time in days to the MAX(MaterialOrderDate). Not being a SQL programmer I am stuck. If there is a better way to doing things, I am open to all suggestions.

Example data

OrderID FloorStock  LeadTime    MaterialAvailable   OrderedDate  ExpectedDate   ReceivedDate    
80         0        10                     0        7/01/22         8/23/22         8/23/22
80         0        5                      0        7/05/22         9/05/22
80         0        20                     0        7/10/22
106        1        0                      1            
148        0        15                     0        8/01/22

From the above if I asked for OrderID=80 I would expect to get

Order Date: 7/30/22 (7/10/22 + 20 day lead time)

Expected Date: 9/05/22

Received Date: 8/23/22

Thanks for the help!

Solution

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