SQL- Calculated Field -- sql server programming

Cady Steldyn
9/11/2004 9:51:34 PM

SQL- Calculated Field

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

*** Sent via Developersdex http://www.developersdex.com ***

Uri Dimant
9/12/2004 8:28:37 AM

Cady
untested
DROP TABLE #Test

CREATE TABLE #Test
(
col DATETIME NOT NULL,
col1 VARCHAR(40) NOT NULL,
col2 INT NOT NULL,
col3 INT NOT NULL
)

INSERT INTO #Test VALUES ('20030912','A100',20,0)
INSERT INTO #Test VALUES ('20031025','A100',0,10)

SELECT *,(SELECT SUM(ISNULL(col2-col3,0)) FROM #Test T WHERE
T.col<=#Test.col) AS D
FROM #Test

[quoted text, click to view]

sql server programming : SQL- Calculated Field