asked on

Running sum- plain SQL

Hi Having the table , with fields:

Account
Year
Period
Amount

I want to create a new table (create table SQL)

Account
Year
Period
Amount
RnYear
RnTot

Where the two last fields are running totals (over year and over all)

This is to be done in a query in MSDE or SQL server so i cannot use DSum(), or any VBA etc.. plain SQL

If I have to do it in two SQL (first create and then update so be..)

An examp:

Account Period Year Amount
2020 1 1998 500
2020 2 1998 200
2089 1 1998 400
2089 3 1998 300
2020 1 1999 500
2020 2 1999 200
2089 1 1999 400
2089 3 1999 300

Sould give me in the new table

Account Period Year Amount rnTot rnYear
2020 1 1998 500 500 500
2020 2 1998 200 700 700
2020 1 1999 500 1200 500
2020 2 1999 200 1400 700
2089 1 1998 400 400 400
2089 3 1998 300 700 700
2089 1 1999 400 1100 400 2089 3 1999 300 1400 700

I'm going to create a accumulative table for speeding up accounting reports, so I'm gonna delete and re-create the table every night.

anyone??

tchalkov

The TSQL(the language of SQL Server and msde) is not plain SQL - it is very powerful.

So my suggestion is to use a cursor, which contains the records in the first table ordered by account, year and period and a loop over the records in the cursor.
So in this loop it is very easy to calculate what you need and put it in another table.

If you need help with the TSQL which will do the work - say and I'll help you.

perove

ASKER

Yes, Help me.
I'm a rather experienced Access/jet & visual basic developer, but when it comes to SQl server 7.0 im more of a novise.

Using the SQLDMO I can create job that I schedule to run wher needed, so if the TSQL can be run as a job on SQL server I'm happy-very happy.

Give me som ex. on where to put the Tsql on the server and how to add these into a job, and I'm there (i think)

thanks for a quick reply,

Keep it up

perove

ASKER CERTIFIED SOLUTION

tchalkov

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

perove

ASKER

Will test it immidiatly, it's looking good and straight-froward, as ususl the problem when starting working with new tool it is to know the POSSIBILITY on what you can.

Anyway thanks a mill, I will test this and let you know!

regards from perove in Norway

perove

ASKER

Yes, got it, had to modefy som of the code, (it's int not integer and so on....) but I got the whole picture.

Excellent answer tchalkov! , and again I have to think about how great this site is (you hear EE)!

Anyway, thanks a mill for the input (you have any links for T-SQL debugging and advaced scriting)

perove

perove

ASKER