Encerrado

debug SQL Statement

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

SQL Server 7.0, Access, Windows 2000

Habilidades: ASP, Engenharia, Microsoft, Microsoft Access, MySQL, PHP, Arquitetura de software, Teste de Software, SQL, Visual Basic, Área de trabalho do Windows

Ver mais: what is the problem statement, what is problem statement, what is a problem statement example, what is a problem statement, what is an example of a problem statement, union find problem, union find, statement problem example, statement problem, statement of the problem sample, statement of the problem example, statement of problem sample, statement of a problem, sample problem statement, sample of problem statement, return invoice sample, problem statement sample, problem statement example, problem statement, parts of a problem statement, parts inventory database, it problem statement, how to find the right items to sell, example problem statement, example of statement of the problem

Acerca do Empregador:
( 781 comentários ) Sydney, Australia

ID do Projeto: #3022627

6 freelancers estão ofertando em média $195 para este trabalho

springdotnetvw

See private message.

$68 USD em 1 dia
(137 Comentários)
7.2
anthonyselby

See private message.

$3.4 USD em 1 dia
(18 Comentários)
3.7
MattchedIT

See private message.

$12.75 USD em 1 dia
(17 Comentários)
3.4
jvmvw

See private message.

$21.25 USD em 1 dia
(3 Comentários)
1.3
linkcalifornia

See private message.

$255 USD em 1 dia
(1 Comentário)
0.0
extendedbrain

See private message.

$807.5 USD em 1 dia
(0 Comentários)
0.0