June 5, 2011 at 6:52 am
Hello, Please need your help.
I have 3 tables.
* Product_Table (id_product, product_name)
* Sold_Items (id_product, quantity, date_sold)
* Returned_Items (id_product, quantity, date_returned)
how can I query in order to get results like:
id_product / product_name / sum(sold_quantity) / sum(Returned_quantity) / net_amount (sold minus returnet quantity)
1 / Notebook / 50 / 10 / 40
each query sum for sold and returned quantity has to be parametized by different dates. (date_sold, date_returned)
Thanks in advance.
Jon
June 5, 2011 at 8:49 am
Outer join Products to the Sales and Returns tables. Do a totals query. Then you'll get a record for every Product regardless of whether it has had any sales or returns. Then just create another query based on the totals query and subtract.
June 5, 2011 at 5:52 pm
Heya!!
You could try some fancy SQL, be be aware some of this TSQL code does different things with different versions of SQL, but I am hoping you are using at-least 2005 in which case give the following bit of code a whirl.
SELECT P.ID_Product, P.Product_Name, Sold.Sold_Quantity, Returned.Returned_Quantity,
COALESCE(Sold.Sold_Quantity,0) - COALESCE(Returned.Returned_Quantity) as [Net_Amount]
from Products P
LEFT JOIN (select SUM(Quantity) as Sold_Quantity, ID_Product from Sold_Items GROUP BY ID_Product) Sold ON P.ID_Product = Sold.ID_Product
LEFT JOIN (select SUM(Quantity) as Returned_Quantity, ID_Product from Returned_Items GROUP BY ID_Product) Returned
ON P.ID_Product = Returned.ID_Product
If it doesn't look like what you want let me know and I will start thinking again!!
Sam
June 6, 2011 at 2:39 pm
thank you so much samantha
It worked! really appreciated
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply