How to use View in MSSQL to get temp data?
Posted on 2009-04-14
I have a table to store a opening balance of stock value.
e.g. WarehouseID = 1, ProductID=1, Amount=9880.5
and there is a transactions table to add and reduce the amount when buy or sell transactions make.
e.g. WarehouseID = 1, ProductID=1, Amount=300, Type=Sell, datetime = 2009=04-01'
e.g. WarehouseID = 1, ProductID=1, Amount=200, Type=Sell, datetime = 2009=04-03'
e.g. WarehouseID = 1, ProductID=1, Amount=50, Type=Buy, datetime = 2009=04-04'
Then the update to date balance of amount is 9880.5 - 300 - 200 + 50 and it will keep on changing.
If I want to display the updated balance or in a particular date, I write a stored procedure to calculate and return the value. But, I do not know how can I display the data (over 100 products in 20 warehouses) in aspx with C# as the data is temporarily calculated and not stored in table. Should I build a view or how to build a temp table to let C# to do data bind for display in a listview?