Solved

Running sum- plain SQL

Posted on 2000-02-14
6
573 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

828 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