how to do running totals?

I know this is probably best left to the client side but I was wondering how
to do this on the SQL side too.
If the data is as follows:
CheckDate CheckNum PmtAmount
10-1-05 DEP 15.00
10-1-05 1 -5.00
10-2-05 2 -3.00
10-2-05 3 -2.00
10-2-05 5 -10.00
10-3-05 4 -1.00
10-4-05 DEP 100.00
I'd like a resultant output as follows
CheckDate CheckNum PmtAmount Running Total
10-1-05 DEP 15.00 15.00
10-1-05 1 -5.00
10.00
10-2-05 2 -3.00
7.00
10-2-05 3 -2.00 5.00
10-2-05 5 -10.00 -5.00
10-3-05 4 -1.00 -6.00
10-4-05 DEP 100 94.00
The check numbers are out of sequence on purpose to show that sorting is by
date and then by check number. The ID field in the table might be way out of
order if the user enters transactions out of sequence so it can't be
sorting.
I don't want to do this in a stored procedure but in a select statement of
some kind. Is there a way to acheive this in SQL 2k? If so, how?
Thanks,
KeithWARNING!!!! THIS IS A KLUDGE! USE ONLY FOR ACADEMIC PURPOSES!
Now, that's out of the way :)
SELECT r1.CheckDate, r1.CheckNum, r1.PmtAmount,
(SELECT SUM(PmtAmount)
FROM RunningTotal r2
WHERE (CONVERT(varchar(8), r2.CheckDate, 112) +
CASE WHEN ISNUMERIC(r2.CheckNum)=1 THEN 'Z' + r2.CheckNum
ELSE r2.CheckNum END) <= (CONVERT(varchar(8), r1.CheckDate, 112) +
CASE WHEN ISNUMERIC(r1.CheckNum)=1 THEN 'Z' + r1.CheckNum
ELSE r1.CheckNum END) ) as Total
FROM RunningTotal r1
ORDER BY (CONVERT(varchar(8), r1.CheckDate, 112) +
CASE WHEN ISNUMERIC(r1.CheckNum)=1 THEN 'Z' + r1.CheckNum
ELSE r1.CheckNum END)
Obviously, the join and the running total is dependent on the order
specified, which is why it's MUCH better to let your client application
handle this. However, if you're using something with limited
capability (say, text based emails) this will work.
Stu|||On Fri, 14 Oct 2005 20:58:17 -0400, Keith G Hicks wrote:

>I know this is probably best left to the client side but I was wondering ho
w
>to do this on the SQL side too.
>If the data is as follows:
>CheckDate CheckNum PmtAmount
>10-1-05 DEP 15.00
>10-1-05 1 -5.00
>10-2-05 2 -3.00
>10-2-05 3 -2.00
>10-2-05 5 -10.00
>10-3-05 4 -1.00
>10-4-05 DEP 100.00
>I'd like a resultant output as follows
>CheckDate CheckNum PmtAmount Running Total
>10-1-05 DEP 15.00 15.00
>10-1-05 1 -5.00
>10.00
>10-2-05 2 -3.00
>7.00
>10-2-05 3 -2.00 5.0
0
>10-2-05 5 -10.00 -5.0
0
>10-3-05 4 -1.00 -6.0
0
>10-4-05 DEP 100 94.00
>The check numbers are out of sequence on purpose to show that sorting is by
>date and then by check number. The ID field in the table might be way out o
f
>order if the user enters transactions out of sequence so it can't be
>sorting.
>I don't want to do this in a stored procedure but in a select statement of
>some kind. Is there a way to acheive this in SQL 2k? If so, how?
Hi Keith,
Here's another possibility. Untested, since you didn't post CREATE TABLE
and INSERT statements with sample data and expected output. Check out
www.aspfaq.com/5006 if you prefer a tested reply.
SELECT a.CheckDate, a.CheckNum, a.PmtAmount,
SUM(b.PmtAmount) AS "Running Total"
FROM YourTable AS a
INNER JOIN YourTable AS b
ON b.CheckDate <= a.CheckDate
AND (b.CheckDate < a.CheckDate OR b.CheckNum < a.CheckNum)
GROUP BY a.CheckDate, a.CheckNum, a.PmtAmount
ORDER BY a.CheckDate, a.CheckNum
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Keith G Hicks ,
I assume that the follwing will solve the problem.
Create Table tmpData
(
CheckDate DATETIME,
CheckNum INT, -- It can also be varchar and I assume that the check
number is in increasing order So it may be an identity Column
PmtAmount NUMERIC(30,5)
)
Insert into tmpData
SELECT '20050110',0,15.00
UNION ALL
SELECT '20050110',1,-5.00
UNION ALL
SELECT '20050210',2,-3.00
UNION ALL
SELECT '20050210', 3, -2.00
UNION ALL
SELECT '20050210', 5, -10.00
UNION ALL
SELECT '20050310', 4 ,-1.00
UNION ALL
SELECT '20050410',6, 100.00
SELECT * , (SELECT SUM(PMTAMOUNT) FROM tmpData Where CheckDate <=
A.CheckDate And CheckNum <= A.CheckNum )
>From tmpData A
Drop Table tmpData
With Warm regards
Jatinder Singh