Grouping, Multiple Inner Joins and more

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

SQL Server 2000 Forums

Transact-SQL (2000)

Grouping, Multiple Inner Joins and more

Author

Topic

curry
Starting Member

14 Posts

Posted - 2006-03-30 : 10:37:18

I am working on a stock control system using M$ SQL Server and VB6 and I'm stuck on one area. I need to create a report based on the following criteria.

Reorder Level (From a product table)
Stock Level (From a product table)
QTY on Sales Order (From a sales order table)
Outstanding QTY on Purchase Order (from a purchase order table)

I need to group records togeather by their name and show the "QTY on Sales Order" and "Outstanding QTY on Purchase Order" as a total.

I will have products that have a sales qty but no purchase qty and vise verser.

Using the following Statement I can get a list but its not correct and only lists products that have sales qty and purchase qty

SELECT tbl_products.supplierid, description, suppliername, status as purorderstatus, tbl_products.productid, tbl_purorderitems.qty as purorderqty, tbl_salesorderitems.qty as salesorderqty, tbl_purorderitems.recd as received, orderstatus as salesorderstatus, name, stklevel, rol, roq FROM ((tbl_purorders INNER JOIN tbl_suppliers ON tbl_purorders.supplierid = tbl_suppliers.supplierid) INNER JOIN tbl_purorderitems ON tbl_purorders.orderid = tbl_purorderitems.orderid) INNER JOIN ((tbl_salesorders INNER JOIN tbl_salesorderitems ON tbl_salesorders.orderid = tbl_salesorderitems.orderid) INNER JOIN tbl_products ON tbl_salesorderitems.productid = tbl_products.productid) ON tbl_purorderitems.productid = tbl_products.productid where tbl_salesorders.orderstatus='a' or tbl_salesorders.orderstatus='a' and tbl_purorders.status='0' order by tbl_suppliers.suppliername, tbl_products.name

Example

Product = ABC001
On Sales Order = 2
On Purchase Order = 3

Product = ABC001
On Sales Order = 1
On Purchase Order = 6

Product = ABC002
On Sales Order = 1
On Purchase Order = 1

Product = ABC002
On Sales Order = 4
On Purchase Order = 5

I need it to be like this

Product = ABC001
On Sales Order = 3 (The Total)
On Purchase Order = 9 (The Total)

Product = ABC002
On Sales Order = 5 (The Total)
On Purchase Order = 6 (The Total)

Product = ABC003
On Sales Order = 0 (The Total)
On Purchase Order = 4 (The Total)

I hope I have given enough information and made myself clear enough.

Thank you in advance

Matt

jhermiz

3564 Posts

Posted - 2006-03-30 : 11:04:46

Great thing you posted all the info we needed.
Your SQL should be something like the following:

SELECT Product, SUM(SalesOrder) As OnSalesOrder, SUM(PurchaseOrder) As OnPurchaseOrder
GROUP BY Product

Couple of things to note, if sales order and purchase order is simply a lookup value so that the qty is stored in one field you will simply need to case it out:

SELECT Product, CASE WHEN SellType='SalesOrder' THEN SUM(YourQtyField) END AS SalesOrder, CASE WHEN SellType='PurchaseOrder' THEN SUM(YourQtyField) END AS PurchaseOrder

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

curry
Starting Member

14 Posts

Posted - 2006-03-30 : 11:20:00

quote:
Originally posted by jhermiz

Great thing you posted all the info we needed.
Your SQL should be something like the following:

SELECT Product, SUM(SalesOrder) As OnSalesOrder, SUM(PurchaseOrder) As OnPurchaseOrder
GROUP BY Product

Couple of things to note, if sales order and purchase order is simply a lookup value so that the qty is stored in one field you will simply need to case it out:

SELECT Product, CASE WHEN SellType='SalesOrder' THEN SUM(YourQtyField) END AS SalesOrder, CASE WHEN SellType='PurchaseOrder' THEN SUM(YourQtyField) END AS PurchaseOrder

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Thanks for that, I didn't copy the correct SQL statement. The one I am currently working on does use sum(blah) as blahqty. I will try using CASE and see how I get on.

curry
Starting Member

14 Posts

Posted - 2006-03-31 : 03:46:26

I have never used CASE in a SQL statement before and to be honest even now I have tried it I am still none the wiser. Could I have a more detailed explaination of how I should use it in this instance please.

Thank you again in advance.

Matt

m2diwa@gmail.com
Starting Member

3 Posts

Posted - 2006-04-03 : 06:55:41

Will u just send the ur requirement in details

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources