Hi Alexandre, thanx for your reply.

I understood your select and i ll try to adapt for what i have:

i have 3 commission types:

1 - the vendor commission percent

2 - the product commission value (that is a fixed value for each product
that is sold)

3 - the product commission percent value

my order is something like the following:

if the product commission value isnt null, it takes this value instead of
the product commission percent, and if the commission percent is null too,
then it will use the vendor percent..

But thanx, i think i already know how to do it :)

On 2/6/06, Alexandre Benson Smith <iblist@...> wrote:
>
> Hi Fabio,
>
> Fabio Gomes wrote:
>
> >Hi all, i m new to firebird, so i don't know exactly how i can make a
> >procedure or something like it to solve my problem.
> >
> >I ll try to be as clear as my english allow me to, but sorry, my english
> is
> >kinda bad.
> >
> >I have a vending control, and the vendors have a fixed commission value
> is
> >used to calculate the commission value, my table structure is like this:
> >
> >vending:
> >vendor_id
> >total
> >
> >vendors:
> >vendor_id
> >vendor_comission
> >vendor_name
> >
> >items:
> >product_id
> >vending_id
> >
> >products:
> >product_id
> >product_comission_value
> >product_comission_percent
> >
> >Actually my report calculate the commission value based on the vending
> >total, but i need to make it calculate like this:
> >
> >if i have a product_comission_value, it will forget the vendor_comission
> and
> >add this value to the total commission..
> >
> >So basically it have to select the vendings from that vendor, and for
> each
> >product test if the product have a commission value, if it has so it
> >calculates it, if it doesn't the vendors commission is used.
> >
> >
> I'd go with a simple select liek this:
>
> The comission is a fixed value ? a percent over the total sale ? I am
> missing the Value of each item, you have the Value of the whole sale.
>
> select
> V.Vendor_Name, Sum(Coalesce(P.Product_Comission_Value,
> V.Vendor_Comission) * I.Value/100)
> from
> Items I join
> Products P on (P.ProdutoID = I.ProductID) join
> Vending V2 on (V2.Vending_ID = I.Vending_ID) join
> Vendors V on (V.Vendor_ID = V2.Vednor_ID)
> group by
> V.Vendor_Name
>
> It could have some miswritten table name or column name, but the point
> that solves your problem is that one:
> Sum(Coalesce(P.Product_Comission_Value, V.Vendor_Comission) * I.Value/100)
>
> Coalesce returns the first argument if it is not null or the second
> otherwise.
>
> As you said:
> "test if the product have a commission value, if it has so it calculates
> it, if it doesn't the vendors commission is used."
>
> I assume that if the product has any value it should be used on the
> comission calculation, otherwise (if it is null) use the vendor.comission
>
> I don't know if
> P.Product_Comission_Value and V.Vendor_Comission are real (absolute and
> static) values or a percenth of the total, I assume it is a percent, but
> you get the idea.
>
> >is it clear?
> >
> >How do you guys think its the best way to do it? with a procedure? with
> >normal selects and coding by hand? i m kinda lost with this stuff.
> >
> >Thanks in advance,
> >
> >Fábio.
> >
> >
>
> see you !
>
> --
> Alexandre Benson Smith
> Development
> THOR Software e Comercial Ltda
> Santo Andre - Sao Paulo - Brazil
> www.thorsoftware.com.br
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>
>
> SPONSORED LINKS
> Technical support<http://groups.yahoo.com/gads?t=ms&k=Technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=-XIO8GxY6hqd3NaD5WSEyw> Computer
> technical support<http://groups.yahoo.com/gads?t=ms&k=Computer+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=B29J78SYXnNTjjMFBMznqA> Compaq
> computer technical support<http://groups.yahoo.com/gads?t=ms&k=Compaq+computer+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=7_je1A94xs82CFXUjEqA6g> Compaq
> technical support<http://groups.yahoo.com/gads?t=ms&k=Compaq+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=2zMAuRCo5cJrVBr1Bxa3_w> Hewlett
> packard technical support<http://groups.yahoo.com/gads?t=ms&k=Hewlett+packard+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=_ytYU7aXb57AVaeUfmvLcA> Microsoft
> technical support<http://groups.yahoo.com/gads?t=ms&k=Microsoft+technical+support&w1=Technical+support&w2=Computer+technical+support&w3=Compaq+computer+technical+support&w4=Compaq+technical+support&w5=Hewlett+packard+technical+support&w6=Microsoft+technical+support&c=6&s=196&.sig=4hRo6NXYavRAbTkaYec5Lw>
> ------------------------------
> YAHOO! GROUPS LINKS
>
>
> - Visit your group "firebird-support<http://groups.yahoo.com/group/firebird-support>"
> on the web.
>
> - To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com<firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>
>
> - Your use of Yahoo! Groups is subject to the Yahoo! Terms of
> Service <http://docs.yahoo.com/info/terms/>.
>
>
> ------------------------------
>


[Non-text portions of this message have been removed]