Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 602
  • Last Modified:

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??

0
perove
Asked:
perove
  • 4
  • 2
1 Solution
 
tchalkovCommented:
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.
0
 
peroveAuthor Commented:
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
0
 
tchalkovCommented:
Here is the TSQL statement which goes through table1 and writes the resluts in table2 then prints the resluts.
Create a job on the server with one step of TSQL type. Then place the code there. Or alternatively you can create a stored procedure and call it instead of putting the entire code.

use pubs
declare mycursor cursor for select * from table1 order by account,year,period
open mycursor
declare @id int,@account integer, @period integer , @year integer, @amount float
declare @curaccount integer, @curyear integer
declare @rntot float, @rnyear float
fetch next from mycursor into @id, @account, @period, @year , @amount
select @rntot=0
select @rnyear=0
select @curyear=@year
select @curaccount=@account
delete from table2
while (@@FETCH_STATUS=0)
begin
      if (@curaccount=@account)
      begin
            select @rntot=@rntot+@amount
            if (@curyear=@year)
                  select @rnyear=@rnyear+@amount
            else
            begin
                  select @rnyear=@amount
                  select @curyear=@year
            end
      end
      else
      begin
            select @rntot=@amount
            select @rnyear=@amount
            select @curyear=@year
            select @curaccount=@account
      end
      insert table2(account,period,year,amount,rntot,rnyear) values(@account, @period, @year, @amount, @rntot, @rnyear)
      fetch next from mycursor into @id, @account, @period, @year , @amount
end
select * from table2
deallocate mycursor
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
peroveAuthor Commented:
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
0
 
peroveAuthor Commented:
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

0
 
peroveAuthor Commented:
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 scripting)

perove

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now