SQL Queries - SQL Server Forums

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

SQL Queries

Author

Topic

twells02
Starting Member

6 Posts

Posted - 2008-03-18 : 22:27:23

I am stuck writing a code that will give me the following information...Shows the dollar amount of brass cap fittings sold to customers located in Illinois during March 2005. I need the following displayed in the query Customer name, city, invoice date, inventory diameter, quantity and extended price. Sort the output by extended price with largest first. Name the dynaset "Brass Cap Fittings sold to customers in Illinois March 2005". The problem I am having is getting the City, Diameter, and Price data to show up. I also do not have a clue of how to limit the data to including only brass fitings, only sold to IL customers, and sold only in March of 2005. Here is a sample of the query I have to adjust and the current SQL code associated with it...

SELECT tblCustomer.CustomerName, tblCustomer.City, tblSales.InvoiceDate, tblInventory.Diameter, tblSalesInventory.Quantity, [SInvPrice]*[SInvQuantity] AS Extension
FROM (tblCustomer INNER JOIN tblSales ON tblCustomer.CustomerNumber = tblSales.CustomerNumber) INNER JOIN (tblInventory INNER JOIN [tblSales-Inventory] ON tblInventory.InventoryItemCode = [tblSales-Inventory].InventoryItemCode) ON tblSales.InvoiceNumber = [tblSales-Inventory].InvoiceNumber
ORDER BY tblSales.InvoiceNumber, [tblSales-Inventory].InventoryItemCode;

Name City Date Diameter quantity Extension
Dunn Plumbing 1/23/2005 $651.90
Dunn Plumbing 1/23/2005 $398.86
Dunn Plumbing 1/23/2005 $747.50
Dunn Plumbing 1/23/2005 $58.50
Cole & Co. 1/23/2005 $522.50
Cole & Co. 1/23/2005 $65.80
Cole & Co. 1/23/2005 $42.90
Lin Plumbing Repair, Inc. 1/23/2005 $439.00
Lin Plumbing Repair, Inc. 1/23/2005 $262.50

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-18 : 22:45:37

It's pretty simmilar to your other question. Add the sum & group by. Where clause needs to contain date range and state. Only you know where that information is stored without your table definitions. And BTW this forum is SQL Server not access.

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2008-03-19 : 11:30:49

I need the following displayed in the query Customer name, city, invoice date, inventory diameter, quantity and extended price
SELECT tblCustomer.CustomerName, tblCustomer.City, tblSales.InvoiceDate, tblInventory.Diameter, tblSalesInventory.Quantity, [SInvPrice]*[SInvQuantity] AS Extension

The problem I am having is getting the City, Diameter, and Price data to show up.
Because you have that on your select statement, you only need to select what you need to be displayed, right? You need a WHERE clause.

For more info on how to use the WHERE clause, go to:
http://msdn2.microsoft.com/en-us/library/ms188047.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources