Arithmetic calculation between tables?

[pablovicente]

I'm seting up a database where salesrep. will put the sales of a day and what i want to do is to calculate if they reach their objective, if they not or if they get past it.

Get the idea?

[gio2k]

Please explain yourself a little further.
What information do you want to store in the Database?
If you're worried about MySQL arithmetic calculation capabilities, that's no problem.
As long as you don't need complex mathematical functions like Log, Exp, etc. then SQL can handle the basic ones without trouble.

-Gio-

[pablovicente]

For example the salesrep got to sale 500 products a month, then i would have a table with their objectives (500 products) and a table with its sales what i want to do is to know how many products are left for a given salesman doing "objectivetable - reachedobjectivestable"

[gio2k]

create table expected_sales
(
sales_rep id integer not null,
month integer not null,
expected_sales_count integer not null,
primary key(sales_rep_id, month)
);

create table sales
(
sales_id integer unsigned not null auto_increment,
sales_rep_id integer not null,
sales_date datetime,
sales_total decimal(10),
primary key (sales_id)
);

create table sales_details
(
sales_id integer unsigned not null,
product_id integer not null,
count integer,
unit_price decimal
);

SELECT s.sales_rep_id, sum(sd.count) as total_items
FROM sales s LEFT JOIN sales_details sd
ON s.sales_id = sd.sales_id
WHERE MONTH(sales_date) = $month
GROUP BY s.sales_rep_id

That query should give you how many items were sold by each sales rep.

You can add the third table (expected sales)
to the join and then calculate the difference afterwards.

HTH

-Gio-

[pablovicente]

Thanks a lot for the example you kickstarted my brian from where it was stuck!!

[saravanan1979]

Does Left ouite join Work in MYSQL r what