SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

set dateformat mdy;
CREATE TABLE Material (
  OrderID int,
  FloorStock int,
  LeadTime int,
  MaterialAvailable int,
  OrderedDate date,
  ExpectedDate date,
  ReceivedDate date
);

INSERT INTO Material
  (OrderID, FloorStock, LeadTime, MaterialAvailable, OrderedDate, ExpectedDate, ReceivedDate)
VALUES
  (80, 0, 100, 0, null, null, null),
  (80, 0, 10, 0, '7/01/22', '8/23/22', '8/23/22'),
  (80, 0, 5, 0, '7/05/22', '9/05/22', null),
  (80, 0, 20, 0, '7/10/22', null, null),
  (106, 1, 0, 1, null, null, null),
  (148, 0, 15, 0, '8/01/22', null, null),
  (880, 0, 10, 0, '8/01/22', '9/23/22', '9/23/22'),
  (880, 0, 5, 0, '8/06/22', '9/05/22', null),
  (880, 0, 1, 0, '8/10/22', null, null);
9 rows affected

with data as (
    select *,
        row_number() over (order by dateadd(day, LeadTime, OrderedDate) desc, LeadTime desc) as rn
    from Material
    where OrderID = 880 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


OrderedDate
SelectedLeadTime
ExpectDate
ReceivedDate


2022-08-11
10
2022-09-23
2022-09-23


Warning: Null value is eliminated by an aggregate or other SET operation.


with data as (
    select *,
        dense_rank() over (order by dateadd(day, LeadTime, OrderedDate) desc) as dr
    from Material
    where OrderID = 880 and FloorStock = 0 and MaterialAvailable = 0
)
select * from data where dr = 1


OrderID
FloorStock
LeadTime
MaterialAvailable
OrderedDate
ExpectedDate
ReceivedDate
dr


880
0
10
0
2022-08-01
2022-09-23
2022-09-23
1

880
0
5
0
2022-08-06
2022-09-05
null
1

880
0
1
0
2022-08-10
null
null
1

OrderID	LeadTime	OrderedDate	ExpectedDate	ReceivedDate	dr
880	10	2022-08-01	2022-09-23	2022-09-23	1
880	5	2022-08-06	2022-09-05	null	1
880	1	2022-08-10	null	null	1