SQL Add Lead Time Day to MAX Ordered Date-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

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