On 7/7/06, Phillip Smith <[EMAIL PROTECTED]> wrote:
Hi again,
G'day (it's 03:21 on a friday here).
Same SELECT query as before, different area of it… I have a function that
calculates the recommended purchase order quantity for a stock item based
off various other values and functions:
pqty(stock.code) AS "pqty"
This needs to be rounded up / down to the nearest multiple of the purchase
unit quantity for that product – It's Friday afternoon and my head has
refused to help me work out the maths all afternoon!
Example:
Pqty = 60
Purchase Unit = 25
Pqty needs to be rounded down to 50.
create or replace function roundupdown(pqty int, punit int)
returns int as
$$
select ((case when $1<$2 then $2 else $1 end)/$2::float)::int*$2;
$$
language 'sql';
-- test
select roundupdown(s.x,25) as pqty , (s.x/25::float) as near
from generate_series(1,100) s(x);
I guess I'm also asking if I should do this in the Pqty function or in the
SELECT query to optimize the result?
Whatever suits your usage pattern, I guess.
Thanks all – Enjoy your weekends I hope!
Cheers,
-p
Same to you.
Regards,
Rodrigo
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
