SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help Needed to produce SQL Report
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vennboo
Starting Member

2 Posts

Posted - 03/21/2013 :  12:04:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/21/2013 :  12:46:49  Show Profile  Reply with Quote
which table stores branchID? product?

Cheers
MIK

Edited by - MIK_2008 on 03/22/2013 08:04:34
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 03/22/2013 :  06:56:03  Show Profile  Reply with Quote
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 - 03/22/2013 :  17:23:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 03/22/2013 :  18:17:26  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000