February 3, 2015 at 10:16 pm
I'm sure this is by far not the best way to do things, buts its been the only way I've worked out so far.
I have 2 tables - 1 with all the orders to manufacture products and 1 with all the orders from customers. Now based on some logic I need to allocate the orders to manufactured products to customer orders.
There are just under 1,000,000 rows I'm having to deal with.
#TempTable1 = Lets say has the manufacture products
#TempTable2 = Customer Orders lines
#tempTable3 = Subset of @temptable2 based on #temptable1 when looping through.
Their is more definition stuff I haven't included, but here is my double look query. It takes about 50 min to run currently (down from 4 hours of my original attempt).
Any help would be greatly appreciated.
Thanks in advance.
SET @qty = 0
SET @childQty = 0
DECLARE @TopRow as INT
SET @TopRow = 1
DECLARE @count AS INT , @count3 AS INT
SELECT @count = COUNT(*) from #TmpTable
WHILE @count>0 --EXISTS (SELECT * FROM #TmpTable)
BEGIN
SELECT TOP (@TopRow) @ID=[ID],@Type=[Type],@OrderID=[OrderID],@UniqueOrder=[Unique Order],@Product=[Product],@Qty=[Qty], @TotalQty = Qty,
@CustomerWH=[Customer WH],@DespatchWH=[Despatch WH],@DeliveryDate=[Delivery Date]
FROM #TmpTable ORDER BY [Product],[Customer WH],[Despatch WH],[Priority]
TRUNCATE TABLE #TmpTable3
INSERT INTO #TmpTable3
SELECT * FROM #TmpTable2 WHERE NOT [ID]=@ID AND [Product]=@Product AND [Despatch WH]=@CustomerWH
SELECT @count3 = COUNT(*) FROM #TmpTable3
WHILE @count3 > 0 --EXISTS (SELECT * FROM #TmpTable3)
BEGIN
SELECT TOP (@TopRow) @ChildID=[ID],@ChildOrderID=[OrderID],@ChildUniqueOrder=[Unique Order],@ChildProduct=[Product]
,@ChildQty=CASE WHEN @childQty = 0 THEN [Qty] ELSE @ChildQty END,
@TotalChildQty = Qty,
@ChildCustomerWH=[Customer WH],@ChildDespatchWH=[Despatch WH],@ChildDeliveryDate=[Delivery Date]
FROM #TmpTable3 ORDER BY [Product],[Customer WH],[Despatch WH],[Priority]
IF @ChildQty=@Qty
BEGIN
INSERT INTO [BKDataWarehouse].[dbo].[tbl_mc_Log_OrderLinking]
([Type],[OrderID],[Unique Order],[Product],[Qty],[AllocatedQty],[Customer WH],[Despatch WH],[Delivery Date]
,[Child Type],[Child OrderID],[Child Unique Order],[Child Product],[Child Qty],[Child AllocatedQty],[Child Customer WH],[Child Despatch WH],[Child Delivery Date])
VALUES
(@Type,@OrderID,@UniqueOrder,@Product,@TotalQty,@Qty,@CustomerWH,@DespatchWH,@DeliveryDate
,@ChildType,@ChildOrderID,@ChildUniqueOrder,@ChildProduct,@TotalChildQty,@ChildQty,@ChildCustomerWH,@ChildDespatchWH,@ChildDeliveryDate)
--TRUNCATE TABLE #TmpTable3
DELETE FROM #TmpTable2 WHERE ID = @ChildID
--DELETE FROM #TmpTable WHERE ID = @ID
SET @ChildQty = 0
SET @Qty = 0
SET @count3 = 0
END
ELSE
BEGIN
IF @ChildQty>@Qty
BEGIN
INSERT INTO [BKDataWarehouse].[dbo].[tbl_mc_Log_OrderLinking]
([Type],[OrderID],[Unique Order],[Product],[Qty],[AllocatedQty],[Customer WH],[Despatch WH],[Delivery Date]
,[Child Type],[Child OrderID],[Child Unique Order],[Child Product],[Child Qty],[Child AllocatedQty],[Child Customer WH],[Child Despatch WH],[Child Delivery Date])
VALUES
(@Type,@OrderID,@UniqueOrder,@Product,@TotalQty,@Qty,@CustomerWH,@DespatchWH,@DeliveryDate
,@ChildType,@ChildOrderID,@ChildUniqueOrder,@ChildProduct,@TotalChildQty,@Qty,@ChildCustomerWH,@ChildDespatchWH,@ChildDeliveryDate)
--TRUNCATE TABLE #TmpTable3
--DELETE FROM #TmpTable WHERE ID = @ID
SET @ChildQty = @ChildQty - @Qty
SET @Qty = 0
SET @count3= 0
END
ELSE
BEGIN
INSERT INTO [BKDataWarehouse].[dbo].[tbl_mc_Log_OrderLinking]
([Type],[OrderID],[Unique Order],[Product],[Qty],[AllocatedQty],[Customer WH],[Despatch WH],[Delivery Date]
,[Child Type],[Child OrderID],[Child Unique Order],[Child Product],[Child Qty],[Child AllocatedQty],[Child Customer WH],[Child Despatch WH],[Child Delivery Date])
VALUES
(@Type,@OrderID,@UniqueOrder,@Product,@TotalQty,@ChildQty,@CustomerWH,@DespatchWH,@DeliveryDate
,@ChildType,@ChildOrderID,@ChildUniqueOrder,@ChildProduct,@TotalChildQty,@ChildQty,@ChildCustomerWH,@ChildDespatchWH,@ChildDeliveryDate)
DELETE FROM #TmpTable3 WHERE ID = @ChildID
DELETE FROM #TmpTable2 WHERE ID = @ChildID
SET @Qty = @Qty - @ChildQty
SET @ChildQty = 0
SET @count3 = @count3-1
END
END
END
DELETE FROM #TmpTable WHERE ID = @ID
SET @count = @count-1
END
February 4, 2015 at 1:43 am
Quick thought, this code looks very inefficient, while the logic is rather simple, should be straight forward to construct a set based code for the operation. Could you post DDL and sample data necessary to run this code?
😎
February 4, 2015 at 2:24 am
Not sure if I understand the problem 100% but perhaps you can join the first two tables using a FULL OUTER JOIN?
You would then see all products that match their respective orders, and you would see any products or orders that do not have a match (NULL)
SELECT column_names_from_both_tables
FROM table1
FULL OUTER JOIN
table2
ON table1.column_id1 = table2.column_id1
AND table1.column_id2 = table2.column_id2
AND table1.column_id3 = table2.column_id3
You can get further info on this blog which discusses nested loops
February 4, 2015 at 6:17 am
Byzza (2/3/2015)
I'm sure this is by far not the best way to do things, buts its been the only way I've worked out so far.I have 2 tables - 1 with all the orders to manufacture products and 1 with all the orders from customers. Now based on some logic I need to allocate the orders to manufactured products to customer orders.
There are just under 1,000,000 rows I'm having to deal with.
#TempTable1 = Lets say has the manufacture products
#TempTable2 = Customer Orders lines
#tempTable3 = Subset of @temptable2 based on #temptable1 when looping through.
Their is more definition stuff I haven't included, but here is my double look query. It takes about 50 min to run currently (down from 4 hours of my original attempt).
Any help would be greatly appreciated.
Thanks in advance.
SET @qty = 0
SET @childQty = 0
DECLARE @TopRow as INT
SET @TopRow = 1
DECLARE @count AS INT , @count3 AS INT
SELECT @count = COUNT(*) from #TmpTable
WHILE @count>0 --EXISTS (SELECT * FROM #TmpTable)
BEGIN
SELECT TOP (@TopRow) @ID=[ID],@Type=[Type],@OrderID=[OrderID],@UniqueOrder=[Unique Order],@Product=[Product],@Qty=[Qty], @TotalQty = Qty,
@CustomerWH=[Customer WH],@DespatchWH=[Despatch WH],@DeliveryDate=[Delivery Date]
FROM #TmpTable ORDER BY [Product],[Customer WH],[Despatch WH],[Priority]
TRUNCATE TABLE #TmpTable3
INSERT INTO #TmpTable3
SELECT * FROM #TmpTable2 WHERE NOT [ID]=@ID AND [Product]=@Product AND [Despatch WH]=@CustomerWH
SELECT @count3 = COUNT(*) FROM #TmpTable3
WHILE @count3 > 0 --EXISTS (SELECT * FROM #TmpTable3)
BEGIN
SELECT TOP (@TopRow) @ChildID=[ID],@ChildOrderID=[OrderID],@ChildUniqueOrder=[Unique Order],@ChildProduct=[Product]
,@ChildQty=CASE WHEN @childQty = 0 THEN [Qty] ELSE @ChildQty END,
@TotalChildQty = Qty,
@ChildCustomerWH=[Customer WH],@ChildDespatchWH=[Despatch WH],@ChildDeliveryDate=[Delivery Date]
FROM #TmpTable3 ORDER BY [Product],[Customer WH],[Despatch WH],[Priority]
IF @ChildQty=@Qty
BEGIN
INSERT INTO [BKDataWarehouse].[dbo].[tbl_mc_Log_OrderLinking]
([Type],[OrderID],[Unique Order],[Product],[Qty],[AllocatedQty],[Customer WH],[Despatch WH],[Delivery Date]
,[Child Type],[Child OrderID],[Child Unique Order],[Child Product],[Child Qty],[Child AllocatedQty],[Child Customer WH],[Child Despatch WH],[Child Delivery Date])
VALUES
(@Type,@OrderID,@UniqueOrder,@Product,@TotalQty,@Qty,@CustomerWH,@DespatchWH,@DeliveryDate
,@ChildType,@ChildOrderID,@ChildUniqueOrder,@ChildProduct,@TotalChildQty,@ChildQty,@ChildCustomerWH,@ChildDespatchWH,@ChildDeliveryDate)
--TRUNCATE TABLE #TmpTable3
DELETE FROM #TmpTable2 WHERE ID = @ChildID
--DELETE FROM #TmpTable WHERE ID = @ID
SET @ChildQty = 0
SET @Qty = 0
SET @count3 = 0
END
ELSE
BEGIN
IF @ChildQty>@Qty
BEGIN
INSERT INTO [BKDataWarehouse].[dbo].[tbl_mc_Log_OrderLinking]
([Type],[OrderID],[Unique Order],[Product],[Qty],[AllocatedQty],[Customer WH],[Despatch WH],[Delivery Date]
,[Child Type],[Child OrderID],[Child Unique Order],[Child Product],[Child Qty],[Child AllocatedQty],[Child Customer WH],[Child Despatch WH],[Child Delivery Date])
VALUES
(@Type,@OrderID,@UniqueOrder,@Product,@TotalQty,@Qty,@CustomerWH,@DespatchWH,@DeliveryDate
,@ChildType,@ChildOrderID,@ChildUniqueOrder,@ChildProduct,@TotalChildQty,@Qty,@ChildCustomerWH,@ChildDespatchWH,@ChildDeliveryDate)
--TRUNCATE TABLE #TmpTable3
--DELETE FROM #TmpTable WHERE ID = @ID
SET @ChildQty = @ChildQty - @Qty
SET @Qty = 0
SET @count3= 0
END
ELSE
BEGIN
INSERT INTO [BKDataWarehouse].[dbo].[tbl_mc_Log_OrderLinking]
([Type],[OrderID],[Unique Order],[Product],[Qty],[AllocatedQty],[Customer WH],[Despatch WH],[Delivery Date]
,[Child Type],[Child OrderID],[Child Unique Order],[Child Product],[Child Qty],[Child AllocatedQty],[Child Customer WH],[Child Despatch WH],[Child Delivery Date])
VALUES
(@Type,@OrderID,@UniqueOrder,@Product,@TotalQty,@ChildQty,@CustomerWH,@DespatchWH,@DeliveryDate
,@ChildType,@ChildOrderID,@ChildUniqueOrder,@ChildProduct,@TotalChildQty,@ChildQty,@ChildCustomerWH,@ChildDespatchWH,@ChildDeliveryDate)
DELETE FROM #TmpTable3 WHERE ID = @ChildID
DELETE FROM #TmpTable2 WHERE ID = @ChildID
SET @Qty = @Qty - @ChildQty
SET @ChildQty = 0
SET @count3 = @count3-1
END
END
END
DELETE FROM #TmpTable WHERE ID = @ID
SET @count = @count-1
END
Can you confirm that this is a bin-packing problem?
Next, folks will need scripts and sample data for both tables, preferably with a desired output set too. Read this [/url]if you're unsure what that entails.
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
February 4, 2015 at 3:26 pm
Hi All,
Thanks for the responses so far. I can't really do any kind of joins, is because I need to be able to allocate manufacture order qty's to customer orders and they must be done in a very specific order.
It kind of is a Bin Packing problem I suppose. Basically when ever a customer places an order for a product we raise a manufacturing order for that exact qty BUT the customer doesn't always get that allocation of products, but instead based on the customers priority they may end up with the very next qty off the line.
Eg the last customer might order Qty -25, but because theirs is a rush order, they might get the next 25 off the manufacturing line, which could be a manufacturing order of 5, then 10, then the next 10 of a manufacturing order or 20.
below is some sample data. I've simplified the tables to just the core columns.
in the results W_qty_Used should equal c_Qty_Allocated. We have just been keeping both columns for testing
CREATE Table #TmpTable(
WID int,
Product varchar(10),
Qty int
)
CREATE Table #TmpTable2(
CID int,
Product varchar(10),
Qty int
)
CREATE Table #results(
Product varchar(10),
WID int,
W_Total_Qty int,
W_Qty_Used int,
CID int,
C_Total_Qty int,
C_Qty_Allocated int
)
INSERT INTO #TmpTable( WID, Product, Qty )
SELECT 1000, 'ABC123', 50 UNION ALL
SELECT 1001, 'ABC123', 5 UNION ALL
SELECT 1002, 'ABC123', 12 UNION ALL
SELECT 1003, 'ABC123', 50
INSERT INTO #TmpTable2( CID, Product, Qty )
SELECT 2000, 'ABC123', 5 UNION ALL
SELECT 2001, 'ABC123', 10 UNION ALL
SELECT 2002, 'ABC123', 40 UNION ALL
SELECT 2003, 'ABC123', 62
INSERT INTO #results ( Product ,WID ,W_Total_Qty ,W_Qty_Used ,CID ,C_Total_Qty ,C_Qty_Allocated)
SELECT 'ABC123', 1000,50,5,2000,5,5 UNION ALL
SELECT 'ABC123', 1000,50,10,2001,10,10 UNION ALL
SELECT 'ABC123', 1000,50,35,2002,40,35 UNION ALL
SELECT 'ABC123', 1001,5,5,2002,40,5 UNION ALL
SELECT 'ABC123', 1002,12,12,2003,62,12 UNION ALL
SELECT 'ABC123', 1003,50,50,2003,62,50
SELECT * FROM #TmpTable
SELECT * FROM #TmpTable2
SELECT * FROM #results
DROP TABLE #TmpTable
DROP TABLE #TmpTable2
DROP TABLE #results
February 12, 2015 at 8:12 pm
Byzza (2/4/2015)
Hi All,Thanks for the responses so far. I can't really do any kind of joins, is because I need to be able to allocate manufacture order qty's to customer orders and they must be done in a very specific order.
It kind of is a Bin Packing problem I suppose. Basically when ever a customer places an order for a product we raise a manufacturing order for that exact qty BUT the customer doesn't always get that allocation of products, but instead based on the customers priority they may end up with the very next qty off the line.
Eg the last customer might order Qty -25, but because theirs is a rush order, they might get the next 25 off the manufacturing line, which could be a manufacturing order of 5, then 10, then the next 10 of a manufacturing order or 20.
below is some sample data. I've simplified the tables to just the core columns.
in the results W_qty_Used should equal c_Qty_Allocated. We have just been keeping both columns for testing
CREATE Table #TmpTable(
WID int,
Product varchar(10),
Qty int
)
CREATE Table #TmpTable2(
CID int,
Product varchar(10),
Qty int
)
CREATE Table #results(
Product varchar(10),
WID int,
W_Total_Qty int,
W_Qty_Used int,
CID int,
C_Total_Qty int,
C_Qty_Allocated int
)
INSERT INTO #TmpTable( WID, Product, Qty )
SELECT 1000, 'ABC123', 50 UNION ALL
SELECT 1001, 'ABC123', 5 UNION ALL
SELECT 1002, 'ABC123', 12 UNION ALL
SELECT 1003, 'ABC123', 50
INSERT INTO #TmpTable2( CID, Product, Qty )
SELECT 2000, 'ABC123', 5 UNION ALL
SELECT 2001, 'ABC123', 10 UNION ALL
SELECT 2002, 'ABC123', 40 UNION ALL
SELECT 2003, 'ABC123', 62
INSERT INTO #results ( Product ,WID ,W_Total_Qty ,W_Qty_Used ,CID ,C_Total_Qty ,C_Qty_Allocated)
SELECT 'ABC123', 1000,50,5,2000,5,5 UNION ALL
SELECT 'ABC123', 1000,50,10,2001,10,10 UNION ALL
SELECT 'ABC123', 1000,50,35,2002,40,35 UNION ALL
SELECT 'ABC123', 1001,5,5,2002,40,5 UNION ALL
SELECT 'ABC123', 1002,12,12,2003,62,12 UNION ALL
SELECT 'ABC123', 1003,50,50,2003,62,50
SELECT * FROM #TmpTable
SELECT * FROM #TmpTable2
SELECT * FROM #results
DROP TABLE #TmpTable
DROP TABLE #TmpTable2
DROP TABLE #results
You did a nice job of posting readily consumable data but I suspect the reason why no one has responded is that they can't figure out which table is for what. For example, we don't know what a WID or a CID is and the table names don't give a clue as to the type of data.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply