January 3, 2016 at 10:46 pm
Hello,
Ok so I have this project that exploded more into what it was, but I may be over thinking it.
anyways, so here it is, I have 2 tables, 1 is orders, and the other is parts, the order of course has a list of parts per order, BUT for each part, might have a child that is used to make that part for the order. so for order 12345, part A, needs part B and C to make, so thats no prob, I have that:
Special thanks to:
Jason A. Long and Jeff Moden
Example:
IF OBJECT_ID('tempdb..#orders', 'U') IS NOT NULL
DROP TABLE #orders;
create table #orders (
orderid int,
part varchar(50)
);
IF OBJECT_ID('tempdb..#parts', 'U') IS NOT NULL
DROP TABLE #parts;
create table #parts (
partheader VARCHAR(50),
childpart varchar(50),
Qty int
);
INSERTINTO #orders (orderid,part) VALUES
(1,'bike'),
(2,'rollerskates'),
(3,'scooter');
INSERT INTO #parts(partheader,childpart) VALUES
('bike','wheels',0),
('bike','chains',1),
('chains','metal',0),
('scooter','wheels',0),
('scooter','metal',2);
WITH
cte_PartsOrder AS (
SELECT
o.orderid,
o.part,
node = 1
FROM
#orders o
UNION ALL
SELECT
po.orderid,
p.childpart,
node = po.Node + 1
FROM
#parts p
JOIN cte_PartsOrder po
ON p.partheader = po.part
)
SELECT
po.orderid,
po.part
FROM
cte_PartsOrder po
ORDER BY
po.orderid,
po.node
;
BUT now here is the new additional to the requirement, so going from parent to child, once it goes to child, it first see's if there is any quantity, IF there is no quantity, then go down from child to next child, or grand child in this case, and if no Qty in those Grandchild, go down further... further and further it goes... Which I added in the example above a new column called Qty.
again not sure if a while loop is a good idea, or stick with Recursive but not sure if I should use an IF statement or where clause etc. to check each child or sub child to see if it meets the requirement to go next to recursive or not...
need help 🙁
thanks everyone in advance for any input.
January 4, 2016 at 8:25 am
I would try both approaches to see which perform better with your data.
WITH
cte_PartsOrder AS (
SELECT
o.orderid,
o.part,
node = 1,
Qty = 0
FROM
#orders o
UNION ALL
SELECT
po.orderid,
p.childpart,
node = po.Node + 1,
p.Qty
FROM
#parts p
JOIN cte_PartsOrder po
ON p.partheader = po.part
WHERE po.Qty = 0
)
SELECT
po.orderid,
po.part
FROM
cte_PartsOrder po
ORDER BY
po.orderid,
po.node
;
DECLARE @Node int = 0
SELECT
o.orderid,
o.part,
node = 1,
Qty = 0
INTO #PartsOrder
FROM #orders o
WHILE @@ROWCOUNT > 0
BEGIN
SET @Node += 1;
INSERT INTO #PartsOrder
SELECT
po.orderid,
p.childpart,
node = po.Node + 1,
p.Qty
FROM
#parts p
JOIN #PartsOrder po
ON p.partheader = po.part
WHERE po.Qty = 0
AND po.node = @Node
END
SELECT po.orderid,
po.part
FROM #PartsOrder po
ORDER BY
po.orderid,
po.node;
January 4, 2016 at 10:53 am
I get the impression that you're trying to build out an order processing system that may be a bit more complex than your test data is able to represent...
Tell us whether or not I'm on track here...
1) Orders come in... A given order can contain multiple products and that order various quantities of a given product.
In other words, I can place an order for 3 scooters and 5 bikes... Is that correct?
2) Either all products are built to order or there are pre-assembled products in inventory... Or a combination of the two...
2a) Assuming #2 is correct, I'd also assume that you would wan't to sell off any pre-assembled items before you jump into building new items from raw materials...
In other words, If you you have 10 scooters and 15 bikes already assembled and sitting inventory, you can fill my order without regard for the current stock of unassembled component parts. You only care about the scooter parts if you don't have enough completed scooters on hand to fill my order... Is that correct?
If the above sounds about right, then several things need to happen...
1) Check the order against the current inventory.
2) Place a hold (remove from available inventory) on the items that you can fill off the self (you don't want to allocate the same item to multiple orders due to timing issues).
3) Determine what parts of the order cannot be met based solely by "on hand" inventory.
4) Determine if you have the required component parts on hand to complete the order.
5) Allocate the the required parts to the order and issue a work order to have those parts assembled. (again, you don't want the same parts allocated to multiple orders due to timing issues)
Again, does the above sound about right?
January 4, 2016 at 3:57 pm
Jason... DANG exactly!!!
except i think you have this part as well, IF the part such as an example: for the scooter, if you dont have enough scooters to meet the order, then drill down to the child of those parts that make the scooter (it can be metal, wheels, handle etc, then those parts like wheel can have a drill down IF they dont have enough Qty on Hand to meet that part of the order) and see if they have enough to fill that order, and of course have it in a list with Qty on Hand.
hope that makes sense but Ya jason, hit it on the head of the nail, bring you back good memories? 🙂
January 4, 2016 at 4:52 pm
Siten0308 (1/4/2016)
Jason... DANG exactly!!!except i think you have this part as well, IF the part such as an example: for the scooter, if you dont have enough scooters to meet the order, then drill down to the child of those parts that make the scooter (it can be metal, wheels, handle etc, then those parts like wheel can have a drill down IF they dont have enough Qty on Hand to meet that part of the order) and see if they have enough to fill that order, and of course have it in a list with Qty on Hand.
hope that makes sense but Ya jason, hit it on the head of the nail, bring you back good memories? 🙂
Actually, I've been in the service industry by entire career... I've never had to be concerned with inventory or FIFO/LIFO problems in real life... But they tend to be interesting challenges... Probably because I don't get to see them in my normal day to day activities.
I don't know if I'll have the opportunity to work on this tonight (it been a Monday... all... day... long) but I'll take a crack at it tomorrow evening if someone hasn't already beat me to it.
January 4, 2016 at 8:35 pm
Jason, thank you, and no prob, any help or assistance would be great, no matter how long it takes, if anything, you and everyone on this forum have been a big help. so thank you 🙂
and if/when you do have it, can you explain in detail, this will not only help me, but I am sure anyone else as well that could run into this situation in the future 🙂
again thank you 🙂
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply