Help with running Total.

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

epicazoAsked:
Who is Participating?
 
derekkrommConnect With a Mentor Commented:
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
 
Anthony PerkinsConnect With a Mentor Commented:
What is your Primary Key or unique index?
0
 
derekkrommCommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
SharathConnect With a Mentor Data EngineerCommented:
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
 
derekkrommCommented:
That is correct, apologies, forgot this was 2000. The common table expression wouldn't work either.
0
 
yuchingCommented:
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
 
Anthony PerkinsCommented:
I suspect the author has left the building or just does not want to provide any further feedback.
0
 
epicazoAuthor Commented:
thank u all!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.