Re: Select Maths

On 7/7/06, Phillip Smith <phillips@weatherbeeta.com.au> 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