Skip to content

Instantly share code, notes, and snippets.

@MarioCares

MarioCares/consumo_anual_papeleria.sql

Created January 10, 2017 14:35
Show Gist options
  • Save MarioCares/5e7021b7e4424b28710a26aea7abf474 to your computer and use it in GitHub Desktop.
Save MarioCares/5e7021b7e4424b28710a26aea7abf474 to your computer and use it in GitHub Desktop.
Download ZIP
Listado por mes de papelería.
Raw
consumo_anual_papeleria.sql
SELECT MONTH(fo.orden_at) AS mes, fdo_fp.nombre, SUM(fdo_fp.cantidad) AS cantidad
FROM finanzas_ordencompra AS fo
INNER JOIN
(SELECT fdo.ordencompra_id, fdo.cantidad, fp.nombre
FROM finanzas_detalle_ordencompra AS fdo
INNER JOIN
(SELECT id, nombre
FROM finanzas_producto
WHERE nombre REGEXP 'oficio|carta' AND (nombre NOT REGEXP 'formulario|opalina|libro|arch|adhesivo|talonario|prehospitalaria|laminas|triptico|volantes|lomo|separador') AND (nombre NOT REGEXP 'saco|funda|volante|block|sobre|tallimetro|balanza|flayer|balamza|carne|revista|carpeta|diptico|cuad|tabla') AND departamento = 'SALUD') AS fp ON fp.id = fdo.producto_id) AS fdo_fp ON fdo_fp.ordencompra_id = fo.id
WHERE YEAR(fo.orden_at) = 2016 AND fo.estado_id NOT IN (1,4) AND bodega_central != 1
GROUP BY fdo_fp.nombre, MONTH(fo.orden_at)
ORDER BY MONTH(fo.orden_at) ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment