33 lines
708 B
SQL
33 lines
708 B
SQL
-- Costo, existencia y valor consolidados por item, al ultimo dia del año anterior
|
|
|
|
SELECT
|
|
l.id_linea AS Linea,
|
|
i.id_linea AS Sublinea,
|
|
i.id_item AS Item,
|
|
i.descripcion AS Descripcion,
|
|
ROUND(SUM(e.cantidad * e.costo_unidad), 2) AS Costo_promedio,
|
|
ROUND(SUM(e.cantidad * e.valor_unidad), 2) AS Valor,
|
|
SUM(e.cantidad) AS Stock_UM,
|
|
ROUND(SUM(e.cantidad * i.peso), 2) AS Stock_Kg
|
|
|
|
FROM
|
|
existencia_historico e
|
|
JOIN item i
|
|
ON ( i.id_item = e.id_item
|
|
AND i.id_sucursal = e.id_sucursal )
|
|
LEFT JOIN view_lineas l
|
|
ON ( l.id_linea = LEFT( i.id_linea, 2 ) )
|
|
|
|
WHERE
|
|
(e.fecha = '2014-12-31')
|
|
|
|
GROUP BY
|
|
i.id_item
|
|
|
|
HAVING
|
|
(Stock_UM > 0)
|
|
|
|
ORDER BY
|
|
Item DESC
|
|
|