Solved

Help with running Total.

Posted on 2011-02-25
8
214 Views
Last Modified: 2012-05-11
Why is it that my  runningTotal is not working?



Data Output....

PtID                              BRSVDT     ccActualBrAmt            RunningTotal                            
--------------------------------- ---------- ------------------------ ----------------------------------------
20-3199122                        20080129   22.16                    1471647.31
20-3199122                        20080129   59.12                    1471647.31
20-3199122                        20080129   11.81                    1471647.31
20-3199122                        20080129   53.22                    1471647.31
20-3199122                        20080128   121.24                   1471647.31
20-3199122                        20080128   22.16                    1471647.31
20-3199122                        20080128   39.91                    1471647.31
20-3199122                        20080128   59.12                    1471647.31
20-3199122                        20080128   29.56                    1471647.31
20-3199122                        20080128   59.12                    1471647.31
SELECT PtID, BRSVDT, ccActualBrAmt,
                   (SELECT SUM(ccActualBrAmt) AS Expr1
                    FROM   dbo.VW_HBRVDTFL
                    WHERE (PtID <= t.PtID)) AS RunningTotal
FROM  dbo.VW_HBRVDTFL AS t
WHERE (PtID = '20-3199122')

Open in new window

0
Comment
Question by:epicazo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 400 total points
ID: 34985125
There's no running total to be had because the ID you're using is the same for everything. If you had an identity column as well, it'd be different:

declare @t table (
	id int identity,
	ptid varchar(100),
	actual decimal(10,2))
	
insert into @t (ptid,actual)
select '20-3199122', 22.16
union select '20-3199122', 59.12
union select '20-3199122', 11.81
union select '20-3199122', 53.22
union select '20-3199122', 121.24

select id,ptid,actual,(select SUM(actual) from @t t1 where t1.ptid=t.ptid and t1.id <= t.id) as 'RunningTotal'
from @t t
where ptid='20-3199122'

Open in new window

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 34985649
What is your Primary Key or unique index?
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 34987923
actually i thought of a way you could work this with your current structure:

with rownums as (
	select PtID, BRSVDT, ccActualBrAmt, ROW_NUMBER() over (partition by PtID, order by PtID) as 'rownum' from dbo.VW_HBRVDTFL)
	
SELECT PtID, BRSVDT, ccActualBrAmt,
                   (SELECT SUM(ccActualBrAmt) AS Expr1
                    FROM   rownums
                    WHERE PtID = t.PtID and (rownum <= t.rownum)) AS RunningTotal
FROM  rownums AS t
WHERE (PtID = '20-3199122')

Open in new window


with that, you could even remove the final WHERE PtID = '...' and it should give running totals for each separate PtID i believe, based on the ordering in the table.
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 41

Assisted Solution

by:Sharath
Sharath earned 50 total points
ID: 34988477
ROW_NUMBER is not implemented in SQL Server 2000. There is no other way unless there is a primary key or unique index in the table.
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 34988864
That is correct, apologies, forgot this was 2000. The common table expression wouldn't work either.
0
 
LVL 11

Expert Comment

by:yuching
ID: 35014006
We can use field  BRSVDT and ccActualBRAmt as running total

SELECT PtID, BRSVDT, ccActualBrAmt,
    (SELECT SUM(ccActualBrAmt) AS Expr1
      FROM   dbo.VW_HBRVDTFL a
      WHERE (a.PtID = t.PtID AND  a.BRSVDT <= t.BRSVDT AND a.ccActualBrAmt <= t.ccActualBrAmt))
       AS RunningTotal
FROM  dbo.VW_HBRVDTFL AS t
WHERE (PtID = '20-3199122')
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35038915
I suspect the author has left the building or just does not want to provide any further feedback.
0
 

Author Closing Comment

by:epicazo
ID: 35101504
thank u all!
0

Featured Post

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SCOM to SQL port 14xx failed? 1 32
Linked Server - SP with Param to VIew 7 25
Related to SQL Query 5 21
Estimating my database size 7 21
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

730 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