Well Actually I have this situation
I have a table with
AccCode,
AccMovement,
AccYear, and
AccPeriod


and my data looks like this
AccCode AccYear AccPeriod movement
110401  1998    02      541870.00
110401  1998    03      1210000.00
110401  1998    04      2687330.00
110401  1998    05      1220450.00
110401  1998    06      3508000.00
110401  1998    07      5606620.00
110401  1998    08      1567080.00
110401  1998    09      150000.00
110401  1998    10      1202180.00
110401  1998    11      1373980.00
110401  1998    12      2604600.00


And this account has data for many years ,
Now i want to make a table like this
accCode , AccYear , AccPeriod, Opening balance , movement ,
OpeningBalance+Movement where opening balance = closing balnce(opening
balance + Movement) of
previus period


how can i do this ?

Hi

You could try something like:

CREATE TABLE AccBalance ( Acccode int, accyear int, accperiod smallint,
movement decimal(12,3) )
INSERT INTO AccBalance ( AccCode, AccYear, AccPeriod, movement )
SELECT 110401,  1998,    02,      541870.00
UNION ALL SELECT 110401,  1998,    03,      1210000.00
UNION ALL SELECT 110401,  1998,    04,      2687330.00
UNION ALL SELECT 110401,  1998,    05,      1220450.00
UNION ALL SELECT 110401,  1998,    06,      3508000.00
UNION ALL SELECT 110401,  1998,    07,      5606620.00
UNION ALL SELECT 110401,  1998,    08,      1567080.00
UNION ALL SELECT 110401,  1998,    09,      150000.00
UNION ALL SELECT 110401,  1998,    10,      1202180.00
UNION ALL SELECT 110401,  1998,    11,      1373980.00
UNION ALL SELECT 110401,  1998,    12,      2604600.00

DROP view CumulativeAccBalance

CREATE view CumulativeAccBalance
AS SELECT a.AccCode, a.AccYear, a.AccPeriod, a.movement, ISNULL( ( SELECT
SUM(b.movement) FROM AccBalance b where a.AccCode = b.acccode and a.AccYear =
b.accyear and a.AccPeriod > b.accperiod ),0) as OpeningBalance
FROM AccBalance a

SELECT AccCode, AccYear, AccPeriod, movement, OpeningBalance,  movement +
OpeningBalance AS ClosingBalance FROM CumulativeAccBalance

John

Show quoteHide quote

NOLOCK
INSERT table (column1, column2) (@variable, '100')
Simple JOIN turned ugly - Help!
Is the SQL Server Service Running?
Grouping,Paging Logic