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 |
|
bettyatolive
Starting Member
21 Posts |
 Posted - 2006-06-08 : 05:03:14
|
| I am working on Stocks. and i have 3 tables:stock,stockIn,stockOut. Stosk stores the items. StockIn stores the coming or received stock,StockOut stores the outgoing sales. The table structures are as follows:Stock(Id,name,description,unitprice,stockInHand,stockSold)StockIn(Id_fk,received,date,details,receiptno)StockOut(Id_fk,sold,date,receiptno,salesamount,discount)Now i have to get the opening balance and closing balance for a particular day( for each item in the stock table). i.e. If i require 8thJune details the the opening stock wwould be the prevoius day closing stock.How should i do tht??? I'm not able to solve this..Somebody help me out please....... |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-06-08 : 05:30:08
|
| Terrible naming standards and probably structurewhat does stock hold? Is it the current stock level or a starting level.this might helpselect s.id, name = max(s.name), val = i.val - o.valfrom stock sjoin (select id = id_fk, val = sum(received) from StockIn where date <= @date group by id_fk) i on s.id = i.idjoin (select id = id_fk, val = sum(salesamount) from StockOut where date <= @date group by id_fk) o on s.id = o.id==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
bettyatolive
Starting Member
21 Posts |
Posted - 2006-06-08 : 05:45:10
|
| Stock table holds the current value of stock. And what do you mean by "val"? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-06-08 : 06:11:10
|
| val is the alias name given to sum(received)MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Query help