Обсуждение: Running Total with a Set Maximum

Поиск
Список
Период
Сортировка

Running Total with a Set Maximum

От
"Paul Newman"
Дата:
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 ?
 
Thank you
 
Paul Newman
 
 

Disclaimer
This message may contain information which is legally privileged and/or confidential.  If you are not the intended recipient, you are hereby notified that any unauthorised disclosure, copying, distribution or use of this information is strictly prohibited.  Such notification not withstanding, any comments or opinions expressed are those of the originator, not of Prohire Software Systems Ltd, unless otherwise explicitly stated.

Re: Running Total with a Set Maximum

От
Peter Hunsberger
Дата:
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