Solved

Help with running Total.

Posted on 2011-02-25
8
205 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
8 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 400 total points
Comment Utility
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
Comment Utility
What is your Primary Key or unique index?
0
 
LVL 15

Expert Comment

by:derekkromm
Comment Utility
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
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 50 total points
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 15

Expert Comment

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

Expert Comment

by:yuching
Comment Utility
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
Comment Utility
I suspect the author has left the building or just does not want to provide any further feedback.
0
 

Author Closing Comment

by:epicazo
Comment Utility
thank u all!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
table fragmentation 40 73
How to convert JSON file to csv? 7 49
SQL JOIN 6 31
search for a string in all tables 4 15
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now