[GENERAL] Running Total with a Set Maximum
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 Tripoint Ltd, unless otherwise explicitly stated.
Re: [GENERAL] Running Total with a Set Maximum
On Wed, Sep 1, 2010 at 5:20 AM, Paul Newman pnew...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
