From: Rodrigo De Leon <rdeleonp@gmail.com>
To: Phillip Smith <phillips@weatherbeeta.com.au>
Cc: pgsql-sql@postgresql.org
Subject: Re: Select Maths
Date: Fri, 7 Jul 2006 03:20:57 -0500
Message-ID: <a55915760607070120o26ac61d0l597e22f2618d256c@mail.gmail.com> (raw)
In-Reply-To: <012b01c6a192$1b827730$9b0014ac@ITPhil>
References: <012b01c6a192$1b827730$9b0014ac@ITPhil>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rdeleonp@gmail.com, phillips@weatherbeeta.com.au
Subject: Re: Select Maths
In-Reply-To: <a55915760607070120o26ac61d0l597e22f2618d256c@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox