Return average cost between partially equal products

Navigation
0

As I exemplify in SQL Fiddle , I need to give the tables the data to be returned as follows: / p> 

nome     | descricao| tipo    | custo|    ult_lancamento      | entradas | saidas
---------------------------------------------------------------------------
ProdutoA | ProdutoA | ACABADO | 0.91 | May,12 2017 15:39:00   |   220    | 90
----------------------------------------------------------------------------
ProdutoB | ProdutoB | PRIMA   | 1.25 | March,24 2017 07:40:00 |   50     | (null)

That is, products that have the same name and description will be returned as a product containing a cost average, the sum of its inputs and outputs, and the last release date. In SQL Fiddle, it contains what I could do.

    
asked by anonymous 16.05.2017 / 01:27

1 answer

1

Follow the code for your need: 

Select
p.nome,
p.tipo,
avg(p.custo) as custo_medio,
sum(coalesce(le.quantidade,0)) as entradas,
sum(coalesce(ls.quantidade,0)) as saidas,
(select MAX(data_lancamento) from lancamentos where id_produto=p.id) as ultimo_lancamento
from produtos p
left outer join lancamentos le on le.id_produto = p.id and le.operacao = 'ENTRADA'
left outer join lancamentos ls on ls.id_produto = p.id and ls.operacao = 'SAIDA'
where exists (select * from lancamentos where id_produto = p.id)
group by p.nome,p.tipo

Some Remarks:

1-I do not understand why two products with the same name, this can bring you problems, do not do so.

2-Use the posting table to store the cost price, this way you will have the entire product cost history on the date it was launched. Same situation for sale price.

3-Do not use 'INPUT' and 'EXIT' to differentiate the records of the movement, when it is an output, use negative values, and input, positive, to extract the balance of the product, simply run 'Sum', and if it is still necessary to differentiate the registers, use numbers, eg 0-Input / 1-Outputs

    
16.05.2017 / 02:36
Login Authentication / JsonObject Certificate error while sending an email