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 |
|
raymyster
Starting Member
10 Posts |
 Posted - 2009-10-20 : 10:39:53
|
Hello Community,I have 2 tables: stock, inventory  1) inventory table is read-only3) the query should take time efficiency in consideration.4) it should be 1 query, moreover a SELECT statement5) we can not change the table structure6) simplicity is the keyi added a table called query_result just to show what the resulting cursor should look like with correct values.the idea:get balance of inventory based on deduction of non-expired movie rents in order table.means that everything after DATE() is still rented, everything before DATE() should not be taken into consideration anymore.the result:same as query_result table, that hold the same fields as inventory table but with UP TO DATE balances. The database is attached in a zip file. I experimented with this quite some time & have couple of results but are far from perfection.[url]http://www.coderisland.com/data.zip[/url]here is my non-working far from perfect query, that is more sql like then access like:[CODE]SELECT INVENTORY.item_id,INVENTORY.ITEM_NAME,INVENTORY.qty,(SELECT NVL(SUM(ORDER.qty),0) FROM ORDER WHERE to_date=< DATE() AND ORDER.item_id=INVENTORY.item_id) AS IQTY FROM inventory[/CODE]I hope i get some help.Best regards.Share your knowledge it's one way to achieve immortality http://www.rabihtawil.com |
|
|
rihanna
Starting Member
3 Posts |
Posted - 2009-11-15 : 18:41:19
|
| Hi All,Whats your query?Sorry to say, haven't got your problem.Bulk Sms |
 |
|
|
|
|
|
Best select to get balance