The place to find the database for the SQL Statement is [url removed, login to view]
There are 4 tables. The relationships between the tables are these:
itemsown.inventory=[url removed, login to view]
itemsold.inventory=[url removed, login to view]
invoices.partnum=[url removed, login to view]
itemsold.invoice=[url removed, login to view]
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 SQL Statement to show the sells from the last 30 days. The columns to show are these:
1. Part Number (found on [url removed, login to view])
2. Items Sold
4. Items Have In Stock (even show nulls)
Show ones after 10/07/03
Comment on the different parts of the statement.