Link to home
Start Free TrialLog in
Avatar of epicazo
epicazoFlag for United States of America

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of derekkromm
derekkromm
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That is correct, apologies, forgot this was 2000. The common table expression wouldn't work either.
Avatar of yuching
yuching

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')
I suspect the author has left the building or just does not want to provide any further feedback.
Avatar of epicazo

ASKER

thank u all!