Ayuda para montar una consulta

[ecfisa]

Hola José.

Empezado por José Luis Garcí

...
Lo que quiero hacer es recorrer por orden de código todos los clientes, y que me diga el total de lo comprado entre unas determinadas fechas, poniéndome 0 en los que no hayan comprado nada, así de esta manera, se puede ver que clientes ya no compran o no se les ha visitado nuevamente.
...

Si solamente buscas obtener el código el nombre y el total comprado podes hacer:

Código SQL [-]
SELECT DISTINCT CL.CODIGO,
       CL.NOMBRE,
       COALESCE((SELECT SUM(TOTAL) FROM FACTURA WHERE CLIENTE_ID = CL.ID), 0) AS TOTALCOMPRADO
FROM CLIENTES CL, FACTURA FA
WHERE FA.FECHA BETWEEN :DESDE AND :HASTA
ORDER BY CL.CODIGO

Pero en esa consulta no es lógico pedir datos específicos de una factura tál como la fecha (FA.FECHA), ya que podrá haber distintas fechas en distintas facturas para el mismo cliente. Y además, no se correspondería con el sentido de la consulta que es obtener el total comprado por cada uno de los clientes entre dos fechas.

Saludos

[mamcx]

Para estos casos ayudaria mucho tener ejemplo de datos!

Y por ejemplo, usando:

http://sqlfiddle.com/

[[fjcg02]]

Hola, no he podido responder antes,

No te salen los datos porque los clientes que no tienen facturas ( no reconcilian los registros ) no cumplen la condición que pones , es decir , al no tener facturas, su fecha factura es nula y no cumple la condición.

Si pusieras (factura.fecha between F1 and f2 or factura.fecha is null ) seguro que el resultado es diferente ( y más cercano a lo que quieres).

Para probar quita la condición de la fecha de la factura y ejecuta la consulta. Ya verás como aparecen registros con los datos de fras vacíos, son los que no tienen facturas. A partir de ahí ya te lo eh dicho casi todo...

A ver sí me puedo conectar a la noche desd el pc y puedo aportarte más información.

Saludos

[[José Luis Garcí]]

Empezado por fjcg02

Hola, no he podido responder antes,

No te salen los datos porque los clientes que no tienen facturas ( no reconcilian los registros ) no cumplen la condición que pones , es decir , al no tener facturas, su fecha factura es nula y no cumple la condición.

Si pusieras (factura.fecha between F1 and f2 or factura.fecha is null ) seguro que el resultado es diferente ( y más cercano a lo que quieres).

Para probar quita la condición de la fecha de la factura y ejecuta la consulta. Ya verás como aparecen registros con los datos de fras vacíos, son los que no tienen facturas. A partir de ahí ya te lo eh dicho casi todo...

A ver sí me puedo conectar a la noche desd el pc y puedo aportarte más información.

Saludos

Gracias Javier, seguiré intentándolo.

[[José Luis Garcí]]

Gracias como siempre ecfisa, tienes razón a que sobra datos, pero como no tengo el CLIENTE_ID, lo he intentado modificar por

Código Delphi [-]
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 ESDE AND :HASTA
ORDER BY CL.CODIGO

Pero me da el error

"Dinamyc SQL error SQL error code = -206 Ciumn unknown Fa.CODIGO At line 3 Column 58."

[ecfisa]

Empezado por José Luis Garcí

...
Pero me da el error

"Dinamyc SQL error SQL error code = -206 Ciumn unknown Fa.CODIGO At line 3 Column 58."

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  
       /* Error, no corresponde el alias FA, (fijate que no lo usé en mi mensaje anterior)  */
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

[[José Luis Garcí]]

Empezado por mamcx

Para estos casos ayudaria mucho tener ejemplo de datos!

Y por ejemplo, usando:

http://sqlfiddle.com/

Te entiendo perfectamente, pero como son datos reales, no puedo facilitarlos, por cierto para que es el link que has puesto exactamente?.

[mamcx]

Empezado por José Luis Garcí

Te entiendo perfectamente, pero como son datos reales, no puedo facilitarlos, por cierto para que es el link que has puesto exactamente?.

Para montar un ejemplo con datos (de prueba) y esquema en un motor de BD en linea, ideal para compartir codigo/datos sql y hacer consultas en linea

[[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)
/* comentario, no aplica
WHERE
  (F.FECHA BETWEEN '04/01/2014' AND '04/30/2014')
 fin del comentario */
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...

[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.

[[José Luis Garcí]]

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  
       /* Error, no corresponde el alias FA, (fijate que no lo usé en mi mensaje anterior)  */
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

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)
/* comentario, no aplica
WHERE
  (F.FECHA BETWEEN '04/01/2014' AND '04/30/2014')
 fin del comentario */
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é

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