March 23, 2017 at 6:52 pm
Hello,
I have a report I'm trying to create where they're looking to see when they need to reschedule orders earlier as there will be a demand for certain items earlier than originally expected. So currently I have 2 tables, one for the records where we're determining the demand, date of the demand and the qty needed, and the second table is for the orders. So I'm pasting below an example of the issue. In this example, I would expect to see 3 output rows (1 for each record in the #Records table), with the First Order that falls after the #Records.Due_Date, and the Last Order that would be needed to fulfill the Qty Needed. So for the first record I would expect to see Record ID 1, and First Order would be Order ID 3, and Last Order would be Order ID 4. I've been able to get the First Order properly, but the Last Order is what is giving me trouble. For the Record ID 1 I'm getting Order 5 for the Last Order instead of Order 4. Any suggestions?
TIA!!
CREATE TABLE #Records
(
ID INT,
Due_Date DATETIME,
Qty_Needed DECIMAL(18, 6)
);
CREATE TABLE #Orders
(
Order_ID INT,
Order_Due_Date DATETIME,
Order_Qty DECIMAL(18, 6)
);
INSERT #Records (ID, Due_Date, Qty_Needed)
SELECT 1, '3/31/17', 10000
UNION ALL
SELECT 2, '4/15/17', 16000
UNION ALL
SELECT 3, '5/1/17', 5000;
INSERT #Orders (Order_ID, Order_Due_Date, Order_Qty)
SELECT 1, '3/25/17', 4000
UNION ALL
SELECT 2, '3/26/17', 5000
UNION ALL
SELECT 3, '4/1/17', 2500
UNION ALL
SELECT 4, '4/5/17', 10000
UNION ALL
SELECT 5, '4/20/17', 7000
UNION ALL
SELECT 6, '4/30/17', 3500
UNION ALL
SELECT 7, '5/3/17', 7500;
WITH CTE_Orders AS
(
SELECT
O.Order_ID,
O.Order_Due_Date,
O.Order_Qty,
O.Order_Qty AS Running_Total
FROM #Orders AS O
WHERE O.Order_ID = 1
UNION ALL
SELECT
O2.Order_ID,
O2.Order_Due_Date,
O2.Order_Qty,
CONVERT(DECIMAL(18, 6), C.Running_Total + O2.Order_Qty) AS Running_Total
FROM #Orders AS O2
JOIN CTE_Orders AS C
ON C.Order_ID = O2.Order_ID - 1
)
--select * from CTE_Orders
SELECT *
FROM #Records AS R
CROSS APPLY
(
SELECT TOP(1)
O.Order_Due_Date,
O.Order_ID,
O.Order_Qty,
O.Running_Total
FROM CTE_Orders AS O
WHERE O.Order_Due_Date > R.Due_Date
ORDER BY
O.Order_Due_Date
) AS F
OUTER APPLY
(
SELECT TOP(1)
O2.Order_ID,
O2.Order_Due_Date,
O2.Order_Qty,
O2.Running_Total
FROM CTE_Orders AS O2
WHERE O2.Order_Due_Date > R.Due_Date
AND O2.Order_Due_Date > F.Order_Due_Date
AND O2.Order_ID > F.Order_ID
AND O2.Running_Total - F.Running_Total > R.Qty_Needed
ORDER BY
O2.Order_Due_Date
) AS L
DROP TABLE #Orders;
DROP TABLE #Records;
March 23, 2017 at 9:11 pm
Ok, so I figured it out in case anyone else comes across this post LOL
the last line of the Where clause for Outer Apply L should be
AND (O2.Running_Total - F.Running_Total) + F.Order_Qty >= R.Qty_Needed
March 24, 2017 at 4:54 am
lk4772 - Thursday, March 23, 2017 9:11 PMOk, so I figured it out in case anyone else comes across this post LOL
the last line of the Where clause for Outer Apply L should beAND (O2.Running_Total - F.Running_Total) + F.Order_Qty >= R.Qty_Needed
Same method, same results, much less code:
SELECT *
FROM #Records r
OUTER APPLY (
SELECT TOP 1 *
FROM (SELECT *, Running_Total = SUM(Order_Qty) OVER(ORDER BY Order_Due_Date) FROM #Orders) o
WHERE o.Order_Due_Date > r.Due_Date
ORDER BY o.Order_Due_Date
) f
OUTER APPLY (
SELECT TOP 1 *
FROM (SELECT *, Running_Total = SUM(Order_Qty) OVER(ORDER BY Order_Due_Date) FROM #Orders) o2
WHERE o2.Order_Due_Date > R.Due_Date
AND o2.Order_Due_Date > F.Order_Due_Date
AND o2.Order_ID > F.Order_ID
AND (o2.Running_Total - F.Running_Total) + F.Order_Qty >= R.Qty_Needed
ORDER BY o2.Order_Due_Date
) y
[/code]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 24, 2017 at 9:23 am
Thanks Chris! That is much better for all (code and plan and statistics)! 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply