Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Running sum- plain SQL

Posted on 2000-02-14
6
Medium Priority
?
598 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 800 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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 …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

916 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