Advertisement
skuda

query slower on 9.3 simplified a little bit.

Jun 5th, 2014
390
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT mo.referencia,
  2.        ent.unidades AS entrada,
  3.        ven.unidades AS vendidas,
  4.        taprov.precio
  5.  
  6. FROM modelo AS mo
  7.  
  8.    JOIN (SELECT mo.modelo_id AS id_modelo,
  9.                 SUM(lin.unidades_reales)::int AS unidades,
  10.                 MIN(cab.time_stamp_recepcion)::date AS min_fecha
  11.  
  12.          FROM modelo AS mo
  13.             JOIN articulo AS art ON art.id_modelo = mo.modelo_id
  14.             JOIN albaran_entrada_linea As lin ON lin.id_articulo = art.articulo_id
  15.             JOIN albaran_entrada_cabecera AS cab ON cab.albaran_id = lin.id_albaran
  16.  
  17.          WHERE cab.id_almacen_destino = 109
  18.  
  19.          GROUP BY mo.modelo_id
  20.  
  21.          HAVING MIN(cab.time_stamp_recepcion)::date = (current_date - interval '30 days')::date ) AS ent ON ent.id_modelo = mo.modelo_id
  22.                  
  23.    JOIN (SELECT mo.modelo_id AS id_modelo,
  24.                 (CASE
  25.                    WHEN SUM(tic.unidades) > 0
  26.                       THEN  SUM(tic.unidades)
  27.                    ELSE 0
  28.                 END)::int AS unidades
  29.          FROM modelo AS mo
  30.             JOIN articulo AS art ON art.id_modelo = mo.modelo_id
  31.  
  32.             JOIN (SELECT lin.id_articulo,
  33.                          SUM(lin.unidades) AS unidades
  34.                          
  35.                   FROM ticket_cabecera AS cab
  36.                      JOIN ticket_linea AS lin ON lin.id_ticket = cab.ticket_id
  37.                                               AND lin.tienda = cab.tienda
  38.                                              
  39.                   WHERE cab.tienda = 109
  40.                   GROUP BY lin.id_articulo) AS tic ON tic.id_articulo = art.articulo_id
  41.  
  42.        GROUP BY mo.modelo_id) AS ven ON ven.id_modelo = mo.modelo_id
  43.          
  44.    JOIN tarifa_proveedor_modelo_precio AS taprov ON taprov.id_modelo = mo.modelo_id
  45.                                                 AND taprov.id_tarifa = 3
  46.  
  47. WHERE mo.id_seccion <> 4
  48.   AND (ent.unidades <> 0 OR ven.unidades <> 0)
  49.  
  50. ORDER BY referencia;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement