Joining against min date only

  • This is one of those things very hard (for me)to explain in words.

    See image below, the requirement is assign all of the sales with Reqdate prior to the "Best Before Date"  but ONLY the earliest "Best Before Date." I am trying to do this only with T-SQL.  My full scenario is of course more than complicated than this but this is root problem to solve for using two tables "on hand" and "sales" mocked up below.

     

    help

  • Got some data instead of just pictures?

    Oh, something like this:

    SELECT 
    roh.itemID,
    roh.[Best Before Date],
    roh.[On Hand],
    oa.TotalOrders
    FROM REporting.FGMonthlyRiskOnHand roh
    OUTER APPLY (
    SELECT SUM(SalesOrders) AS TotalOrders
    FROM Reporting.FGMonthlyRiskSales mrs
    WHERE mrs.ItemID = roh.ItemID
    AND mrs.ReqDate < roh.[Best Before Date]
    ORDER BY mrs.BestBeforeDate ASC
    ) oa

    • This reply was modified 3 weeks, 2 days ago by  pietlinden.
  • Maybe use OUTER APPLY?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply