Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jonasggg
Starting Member
4 Posts |
 Posted - 2011-06-05 : 08:38:16
|
| 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-06-05 : 09:08:54
|
| [code]select p.id_product, p.product_name, COALESCE(s.sold_qty,0) as sold_qty, COALESCE(r.ret_qty,0) as ret_qty, COALESCE(s.sold_qty,0)-COALESCE(r.ret_qty,0) AS net_qtyfrom product_table pleft join (select id_product,sum(quantity) as sold_qty from Sold_Items group by id_product)son s.id_product = p.id_productleft join (select id_product,sum(quantity) as ret_qty from Returned_Items group by id_product)ron r.id_product = p.id_product[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
jonasggg
Starting Member
4 Posts |
Posted - 2011-06-06 : 16:38:14
|
| thank you so much visakh16!It worked! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-06-12 : 04:48:57
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
subtract 2 columns values from two different table