Need Help Producing a List. - 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

Need Help Producing a List.

Author

Topic

twells02
Starting Member

6 Posts

Posted - 2008-03-18 : 19:56:03

I am not sure the SQL code to use...I am to produce a list that shows total sales by customer for the first two months of the year. I am to show the customer name, customer number, and total sales in that order. The current format I have it in list every invoice separately and what I need to do is find a way (the code) to combine the invoice totals into one total amount and only have the company name listed once. I also do not know how to set a date range so it includes invoices only from 1/1/2005 to 2/28/2005 Right now this is the current list and SQL code I have:

Name Customer Number Date Sales
Ace Construction Co. 10002 3/18/2005 $62.25
Ace Construction Co. 10002 2/13/2005 $62.25
Ace Construction Co. 10002 2/13/2005 $97.50
Ace Construction Co. 10002 2/13/2005 $89.00
Ace Construction Co. 10002 2/13/2005 $194.25
Ace Construction Co. 10002 1/29/2005 $24.90
Ace Construction Co. 10002 3/18/2005 $67.80
Ace Construction Co. 10002 3/18/2005 $304.15
Ace Construction Co. 10002 1/29/2005 $695.00
Ace Construction Co. 10002 1/29/2005 $19.50
Ace Construction Co. 10002 3/18/2005 $299.00
Aho Electrical Services 10023 3/20/2005 $449.55
Aho Electrical Services 10023 3/20/2005 $149.55
Aho Electrical Services 10023 3/20/2005 $994.70
Aho Electrical Services 10023 3/20/2005 $276.45
Barren Construction 10050 1/30/2005 $298.41
Barren Construction 10050 2/25/2005 $62.85

SELECT DISTINCTROW tblCustomer.CustomerName, tblSales.CustomerNumber, tblSales.InvoiceDate, [SInvPrice]*[SInvQuantity] AS Sales
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;

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-18 : 20:35:52

Hard to say from your example but I think it will be something like this:

SELECT tblCustomer.CustomerName, tblSales.CustomerNumber, tblSales.InvoiceDate, sum([SInvPrice]*[SInvQuantity]) AS Sales
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
where tblSales.InvoiceDate between '20050101' and '20050228'
group by tblCustomer.CustomerName, tblSales.CustomerNumber, tblSales.InvoiceDate
ORDER BY tblCustomer.CustomerName

The distinct concerns me - that usually means some join is wrong.

twells02
Starting Member

6 Posts

Posted - 2008-03-18 : 20:42:06

Is there any other details that I can give you that would help you out??

twells02
Starting Member

6 Posts

Posted - 2008-03-18 : 20:43:37

When I tried your code it gave me this message "Data type mismatch in criteria expression"

twells02
Starting Member

6 Posts

Posted - 2008-03-18 : 21:01:12

Ok this code works for getting all of the companies as one line, however the only thing that is not working is the specific date range.

SELECT tblCustomer.CustomerName, tblSales.CustomerNumber, sum([SInvPrice]*[SInvQuantity]) AS Sales
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
WHERE tblSales.Date between '01/01/2005' and '02/28/2005'
GROUP by tblCustomer.CustomerName, tblSales.CustomerNumber
ORDER BY tblCustomer.CustomerName

twells02
Starting Member

6 Posts

Posted - 2008-03-18 : 21:26:45

Ok this code works for getting all of the companies as one line, however the only thing that is not working is the specific date range. It asks me to Enter a Parameter Value for the "tblSales.Date". Whenever I enter a date it totals the enter year instead of just the first 2 months.

SELECT tblCustomer.CustomerName, tblSales.CustomerNumber, sum([SInvPrice]*[SInvQuantity]) AS Sales
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
WHERE tblSales.Date between '01/01/2005' and '02/28/2005'
GROUP by tblCustomer.CustomerName, tblSales.CustomerNumber
ORDER BY tblCustomer.CustomerName

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-18 : 22:41:03

Because your column is actually called InvoiceDate I suspect. This is getting a bit Access specific...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources