Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT mo.referencia AS "REFERENCIA",
- ent.unidades AS "ENTRADA",
- ven.unidades AS "VENDIDAS",
- stock.unidades AS "STOCK DISP",
- stock.num_tiendas AS "TIENDAS STOCK",
- CASE
- WHEN ent.unidades = 0
- THEN NULL
- ELSE
- CASE
- WHEN ((ven.unidades::numeric / ent.unidades::numeric) * 100.0)::int < 100
- THEN ((ven.unidades::numeric / ent.unidades::numeric) * 100.0)::int
- ELSE 100
- END
- END AS "%% VEN",
- (taprov.precio * (1.0 + (mo.tipo_iva / 100.0)))::numeric(7,2) AS "COSTO IVA INC.",
- ta3.precio AS "PVP RECOME.",
- ta1.precio AS "PVP ACTUAL"
- FROM modelo AS mo
- JOIN (SELECT mo.modelo_id AS id_modelo,
- SUM(lin.unidades_reales)::int AS unidades,
- MIN(cab.time_stamp_recepcion)::date AS min_fecha
- FROM modelo AS mo
- JOIN articulo AS art ON art.id_modelo = mo.modelo_id
- JOIN albaran_entrada_linea As lin ON lin.id_articulo = art.articulo_id
- JOIN albaran_entrada_cabecera AS cab ON cab.albaran_id = lin.id_albaran
- WHERE cab.id_almacen_destino = 109
- GROUP BY mo.modelo_id
- HAVING MIN(cab.time_stamp_recepcion)::date = (current_date - interval '30')::date) AS ent ON ent.id_modelo = mo.modelo_id
- JOIN (SELECT mo.modelo_id AS id_modelo,
- (CASE
- WHEN SUM(tic.unidades) > 0
- THEN SUM(tic.unidades)
- ELSE 0
- END
- +
- CASE
- WHEN SUM(res.unidades) > 0
- THEN SUM(res.unidades)
- ELSE 0
- END)::int AS unidades
- FROM modelo AS mo
- JOIN articulo AS art ON art.id_modelo = mo.modelo_id
- LEFT JOIN (SELECT lin.id_articulo,
- SUM(lin.unidades) AS unidades
- FROM ticket_cabecera AS cab
- JOIN ticket_linea AS lin ON lin.id_ticket = cab.ticket_id
- AND lin.tienda = cab.tienda
- WHERE cab.tienda = 109
- GROUP BY lin.id_articulo) AS tic ON tic.id_articulo = art.articulo_id
- LEFT JOIN (SELECT lin.id_articulo,
- SUM(lin.unidades) AS unidades
- FROM reserva_cabecera AS cab
- JOIN reserva_linea AS lin ON lin.id_reserva = cab.reserva_id
- AND lin.tienda = cab.tienda
- WHERE cab.tienda = 109
- AND cab.recogido = TRUE
- GROUP BY lin.id_articulo) AS res ON res.id_articulo = art.articulo_id
- GROUP BY mo.modelo_id) AS ven ON ven.id_modelo = mo.modelo_id
- JOIN tarifa_modelo_precio AS ta3 ON ta3.id_modelo = mo.modelo_id
- AND ta3.id_tarifa = 3
- JOIN tarifa_proveedor_modelo_precio AS taprov ON taprov.id_modelo = mo.modelo_id
- AND taprov.id_tarifa = 3
- LEFT JOIN tarifa_modelo_precio AS ta1 ON ta1.id_modelo = mo.modelo_id
- AND ta1.id_tarifa = 1
- LEFT JOIN (SELECT mo.modelo_id AS id_modelo,
- (SUM(st.stock) - SUM(st.reservado))::int AS unidades,
- COUNT(DISTINCT(st.tienda)) AS num_tiendas
- FROM ubicacion_stock AS st
- JOIN articulo AS art ON art.articulo_id = st.id_articulo
- JOIN modelo AS mo ON mo.modelo_id = art.id_modelo
- WHERE st.tienda = 109
- AND (st.stock - st.reservado) > 0
- GROUP BY mo.modelo_id) AS stock ON stock.id_modelo = mo.modelo_id
- WHERE mo.id_seccion <> 4
- AND (ent.unidades <> 0 OR ven.unidades <> 0)
- ORDER BY "REFERENCIA";
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement