My Problem with calculated field

I have a table for tracking the movement of cash register it has the fields

ID
Date
Amount_Debit
Amount_Credit
Explanation

I want to add a calculated field named balance that shows the actual balance of the cachregister after the entry in the row I

used this but it gave me an error

sum(amount_debit-amount_credit) where id<=id

the general idea is to get the summary of the rows that has an Id equal or less than the row id of the row that I want to show

the balance of it

can any one help me to get the desired result in the best way possible

additional question if possible can I get correct cash balance if I drop the ID field depending on date

I use SQL Server 2005 Express

hi,

Samer Selo wrote:

I have a table for tracking the movement of cash register it has the fields

ID
Date
Amount_Debit
Amount_Credit
Explanation

I want to add a calculated field named balance that shows the actual balance of the cachregister after the entry in the row I

used this but it gave me an error

sum(amount_debit-amount_credit) where id<=id

the general idea is to get the summary of the rows that has an Id equal or less than the row id of the row that I want to show

the balance of it

can any one help me to get the desired result in the best way possible

additional question if possible can I get correct cash balance if I drop the ID field depending on date

I use SQL Server 2005 Express

you can not directly define a computed column in the object's DDL for this kind of task, but you are not required as well, as this value is dynamic and dependent on ordering and the like..

you can of course dynamically get this value in a "running total" query like

SET NOCOUNT ON;

USE tempdb;

GO

CREATE TABLE dbo.myTB (

Id int NOT NULL IDENTITY PRIMARY KEY,

Date datetime NOT NULL,

Amount_Debit decimal ( 18, 4 ) NOT NULL DEFAULT 0,

Amount_Credit decimal ( 18, 4 ) NOT NULL DEFAULT 0,

Explanation varchar(10) NULL

);

GO

INSERT INTO dbo.myTB VALUES ('20060101', 0, 10, '' );

INSERT INTO dbo.myTB VALUES ('20060101', 10, 0, '' );

INSERT INTO dbo.myTB VALUES ('20060102', 0, 10, '' );

INSERT INTO dbo.myTB VALUES ('20060103', 0, 10, '' );

INSERT INTO dbo.myTB VALUES ('20060105', 15, 0, '' );

GO

SELECT t.Id, t.Date, t.Amount_Credit, t.Amount_Debit, (SUM(t2.Amount_Credit) - SUM(t2.Amount_Debit)) AS [Balance]

FROM dbo.myTB t

CROSS JOIN dbo.myTB t2

WHERE t2.Id <= t.Id

GROUP BY t.Id, t.Date, t.Amount_Credit, t.Amount_Debit

ORDER BY t.Id;

GO

DROP TABLE dbo.myTB;

--<--

Id Date Amount_Credit Amount_Debit Balance

-- -- -- -- -

1 2006-01-01 00:00:00.000 10.0000 0.0000 10.0000

2 2006-01-01 00:00:00.000 0.0000 10.0000 0.0000

3 2006-01-02 00:00:00.000 10.0000 0.0000 10.0000

4 2006-01-03 00:00:00.000 10.0000 0.0000 20.0000

5 2006-01-05 00:00:00.000 0.0000 15.0000 5.0000

regards

|||Thank You I'll Try that