Cita:
Empezado por ecfisa
Hola José.
Aunque se trate de la misma tabla son distintas referencias:
Código SQL [-]
SELECT DISTINCT CL.CODIGO,
CL.NOMBRE,
COALESCE((SELECT SUM(TOTAL) FROM FACTURA WHERE FA.CODIGO = CL.CODIGO), 0) AS TOTALCOMPRADO
FROM CLIENTES CL, FACTURA FA
WHERE FA.FECHA BETWEEN :DESDE AND :HASTA
ORDER BY CL.CODIGO
La consulta debería ser:
Código SQL [-]
SELECT DISTINCT CL.CODIGO,
CL.NOMBRE,
COALESCE((SELECT SUM(TOTAL) FROM FACTURA WHERE CODIGO = CL.CODIGO), 0) AS TOTALCOMPRADO
FROM CLIENTES CL, FACTURA FA
WHERE FA.FECHA BETWEEN :DESDE AND :HASTA
ORDER BY CL.CODIGO
O, si lo preferís:
Código SQL [-]
SELECT DISTINCT CL.CODIGO,
CL.NOMBRE,
COALESCE((SELECT SUM(TOTAL) FROM FACTURA FA2 WHERE FA2.CODIGO = CL.CODIGO), 0) AS TOTALCOMPRADO
FROM CLIENTES CL, FACTURA FA
WHERE FA.FECHA BETWEEN :DESDE AND :HASTA
ORDER BY CL.CODIGO
Saludos  |
Ecfisa, como siempre gracias por tú interés, te comento, tienes razón en que la primera me da error, la segunda, simplemente me devuelve nada, aunque no da ningún error y la tercera me da el error por que no reconece FA2.CODIGO
Cita:
Empezado por fjcg02
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... |
Gracias a ti también Javier, te comento no era exactamente lo que buscaba, ya que no recorría todos los cliente, aunque te acercaste mucho con la siguiente, consulta, a la que tuve que quitarle el order by y añadir el campo que estaba en esta al principio del Group by
Código SQL [-]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.ID,C.CODIGO, C.NOMBRE
Lo que si me ha gustado mucho y pienso implementarlo es to solución para ver el año, tuve que corregirla lo mismo, pero quedo francamente bien
Código SQL [-]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) = 3 then F.TOTAL else 0 end) as MARZO,
SUM( case when extract(month from F.FECHA) = 4 then F.TOTAL else 0 end) as ABRIL,
SUM( case when extract(month from F.FECHA) = 5 then F.TOTAL else 0 end) as MAYO,
SUM( case when extract(month from F.FECHA) = 6 then F.TOTAL else 0 end) as JUNIO,
SUM( case when extract(month from F.FECHA) = 7 then F.TOTAL else 0 end) as JULIO,
SUM( case when extract(month from F.FECHA) = 8 then F.TOTAL else 0 end) as AGOSTO,
SUM( case when extract(month from F.FECHA) = 9 then F.TOTAL else 0 end) as SEPTIEMBRE,
SUM( case when extract(month from F.FECHA) = 10 then F.TOTAL else 0 end) as OCTUBRE,
SUM( case when extract(month from F.FECHA) = 11 then F.TOTAL else 0 end) as NOVIEMBRE,
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.ID ,C.CODIGO,C.NOMBRE
Me gustaría, poder especificar el años, así que investigare, pero para ello deberé crear datos falsos, pero lo investigaré
Cita:
Empezado por mamcx
Vuelvo e insisto: Ejemplos de datos. Y no solo de lo que tienes, sino de como se debe *ver*. Esto es importante porque entre otras cosas, como validar que lo que te indiquemos es correcto o no? Las palabras pueden ser ambiguas pero los datos concretos no.
|
Mamcx, tienes toda la razón, como ya se hablo a lo largo del tema, yo estaba equivocado, al principio, ya que pasaba varios parámetros de la facturas y con estos pretendía montar un grid que me diera tres datos importantes, que eran una vez montados en el grid
Poner en verde, los que hubiesen comprado y pagado, en crema a los que hubiesen comprado, pero aún no hubiesen pagado y por último en rojo los que simplemente no habían comprado en esas fechas
Como digo tenían razón ya que un cliente podía tener facturas, unas pagas y potras no, pero yo quería la que me diera el total del comprado entre esas fechas y no me hacia falta tantos datos, así que me bastaba con el código del cliente, el nombre del mismo y el total de compras en ese mes (el importe)
Los datos del cliente, vamos a inventarlos serian:
(ID, código, Nombre)
1, 1, Bar Pepito
5, 2, Taller Mor
6, 3, Viajes Toma
9, 5, Junito
16, 8, Residencia Salud
Los datos de las Facturas (ejemplos también) serian:
(Código cliente, Total de la factura)
1, 20
8, 16.52
3, 42.21
1, 45.30
8, 125.16
El resultado debería ser ordenado por el ID
(Código, Nombre, total)
1, Bar Pepito, 65.30
2, Taller Mor, 0
3, Viajes Toma, 42.21
5, Junito, 0
8, Residencia Salud, 141.68
Espero que te refirieras a esto y lamento pero no se como colocar tablas aquí
De todas maneras, Gracias a Marc y a Sergio de El club hermano DelphiAccess, lo he solucionado con la siguiente consulta
Código SQL [-]SELECT CODIGO, NOMBRE,
COALESCE( (SELECT SUM(TOTAL) FROM FACTURA WHERE CODIGOCLIENTE = CODIGO AND
FECHA BETWEEN '04/01/2014' AND '04/30/2014'), 0) AS TOTAL
FROM CLIENTES ORDER BY CLIENTES.ID
Gracias a todos, he aprendido muchas cosas, espero no olvidarlas, o no perderlas, ya que siempre suelo guardarlas
__________________
Un saludo desde Canarias, "El abuelo Cebolleta"
|