On Wed, Sep 1, 2010 at 5:20 AM, Paul Newman <pnewman@prohire.co.uk> wrote:
> Hi,
> Could someone out there help me.
> Given the following set of values
> +3
> +3
> 0
> +3
> +3
> -3
> 0
> +3
>
> I want to have a maximum sum of 6 at any one point , in other words I want
> the following
>
>
> +3 -- 3
> +3 -- 6
> 0 -- 6
> +3 -- 6
> +3 -- 6
> -3 -- 3
> 0 -- 3
> +3 -- 6
>
> How can I do this ?
>
Looks like what you really want is a running total where the max is no
more than 6 at any time. Except it's not really a running total since
the current total is dependent on the last max?
You might be able to pull this off with some combination of window and
max, but really this is a rather odd mathematical operation so it's
not easy to code up directly in a single query. If you can add
another column that is the running total to date and if you can use a
sequence for another column (primary key perhaps?) then the problem
becomes pretty trivial: at insert time you can simply pick the running
total from the most recent row (found via the max sequence) and create
the new (pseudo) running total with a case statement. If you can't do
that, then I think it would be easiest to code this up in a procedure,
but before anyone jumps on that you might want to let us know if you
are free to add columns to the schema?
--
Peter Hunsberger