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
8
Medium Priority
?
217 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 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'

Open in new window

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 41

Assisted Solution

by:Sharath
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

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

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…

636 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