Relate MySQL tables and filter by date

Navigation
6

I need only one final value, the commission amount for products with extra commission in a date range and a specific vendor, the information is in 3 tables in the DB:

Below is the table 'products_commission_extra', the column 'Data_abertura' shows when the product began to have extra commission, and 'Closing_Date' when left unused, the value column is how much commission is earned per unit sold that if you put extra commission on a product it creates a new registry instead of changing the old one, to be able to have a history)

ThisistheSalesItems,itshowsthesalesproducts

Andthisisthesales(employeeIDanddateofsalestayhere)

SofarthecodeIwrotewiththehelpwasasfollows:

SELECTitens_venda.Id_produto,Quantidade,Valor,Id_funcionario,data_vendaFROMgenius.Itens_vendaJOINgenius.vendasONitens_venda.Id_venda=vendas.IdJOINprodutos_comissao_extraONprodutos_comissao_extra.Id_produto=itens_venda.Id_produtoWHEREdata_vendaBETWEENData_aberturaANDifnull(Data_fechamento,curdate())ANDId_funcionario=5ORDERBYData_fechamentoDESC;

Theresultisthis:

It's almost what I need, just need to filter by a specific date range, for example, I want only the month 7, but have to follow everything that has already been written in the code, I just have to filter the result that I already I have

    
asked by anonymous 09.09.2016 / 13:09

1 answer

2

You can start by using the INNER JOIN to join your tables, use the WHERE to refer to a specific field (specific vendor) clause BETWEEN for a date range (extra commission on a date range) and a ORDER BY to optimize your query if you have more than one result.

Here's a kick to get you started. 

   select * from Vendas V
    join Itens_venda IV
    on IV.Id_venda = V.Id 
    join produtos_comissao_extra PC
    on PC.Id_produto = IV.Id_produto
    where V.Id_funcionario = 1
    order by Data_fechamento desc

If you need anything else, it would be good to edit your question in more detail.

    
09.09.2016 / 14:44
Rotate objects around the specific CSS axis Export R data to Excel formatted