January 20, 2014 at 12:51 pm
I am trying to take 3 tables representing purchase orders, purchase order details and an Item file that contains the price of the item (Please see the attachment). I want do end up with:
Purchase Order NumberPurchase Order Total
12345 1066
Any help would be greatly appreciated, I have been running around in circles trying to come up with a query to do this
January 20, 2014 at 1:02 pm
This is easily accomplished with joins and an aggregate function. I have no intention on doing your homework, but I can guide you on specific questions that you have.
In short, what have you tried?
January 20, 2014 at 1:38 pm
Luis,
Thank you for taking the time to reply. While you didn't answer my question, you solved my problem anyway. I was trying to over complicate my solution and doing a lot of stuff I didn't need like trying to flatten the PO details to the PO's... By the way... This was not homework;-)
Thank you
I ended up with:
SELECT PO.PurchaseOrderNumber, SUM(POD.Quantity * I.ItemCost)
FROM #PurchaseOrderDetail as pod
INNER JOIN #PurchaseOrder PO
ON PO.PurchaseOrderID = POD.PurchaseOrderID
INNER JOIN #Item I ON I.ItemID = pod.ItemID
GROUP BY PO.PurchaseOrderNumber
January 20, 2014 at 2:04 pm
That's exactly what I had in mind. It's always better when people are able to find an answer on their own.
By the way, you should think on changing the design for your tables. It's usual that an item's price varies over the time. If you have a single item file, you won't be able to determine the real total for each order unless you save a history of the cost. You might want to add a column on your PODetails table to keep the exact cost of the item at the moment of the purchase. You could even add a computed column to reference the calculation for (quantity *cost).
Anyway, I don't have the complete picture to give complete solutions, but just thought of leaving this idea.
And for future help, please read the article linked in my signature on how to post sample data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply