debug sql statement (repost)

Completed Posted Dec 4, 2003 Paid on delivery
Completed Paid on delivery

The place to find the database for the SQL Statement is [url removed, login to view] There are 6 tables. The relationships between the tables are these: itemsown.inventory=[url removed, login to view] itemsold.inventory=[url removed, login to view] itemsold.invoice=[url removed, login to view] inventory.id=[url removed, login to view] exceptions.id=[url removed, login to view] exceptiondetails.exception=[url removed, login to view] Here is the statement I have so far which is almost correct but not quite. newdate=DateAdd("d", "-30", Now) sqls="SELECT [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], Sum([url removed, login to view]) AS ItemsSold, [url removed, login to view], (SELECT Sum([url removed, login to view]) FROM itemsown WHERE [url removed, login to view] = [url removed, login to view]) AS ItemsHaveInStock" sqls=sqls & " FROM inventory INNER JOIN itemsold ON [url removed, login to view] = [url removed, login to view] where [url removed, login to view]>'" & newdate & "' AND itemsold.shouldhome='y'" sqls=sqls & " GROUP BY [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view] UNION ALL SELECT [url removed, login to view], [url removed, login to view], [url removed, login to view], [url removed, login to view], '', null, (SELECT Sum([url removed, login to view]) FROM itemsown" sqls=sqls & " WHERE [url removed, login to view] = [url removed, login to view]) AS ItemsHaveInStock FROM inventory WHERE NOT EXISTS (SELECT [url removed, login to view] FROM itemsold" sqls=sqls & " WHERE [url removed, login to view] = [url removed, login to view] AND [url removed, login to view]>'" & newdate & "' AND itemsold.shouldhome='y') ORDER BY [url removed, login to view]" The only problem is if a left side sells, but we have a right side, it shows the sell and shows we had the left side even though we did not. The example is part number 8-04. We sold a left side. There is no left side in stock. In this case, it should show 2 entries. One to show we sold a left side with none in stock and another one to show we sold 0 of the right side but have one in stock. Everything else is fine. What this statement does is shows what needs to be ordered. It is used with another formula to determine whether to show on the page (if number sold is greater than the number we have in stock). Below is information from the previous bid request. See deliverables for more information.

## Deliverables

The completed SQL Statement to account for the side sold and the side I have in inventory. If there is no side, then everything is correct. Otherwise, if there is more than one side, show both. Even if its 0 sold and 0 in inventory for the statement. If the structure needs to be changed, that is fine as well. if an inventory item is in the itemsold table but nothing matching in the itemsown, then show it as null. The statement should return all the sales (count them) from the last 30 days. If nothing corresponds to the itemsown, then show that column as null. Also, from the itemsold, it should only show where shouldhome='y' The information retrieved should be something like this Inventory Number, Items Sold, Side, Items Currently Have For this sample, just show all the ones after 10/07/03. I can change it later to show for the last 30 days. The inventory number needs to be the [url removed, login to view] needs to group by [url removed, login to view] itemsold should count how many sold of the inventory item. items currently have should either be 0 if the current amount is 0. For the itemsown, no sum function is necessary. It needs to be grouped by side as well. It may or may not be null. The purpose of this is to show based on the 30 day sells, what items needs to be ordered. If there is one with a left and a right, show them twice. Let me know if you have any other questions. The statement needed will be similar but take into account of what side was sold and compared to what side I have in stock.

## Platform

Windows 2000, Access, SQL Server 7.0

Engineering Microsoft Microsoft Access MySQL PHP Software Architecture Software Testing SQL Windows Desktop

Project ID: #3026306

About the project

1 proposal Remote project Active Dec 4, 2003

Awarded to:

justserg

See private message.

$12 USD in 1 day
(140 Reviews)
6.7