Solved

Running sum- plain SQL

Posted on 2000-02-14
6
549 Views
Last Modified: 2008-03-03
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
Comment
Question by:perove
  • 4
  • 2
6 Comments
 
LVL 7

Expert Comment

by:tchalkov
ID: 2521833
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
 
LVL 9

Author Comment

by:perove
ID: 2521909
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
 
LVL 7

Accepted Solution

by:
tchalkov earned 200 total points
ID: 2522012
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 9

Author Comment

by:perove
ID: 2522110
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
 
LVL 9

Author Comment

by:perove
ID: 2522844
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
 
LVL 9

Author Comment

by:perove
ID: 2522845
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Export  Access Query To Excell 16 43
bind Combobox 4 28
MS Access XML API HTTP POST Call Object Send Not Working Correctly 15 34
Query design issue 2 18
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now