Friday Fun - RunningTotalsAnyone?

If I put this in crystal reports I can manipulate it ok, but actually translating it to a command isn't so easy for me. I'm terrible at cursors and need some help here, pretty pretty please.

I have a table...

CREATE TABLE [dbo].[StagePayment] (
[PaymentID] [uniqueidentifier] NOT NULL ,
[JobID] [uniqueidentifier] NOT NULL ,
[Paid] [bit] NOT NULL ,
[Sequence] [smallint] NOT NULL ,
[Amount] [decimal](10, 2) NOT NULL ,
[Activity] [varchar] (30) NULL ,
[ActivityID] [uniqueidentifier] NULL ,
[DueDate] [datetime] NULL ,
[InvoiceNumber] [varchar] (50) NULL ,
[DueNext] [bit] NOT NULL
) ON [PRIMARY]
GO

Simple example of the data I need to work with...

PayID JobID Paid Sequence Amount DueNext

6094 12 1 1 0.00 0
6B1E 18 1 1 0.00 0
297C 25 1 1 0.00 0
E175 30 1 1 4390.00 0
8CF9 30 1 2 4390.00 0
9168 39 1 1 4400.00 0
59F4 39 1 2 6632.00 0
BCAF 54 1 1 417.50 0
5922 54 0 2 417.50 0
8DA0 68 1 1 2988.00 0
76C5 68 1 2 9958.00 0
0C13 68 1 3 7566.00 0
F3F4 68 1 4 2390.00 0

What I need to accomplish is flagging the DueNext field if the amount due has not been paid. I cannot rely on the Paid field as it's currently user controlled. I need to compare what's due with the total already paid (stored in another table).

So for instance,
Job #68 - the total amount due would be $22902.00

If the customer has paid $15000.00 then Sequence 3, PayID 0C13, should be flagged as DueNext.

Anyone have a very simple example of a running total cursor that I can reference or a better idea of how to work with this? I appreciate any and all suggestions!
Thanks!
abbra_dooYou can do running totals in SQLSVR without cursors, but is it better to handle them in your reporting tool (Crystal Reports, in your case).
Include the total already paid from the other table as a column in your result set. Have Crystal keep the running total and flag records that exceed the total paid.|||You could create function and bind it to the DueNext field. Inside the function you can compare the totals and have the function return either a 1 or a 0 (zero)

Here's an example from BOL

CREATE FUNCTION CubicVolume
-- Input dimensions in centimeters
(@.CubeLength decimal(4,1), @.CubeWidth decimal(4,1),
@.CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @.CubeLength * @.CubeWidth * @.CubeHeight )
END

This function can then be used anywhere an integer expression is allowed, such as in a computed column for a table:

CREATE TABLE Bricks
(
BrickPartNmbr int PRIMARY KEY,
BrickColor nchar(20),
BrickHeight decimal(4,1),
BrickLength decimal(4,1),
BrickWidth decimal(4,1),
BrickVolume AS
(
dbo.CubicVolume(BrickHeight,
BrickLength, BrickWidth)
)
)

Obviously your logic will differ from this function but essentially it should work the same way you'll just have to have some If...then logic to determine what you want to return.

Best regards|||Thanks blindman but our developers are revamping the application for our users and the DueNext field is new and needs to be populated within the database. Otherwise I'd be real happy as the report was alreay in place and working. Eventually this change is supposed to make life on the payment side of the fence much more intuitive and reports should become easier overall - fingers crossed!
abbra_doo|||Thanks mkal -

I'll read up more on this and see if it'll help.

abbra_doo|||Would it be easier to add a temporary field called RunningTotal to the StagePayment table and insert the tabulated running total for that job then I would only have to compare and flag DueNext as necessary? Sort of like this...

PayID JobID Paid Sequence Amount RunningTotal DueNext

6094 12 1 1 0.00 0.00 0
6B1E 18 1 1 0.00 0.00 0
297C 25 1 1 0.00 0.00 0
E175 30 1 1 4390.00 4390.00 0
8CF9 30 1 2 4390.00 8780.00 0
9168 39 1 1 4400.00 4400.00 0
59F4 39 1 2 6632.00 11032.00 0
BCAF 54 1 1 417.50 417.50 0
5922 54 0 2 417.50 835.00 0
8DA0 68 1 1 2988.00 2988.00 0
76C5 68 1 2 9958.00 12946.00 0
0C13 68 1 3 7566.00 20512.00 0
F3F4 68 1 4 2390.00 22902.00 0

I'm still sort of stuck on getting the runnig total to tally right though so if anyone has thoughts I would love to hear them!
abbra_doo|||I wouldn't use a function for this.

In your case, you need to calculate a running total, which requires a scan of the table. A user-defined function would execute once for every record in your resultset (or more), so 1000 records means 1000 function calls and 1000 scans.

Here is one method of doing this without a cursor:
select StagePayment.JobID,
StagePayment.Sequence,
StagePayment.Amount,
sum(RunningSums.Amount) as RunningSum
from StagePayment
inner join StagePaymen RunningSums
on StagePayment.JobID = RunningSums.JobID
and StagePayment.Sequence >= RunningSums.Sequence
group by StagePayment.JobID,
StagePayment.Sequence,
StagePayment.Amount|||blindman, excellent as usual

next he's gonna ask you for the update statement...

;)|||Sorry, I've been out and I'm just getting back to this.

Thanks a bunch blindman! I was using a <= in my query and getting exactly the same as the Amount and once switched to >= I got the running total! So thanks, thanks, thanks! I was the blind-one in that query! Hahaha!

I ended up creating a temp table and I put all the data from the StagePayment table, plus a running total column, plus the total amount paid column for each job. Then I just updated the temp table and selected the minimum sequence for a job where the amount due (running total field) was less than the amount paid. Spot checked some of the jobs and updated the actual StagePayment table and !!Voila!!, no cursor needed - I really dislike them! Everything looks like it's correct so I think I'm a happy camper!

Thanks again!
Hope you all have a terrific 2007 (Lunar Year of the Fire Pig)!
abbra_doo