April 21, 2018 at 10:01 pm
Hello,
I am back, want to say thank you again John M. for the assistance, with that logic i was able to complete one of my main requirements... but now faced with another challenge... this time, i will create a ddl etc. for the problem i am running into 🙁
CREATE TABLE #Order
(
OrderID int,
PartID int,
Quantity int,
OnHand int
)
create Table #Purchase
(
POrderID int,
PartID int,
Quantity int
)
Insert into #Order(OrderID,PartID,Quantity,OnHand)
values (1,1,5,8)
Insert into #Order(OrderID,PartID,Quantity,OnHand)
values (2,1,3,3)
Insert into #Order(OrderID,PartID,Quantity,OnHand)
values (3,1,2,0)
Insert into #Order(OrderID,PartID,Quantity,OnHand)
values (4,1,3,0)
Insert into #Order(OrderID,PartID,Quantity,OnHand)
values (5,1,2,0)
Insert into #Purchase(POrderID,PartID,Quantity)
values (1,1,1)
Insert into #Purchase(POrderID,PartID,Quantity)
values (2,1,3)
Insert into #Purchase(POrderID,PartID,Quantity)
values (3,1,2)
Insert into #Purchase(POrderID,PartID,Quantity)
values (4,1,2)
so above, i have an order table, and a purchase table, so the company is trying to fill those orders if the current on hand doesnt meet it, so they have to purchase, sometimes the purchase doesn't meet the order, which is why the 4th column is 8, then drops to 3 because of the order took 5, then the 2nd order took 3, then thats when the Purchase order should take over, i sorta have the logic, which is easy, if onhand = 0 then select a PO, but the hard part is... not sure how i can have 1 purchase order take over if the other PO doesnt cover the other orders... is it even possible? is it something that would require a developer to write that logic?
if not possible, then maybe what i can say is... aggregate or sum up all demanding orders and on hand, and po and what they would have left? any help on this would greatly be appreciated.
thanks again to John M 🙂 and thank you advance
April 21, 2018 at 11:41 pm
Siten0308 - Saturday, April 21, 2018 10:01 PMHello,I am back, want to say thank you again John M. for the assistance, with that logic i was able to complete one of my main requirements... but now faced with another challenge... this time, i will create a ddl etc. for the problem i am running into 🙁
CREATE TABLE #Order
(
OrderID int,
PartID int,
Quantity int,
OnHand int
)create Table #Purchase
(
POrderID int,
PartID int,
Quantity int
)Insert into #Order(OrderID,PartID,Quantity,OnHand)
values (1,1,5,8)Insert into #Order(OrderID,PartID,Quantity,OnHand)
values (2,1,3,3)Insert into #Order(OrderID,PartID,Quantity,OnHand)
values (3,1,2,0)Insert into #Order(OrderID,PartID,Quantity,OnHand)
values (4,1,3,0)Insert into #Order(OrderID,PartID,Quantity,OnHand)
values (5,1,2,0)Insert into #Purchase(POrderID,PartID,Quantity)
values (1,1,1)Insert into #Purchase(POrderID,PartID,Quantity)
values (2,1,3)Insert into #Purchase(POrderID,PartID,Quantity)
values (3,1,2)Insert into #Purchase(POrderID,PartID,Quantity)
values (4,1,2)so above, i have an order table, and a purchase table, so the company is trying to fill those orders if the current on hand doesnt meet it, so they have to purchase, sometimes the purchase doesn't meet the order, which is why the 4th column is 8, then drops to 3 because of the order took 5, then the 2nd order took 3, then thats when the Purchase order should take over, i sorta have the logic, which is easy, if onhand = 0 then select a PO, but the hard part is... not sure how i can have 1 purchase order take over if the other PO doesnt cover the other orders... is it even possible? is it something that would require a developer to write that logic?
if not possible, then maybe what i can say is... aggregate or sum up all demanding orders and on hand, and po and what they would have left? any help on this would greatly be appreciated.
thanks again to John M 🙂 and thank you advance
Can you kindly post desired output?
Saravanan
April 21, 2018 at 11:54 pm
ahh that would help right saravanatn lol, sorry
the output i would like is:
Order-------part--------Quantity---------Onhand-------PO--------remaining
1------------ 1 ---------5------------------8-------------null---------3
2---------------1-------------3------------------3-------------null---------0
3--------------1--------------2------------------0------------1------------- -1 - the reason its negative 1 is because the first purchase order couldnt cover the order 3, because order 3 required 2, but the po only had 1
3--------------1--------------2------------------0------------2-------------2 ----------the 2nd po, or Purchase order 2, had quantity 3, but since the first PO couldnt cover it, the 2nd po did...
just thinking, is it possible that sql could handle this kind of calculation, maybe i need to rethink and see if a developer can accomplish this?
thanks again and sorry for the missing info and if the dash (-) thrwos you off, i am not sure how to post a table besides maybe a screen shot?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply