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.
| Author |
Topic |
|
JesseD
Starting Member
1 Post |
 Posted - 2004-10-28 : 04:12:50
|
| Hi all!I'm building an inventory control application, but now I'm having a problem with a query. The problem concerns two tables, lets say table1 and table2.In table1 every available size is listed with the current stock. Every time a delivery takes place the stock will be raised by the delivered item count. At the same moment the id, date of delivery and the deliverd item count will be inserted in table2. This is to calculate historical stock count for certain sizes.Every time a size item has been sold, the id, date of sale and the negative saled item count will be inserted in table 2.To calculate historical stock count for a certain size, you can make use a sum-function that will add the deliverd items and deletes the sold items before a certain date. For example: select table1.size, table1.stock, sum(table2.count) as oldstock from table1 full outer join table2 on table2.id=table1.id where table2.date <= '2004-10-02' group by table1.size, table1.stockThe query above displays the size, actual stock and the historical stock for all sizes at 2004-10-02. Here comes the problem:If I want to list all sizes, current stocks and old stocks, including sizes, current stocks and old stocks (in this case "null") which have been deliverd after 2004-10-02, how do I have to do this? I have allready built it to include sizes for products which haven't been deliverd yet. The do not exist in table 2. The complete statement till now is:select table1.size, table1.stock, sum(table2.count) as oldstock from table1 full outer join table2 on table2.id=table1.id where table2.date <= '2004-10-02' or table1.id not in (select table1id from table2) group by table1.size, table1.stockI need to complete the statement with something that will include the sizes which have been delivered after the date, but with the actual stock and the oldstock at "null" or "0". Does anyone know? ThanxJessePS - My appologies for my bad English |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-10-28 : 09:52:04
|
post table definitions, sample data in the form of insert into statments and desires result BASED ON the sample data you provide. only that way will you get the needed help fastest.Go with the flow & have fun! Else fight the flow  |
 |
|
|
|
|
|
|
|
Complicated statement