epicazo
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
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')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That is correct, apologies, forgot this was 2000. The common table expression wouldn't work either.
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')
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.
ASKER
thank u all!
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.