SQL-Mathematical Function on a Table

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.

All Forums

Other Forums

MS Access

SQL-Mathematical Function on a Table

Author

Topic

fdtoo
Starting Member

28 Posts

Posted - 2004-09-19 : 05:36:39

Example:

Date | ItemCode | Stock_In_qty | Stock_Out_qty | Bal_qty
------------------------------------------------------------------

12/09/2003 | A100 | 20 | 0 | 20
25/10/2003 | A100 | 0 | 10 | *10

I need to query an Access database that will take field 3 (Stock_In_qty) plus any bal from
the above row in the calculated field (Bal_qty) minus field 4 (Stock_Out_qty) that will show me
the latest Bal_qty, note that Bal_qty = (Stock_In_Qty - Stock_Out_Qty) AS Bal_Qty.As an Example in the above scenario,
the Bal_qty in the second row on 25/10/2003 is (0 + 20(Row 1) - 10) = *10.Stock out not necessary comes from
Stock In, it could simply be taken out from the Bal_qty balance from previous month, any clues?

can anyone help? Thanks

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-19 : 19:23:09

This is one way to do it:

SELECT S.[Date], S.ItemCode, S.Stock_In_Qty, S.Stock_Out_Qty, (SELECT Sum(Stock_In_Qty) - Sum(Stock_Out_Qty)  FROM Stock WHERE ItemCode = S.ItemCode AND [Date] <= S.[Date]) as Bal_qty
FROM Stock S;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources