From: | "Rodrigo De Leon" <rdeleonp(at)gmail(dot)com> |
---|---|
To: | "Phillip Smith" <phillips(at)weatherbeeta(dot)com(dot)au> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Select Maths |
Date: | 2006-07-07 08:20:57 |
Message-ID: | a55915760607070120o26ac61d0l597e22f2618d256c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 7/7/06, Phillip Smith <phillips(at)weatherbeeta(dot)com(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Aaron Bono | 2006-07-07 08:32:33 | Re: Alternative to serial primary key |
Previous Message | David Clarke | 2006-07-07 08:07:11 | Re: Alternative to serial primary key |