Hi,

I've got a table that holds payment information as follows:

Code    Payment    Percent
~~~~~~~~~~~~~~~~~~
AAAA    1            20%
AAAA    2            40%
AAAA    3            10%
AAAA    4            30%
XXXX    1            25%
XXXX    2            25%
XXXX    3            25%
XXXX    4            25%


What I would like to obtain is a running total result on the percentage that
resets when the code resets, so the result would look like:

Code    Payment    Percent
~~~~~~~~~~~~~~~~~~
AAAA    1            20%
AAAA    2            60%
AAAA    3            70%
AAAA    4           100%
XXXX    1            25%
XXXX    2            50%
XXXX    3            75%
XXXX    4           100%


I was looking at the RUNSUM function, but need to get it to 'reset' itself
when the code changes, and am not sure how to go about this.

I'm using DBISam 4. Just wondering if this is possible, or am I attempting
to do this the wrong way?

Thanks & Regards

Adam.

Adam,

<< I was looking at the RUNSUM function, but need to get it to 'reset'
itself when the code changes, and am not sure how to go about this.

I'm using DBISam 4. Just wondering if this is possible, or am I attempting
to do this the wrong way? >>

The problem is that RUNSUM() won't reset (ever).   What you'll want to do is
to loop through a distinct list of the Codes and execute a RUNSUM() query
for each code, dumping the results into another temporary table.

--
Tim Young
Elevate Software
www.elevatesoft.com

Good Morning Tim,

Thanks for your reply...

> The problem is that RUNSUM() won't reset (ever).   What you'll want to do
> is to loop through a distinct list of the Codes and execute a RUNSUM()
> query for each code, dumping the results into another temporary table.

When you say is loop through a list of codes, do you mean outside of the
SQL? (I'm assuming there's no way to do this through SQL alone?)

Cheers mate

Adam.

Hi Tim,

I think I may have just come up with a solution, but wanted to run it here
first, incase there are any problems.

The trick was not to use runsum afterall...

select Code, P.Payment, Sum(P2.Percentage) Percent
from MyTable P
inner join Mytable P2 on (P2.Code = P.Code) and (P2.Payment <= P.Payment)
Group by Code, Payment

Cheers

Adam.

Adam,

Sorry for the delay in responding.

<< When you say is loop through a list of codes, do you mean outside of the
SQL? (I'm assuming there's no way to do this through SQL alone?) >>

Yes, and no, there's no way to loop in SQL in DBISAM (EDB can .

--
Tim Young
Elevate Software
www.elevatesoft.com

Adam,

<< I think I may have just come up with a solution, but wanted to run it
here first, incase there are any problems.

The trick was not to use runsum afterall...

select Code, P.Payment, Sum(P2.Percentage) Percent
from MyTable P
inner join Mytable P2 on (P2.Code = P.Code) and (P2.Payment <= P.Payment)
Group by Code, Payment >>

Very ingenius.  I never think of using the < > operators with joins to
limit the scope of aggregation.

--
Tim Young
Elevate Software
www.elevatesoft.com