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