June 17, 2010 at 12:24 am
First, I apologize if this is not posted in the correct place. If not, please kindly direct me to the proper location here or anywhere I might find advice on this. I don't know a lot about SQL, I only use the management studio to query data periodically for business reports I can't get through our software interface. I spent all day trying to figure out how to get what I need now and can't seem to get it right. I need data from 3 tables, the problem is one of the table has multiple records for one Order and I need it to output on a single line based on the type of record. Let me try to explain:
(There are more columns in these tables than shown, but only including pertinent ones here. Table 3 "OrderCharges", is the one that I want to query both records onto one line in the results.)
Table 1 ("Orders" - 1 record per OrderID)
OrderIDOrderNumber
12341000
Table 2 ("OrderItems" - Varying records per OrderID)
OrderIDSKUNAME
1234W-1Widget 1
Table 3 ("OrderCharges" - 2 records per OrderID)
OrderIDTypeAmount
1234TAX.93
1234SHIPPING4.99
What I want for results is one line for each Order Item. If I could only get the tax & shipping on one item result line, that would be optimal, but if I need to manipulate that manually it is not a big deal. Most orders are for a single item.
OrderIDOrderNumberSKUNAMETAXSHIPPING
12341000W-1Widget 1.934.99
What I get is 2 lines for each item one with tax and null for shipping and one with shipping and null for tax. This is the closest I've gotten today, can anybody help me?
(My Query)
------------------------------
SELECT Orders.OrderNumber, OrderItems.SKU, OrderItems.Name,
Tax = CASE WHEN (OrderCharges.Type = 'TAX') THEN Ordercharges.Amount END,
Shipping = CASE WHEN (OrderCharges.Type = 'SHIPPING') THEN Ordercharges.Amount END
FROM Orders
JOIN OrderItems
ON Orders.OrderID = OrderItems.OrderID
JOIN OrderCharges
ON Orders.OrderID = OrderCharges.OrderID
WHERE OrderDate between '6/1/2010' and '6/15/2010'
ORDER BY OrderDate
-----------------------------
Most Appreciative! - Joe
June 17, 2010 at 12:38 am
This is certainly the correct place to ask this query ; no worries there !
For a first time poster, you have done a commendable job in explaining things, kudos there!
Now the query which u have wrote, u completed 95% of your requirement ; just you will have to GROUP the data and use an AGGREGATE function to "flatten" your result set.
this is my query:
SELECT Orders.OrderID, Orders.OrderNumber, OrderItems.SKU, OrderItems.Name,
-- Added MAX
Tax = MAX( CASE WHEN (OrderCharges.Type = 'TAX') THEN Ordercharges.Amount END ) ,
-- Added MAX
Shipping = MAX( CASE WHEN (OrderCharges.Type = 'SHIPPING') THEN Ordercharges.Amount END )
FROM Orders
JOIN OrderItems
ON Orders.OrderID = OrderItems.OrderID
JOIN OrderCharges
ON Orders.OrderID = OrderCharges.OrderID
WHERE OrderDate between '6/1/2010' and '6/15/2010'
-- Have added here
GROUP BY
Orders.OrderID,Orders.OrderNumber, OrderItems.SKU, OrderItems.Name
ORDER BY OrderDate
The changes i have made are below the comments! I have not tested it as i dint have data to test it, please test it yourself!
Tell us if that helped you, Cheers!!
June 17, 2010 at 12:41 am
The description of the problem , the tables structure and the desired results are VERY WELL put by you! I have one recommendation though, please go through the following article on how to make your fine explanation into readily-consumable code so that you get TESTED and OPTIMIZED answer!
Link : CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]
Again, nice post buddy !:-)
June 17, 2010 at 11:15 am
I want to say Thank You So Much for the help!
ColdCoffee -- It worked! I had to add "Orders.OrderDate to the SELECT and GROUP BY, but the error was clear enough to help me figure that out on my own. I also appreciate the link to the forum etiquette page. I do want to be respectful of all of your time in looking at my post and will keep that link handy.
sewartc-708166 -- It worked just as you wrote it, thanks!
The great part is.... both of these give me a little more knowledge 😎 and you both saved me more hours of my day today!
Most Appreciative ~ Joe
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply