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 |
vennboo
Starting Member
2 Posts |
Posted - 2013-03-21 : 12:04:25
|
I have been given the following code by a colleague to see if I can add the branch.At the moment we have a report showing supplier name, style number, colour, size, quantity ordered, quantity sold and quantity left. (I have attached the report)We would like to show what quantity has been ordered, sold and is available at each branch (branch_id). So we can see if we need to move any stock.Can anyone help? Thanks in advance. /* get stock qty */select plu, description, supplier_name, style, colour_name, size_name,sum(branchstock.stock_quantity) as 'stockqty',sum(0) as 'orderqty',sum(0) as 'saleqty'into memory xfrom productsinner join branchstock on (branchstock.plu = products.plu)group by pluunion/* get orders qty */select plu, description, supplier_name, style, colour_name, size_name,sum(0) as 'stockqty',sum(qty) as 'orderqty',sum(0) as 'saleqty'from productsinner join bookgoodsin on (bookgoodsin.plu = products.plu)group by pluunionselect plu, description, supplier_name, style, colour_name, size_name,sum(0) as 'stockqty',sum(0) as 'orderqty',sum(quantity) as 'saleqty'from productsinner join transactions on (transactions.full_plu_string = products.plu)group by pluorder by plu;/* DO NOT EDIT BELOW HERE*/select plu, description, supplier_name, style, colour_name, size_name,sum(orderqty) as 'In',sum(saleqty) as 'Out',sum(stockqty)as 'Balance'INTO MEMORY Yfrom memory xgroup by plu;/* display final report */select plu, supplier_name,cast(style as CHAR(16)), colour_name, size_name,IN as 'Ordered', OUT as 'Sold', BALANCE as 'Current Stock'from memory Ywhere balance > 0group by pluThank you for all your helpLouisa |
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-03-21 : 12:46:49
|
which table stores branchID? product?CheersMIK |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-22 : 06:56:03
|
unless you give some sample data from tables, its quite hard for someone to help you out. Post some sample data and explanation on how you want output to come------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
vennboo
Starting Member
2 Posts |
Posted - 2013-03-22 : 17:23:32
|
The branch stock table stores the branch_id.We would like to show for each branch what quantity has been ordered, sold and is available at each branch.Thank you for your help |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-03-22 : 18:17:26
|
The people that help out here are all un-paid volunteers. Providing the DDL (CREATE TABLE, CREATE INDEX, etc.), DML (INSERT statements) and Expected Output will go a long way in getting people to look at your issue and help you out. That way we can run our code against your data and the benefit to you is you get working code back. It's also a good idea to include code for what you have already tried. Here are some links that can help guide you to providing the needed information:http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
|
|
|
|