Advertisement
skuda

another problematic query on 9.3

Jun 5th, 2014
149
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT tic_cab.fecha,
  2.              
  3.        COUNT(DISTINCT(tic_cab.ticket_id))::int AS operaciones,
  4.        SUM(tic_lin.unidades)::int AS unidades,
  5.        SUM( (tic_lin.total_linea / nullif((1.0 + (tic_lin.iva::numeric / 100.0)), 0.0))::numeric(20,2) ) AS total,
  6.  
  7.        SUM( (COALESCE(tic_lin.rentabilidad_oferta, tic_lin.total_linea)
  8.              / nullif((1.0 + (tic_lin.iva::numeric / 100.0)), 0.0))::numeric(20,2) ) AS rent_total
  9.  
  10. FROM t001.ticket_cabecera AS tic_cab
  11.    JOIN t001.ticket_linea AS tic_lin ON tic_lin.id_ticket = tic_cab.ticket_id
  12.    JOIN articulo AS art ON art.articulo_id = tic_lin.id_articulo
  13.    JOIN modelo AS mo ON mo.modelo_id = art.id_modelo
  14.  
  15.    LEFT JOIN (SELECT sdev.id_ticket,
  16.                      lin.id_linea
  17.               FROM devolucion_cabecera sdev
  18.                 JOIN devolucion_linea lin ON lin.id_devolucion = sdev.devolucion_id
  19.               WHERE sdev.id_tienda = 001
  20.                 AND lin.documento_origen_tipo = 'RESERVA') devres ON devres.id_ticket = tic_lin.id_ticket
  21.                                                                  AND devres.id_linea = tic_lin.linea_id
  22.  
  23.  
  24. WHERE fecha BETWEEN '2014-05-19' AND '2014-05-19'
  25.    AND devres IS NULL
  26.    
  27. GROUP BY tic_cab.fecha
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement