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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help Needed to produce SQL Report

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 x
from products
inner join branchstock on (branchstock.plu = products.plu)
group by plu
union

/* 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 products
inner join bookgoodsin on (bookgoodsin.plu = products.plu)
group by plu
union

select plu, description, supplier_name, style, colour_name, size_name,
sum(0) as 'stockqty',
sum(0) as 'orderqty',
sum(quantity) as 'saleqty'
from products
inner join transactions on (transactions.full_plu_string = products.plu)
group by plu
order 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 Y
from memory x
group 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 Y
where balance > 0
group by plu


Thank you for all your help
Louisa

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-03-21 : 12:46:49
which table stores branchID? product?

Cheers
MIK
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

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

Go to Top of Page
   

- Advertisement -