Hola de nuevo,
a ver si ahora podemos dar un poco de juego al tema....
Esta es la query de la que partimos...
Código SQL
[-]SELECT
CLIENTES.CODIGO,
CLIENTES.NOMBRE,
FACTURA.NUMERO,
FACTURA.SERIE,
FACTURA.FECHA,
FACTURA.CODIGOAGENTE,
FACTURA.TOTAL,
FACTURA.COBRADA
FROM
CLIENTES
RIGHT JOIN FACTURA ON (CLIENTES.CODIGO=FACTURA.CODIGOCLIENTE)
WHERE
(FACTURA.FECHA BETWEEN '04/01/2014' AND '04/30/2014')
ORDER BY
CLIENTES.ID
yo cambiaría el rigth join por left join y quitaría la condición de fechas. Qué hace, me saca todos los clientes, y todas las fras. Aquellos clientes que no tengan fras, tendrán los datos de la tabla de facturas en blanco.
Código SQL
[-]
SELECT
C.CODIGO,
C.NOMBRE,
F.NUMERO,
F.SERIE,
F.FECHA,
F.CODIGOAGENTE,
F.TOTAL,
F.COBRADA
FROM
CLIENTES C
LEFT JOIN FACTURA F ON (C.CODIGO=F.CODIGOCLIENTE)
ORDER BY
C.ID
Bueno, si quiero saber qué me facturan los clientes entre una fecha, pongo el filtro. Al ponerlo, todos aquellos clientes que no tienen facturas en esas fechas, desaparecen porque las fechas ( que serán nulas ) no cumplen la condición.
Para que no se pierdan, sustituyo la condición por
Código SQL
[-]SELECT
C.CODIGO,
C.NOMBRE,
F.NUMERO,
F.SERIE,
F.FECHA,
F.CODIGOAGENTE,
F.TOTAL,
F.COBRADA
FROM
CLIENTES C
LEFT JOIN FACTURA F ON (C.CODIGO=F.CODIGOCLIENTE)
WHERE
(F.FECHA BETWEEN '04/01/2014' AND '04/30/2014' OR F.FECHA is null)
ORDER BY
C.ID
Ahora tendrían que salir todos los que quiero.
Vamos a agruparlos. Quiero agrupar por cliente, es decir, una fila por cliente y el total. Para ello solo necesito el codigo y nombre del cliente y el montante de las fras, que sumo, el resto de campos sobra.
SELECT
C.CODIGO,
C.NOMBRE,
SUM(F.TOTAL)
FROM
CLIENTES C
LEFT JOIN FACTURA F ON (C.CODIGO=F.CODIGOCLIENTE)
WHERE
(F.FECHA BETWEEN '04/01/2014' AND '04/30/2014' OR F.FECHA is null)
group by
C.CODIGO, C.NOMBRE
ORDER BY
C.ID
En principio ya tendría los datos tal y como los quiero. Aquellos clientes que no tienen fras, el total lo tendrán a nulos. Con una máscara en el dataset que muestra los datos supongo que será suficiente, si no, habría que utilizar la función colaesce en la consulta.
Para terminar, paso a detallar cómo se consigue poner los meses en columnas, tal y como me ha pedido el abuelete, dado que yo me he ofrecido a hacerlo, claro.
Para ello, cambio la condición del where para que me saque el año completo y sumo en cada columna si se cumple la condición, es decir, si el mes corresponde a la columna.
SELECT
C.CODIGO,
C.NOMBRE,
SUM(F.TOTAL) as TOTAL,
SUM( case when extract(month from F.FECHA) = 1 then F.TOTAL else 0 end) as ENERO,
SUM( case when extract(month from F.FECHA) = 2 then F.TOTAL else 0 end) as FEBRERO,
...
SUM( case when extract(month from F.FECHA) = 12 then F.TOTAL else 0 end) as DICIEMBRE
FROM
CLIENTES C
LEFT JOIN FACTURA F ON (C.CODIGO=F.CODIGOCLIENTE)
WHERE
(F.FECHA BETWEEN '01/01/2014' AND '12/31/2014' OR F.FECHA is null)
group by C.CODIGO,C.NOMBRE
ORDER BY
C.ID
Ya para acabar y poner la guinda sobre la nata, se puede hacer que la consulta coja la fecha del sistema, calcule el mes y saque las facturas de los últimos 13 meses por cliente, aprovechando para calcular la tendencia anual móvil y ver cómo evoluciona nuestro negocio.
Ojo, estas últimas consultas deben ser estudiadas cuidadosamente, ya que si por falta de tunning de la bbdd o por tener muchos registros las tablas, pueden demorar bastante y puede ser necesario utilizar otra técnica para extraer estos datos.
Espero que el abuelete esté servido para estudiar un par de horitas y convertirse en el rey de las consultas... Independientemente, quedo a
tu absoluta disposición para aclarar cualquier duda que puedas tener.
Un saludo
PD: En una ocasión Casimiro enlazó una infografía en la que se explicaba muy sencillamente y muy claro los inner , left y rigth join. Veré si lo encuentro...