April 5, 2025 at 2:41 am
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.
April 5, 2025 at 9:40 pm
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
April 6, 2025 at 5:01 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy