Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Help with running Total.

Posted on 2011-02-25
Medium Priority
217 Views
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')
``````
0
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

LVL 15

Accepted Solution

derekkromm earned 1600 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'
``````
0

LVL 75

Assisted Solution

Anthony Perkins earned 200 total points
ID: 34985649
What is your Primary Key or unique index?
0

LVL 15

Expert Comment

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')
``````

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

LVL 41

Assisted Solution

Sharath earned 200 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

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

LVL 11

Expert Comment

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

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

Author Closing Comment

ID: 35101504
thank u all!
0

## Featured Post

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
###### Suggested Courses
Course of the Month11 days, 23 hours left to enroll