Ayuda para montar una consulta

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

[[José Luis Garcí]]

Pro cierto Javier según he terminado de montar la respuesta anterior, he buscado Firebird y Year i me ha llevado a http://www.firebirdsql.org/refdocs/l...c-extract.html que supongo es lo que necesito, imagino que donde ponemos las lineas

Código SQL [-]
SUM( case when extract(month from F.FECHA) = 1 then F.TOTAL else 0 end) as ENERO,

Por

Código SQL [-]
SUM( case when (extract(month from F.FECHA) = 1) AND (Extract(year from F.FECHA) = '+QuotedStr('XXXX')+') then F.TOTAL else 0 end) as ENERO,

Donde XXXX sea el año que estamos buscando, supongo que será algo así, en cuanto pueda lo probare

Supongo que si no especifico el año, me estaría leyendo, todas las facturas de diversos años, colocando las cada una en el mes que corresponda, que esta bien si quieres saber el total de ventas, por meses de cllintes y el total comprado, así que es muy probable que implemente las dos, una con el total comprado, con todos los datos que tengamos, sabiendo en que meses compra más determinados clientes y otra para saber por año, gracias nuevamente.

Por cierto lo probé y funciona

[[fjcg02]]

Hola,
completando un poco lo comentado, para saber los datos de los ultimos 13 meses ( o los que quieras ) con una llamada única independientemente de la fecha de la ejecución, utilizaríamos lo siguente:

Código SQL [-]
select
CLIENTE.CODIGO, CLIENTE.NOMBRE,
SUM(FACTURA.TOTAL) AS TOTAL,

/* a partir de  aqui se ponen las columnas que se quieran */
sum( case when FACTURA.FECHA between dateadd( month , -3, current_date - EXTRACT(DAY FROM current_date) + 1) and
                                     dateadd( month , -2, current_date - EXTRACT(DAY FROM current_date) + 1)
               then FACTURA.TOTAL else 0 end ) as Mes_2,

sum( case when FACTURA.FECHA between dateadd( month , -2, current_date - EXTRACT(DAY FROM current_date) + 1) and
                                     dateadd( month , -1, current_date - EXTRACT(DAY FROM current_date) + 1)
               then FACTURA.TOTAL else 0 end ) as Mes_1,

sum( case when FACTURA.FECHA between dateadd( month , -1, current_date - EXTRACT(DAY FROM current_date) + 1) and
                                     dateadd( month , 0, current_date - EXTRACT(DAY FROM current_date) + 1)
               then FACTURA.TOTAL else 0 end ) as Mes_Actual

from FACTURAS
inner join Clientes on ( Clientes.CODIGO = Factura.COdigoCliente)
where
Factura.fecha between current_date -365 and current_Date /* ajustar los días a restar al nº de columnas */
group by 
CLIENTE.CODIGO, CLIENTE.NOMBRE

Código SQL [-]
current_date - EXTRACT(DAY FROM current_date) + 1

da el primer día del mes actual, al que le voy restando meses con la función DATEADD().

Tengase en cuenta que se deberán poner tantas columnas como se desee, teniendo en cuenta que luego en le filtro habrá que hacer coincidir el nº de días con la profundidad hacia atrás que queramos conseguir ( en este caso 365 días , tendría que haber puesto 12 columnas correspondientes a 12 meses).

Abuelete, ten en cuenta que con esta consulta no tendrás que construir la consulta dinámicamente ni tener en cuenta el año, simplemente tendrás que ajustar los nombres de las columnas.

Otra cosa, por simplificar he puesto la condición between, deberá ser
fecha >= fecha_primer_dia_del mes and fecha < fecha_primer_dia_del_mes_siguiente

A prtir de aquí se puede hacer cualquier cosa que se imagine pivotando sobre esta idea y ajustando las condiciones a lo que queramos.
Espero que te valga.

Un saludo

[[fjcg02]]

Bueno,
los ultimos 5 meses que llevo explotando datos de diferentes fuentes me están haciendo mucho daño cerebral .

Estoy extrayendo datos de SQL Server, Oracle, access, ... construyendo informes diarios, semanales y mensuales. Para ello utilizo mucho ( para los semanales y mensuales ) la técnica que os he expuesto anteriormente, con 24 semanas de profundidad en los informes semanales y 13 meses en los mensuales.

Para extraer la información utilizo unas plantillas en excel que contienen las cabeceras de los datos que extraigo y los gráficos ya montados, para que la misma actualización de los datos en los archivos excel me genere los gráficos de forma automática.

Bueno, eso es todo... hasta que el abuelete pregunte algo más.

Lo que sí os puedo garantizar es que una aplicación ( tpv, gestión de ventas, ... ) con un modulito de cuadro de mando automatizado pasa de ser un programita de pantalón corto a una señora aplicación de pantalón largo....

Un saludo

[[José Luis Garcí]]

Javier eres un




Firmado el abuelete.

[[fjcg02]]

Empezado por José Luis Garcí

Javier eres un




Firmado el abuelete.

Gracias jovencito, pero me has sacao más guapo de lo que soy!!

Saludos
PD: En otro rato explicaré la forma en la que ecfisa obtiene los datos, que también e perfectamente válida.

[[fjcg02]]

Abuelete,
te pongo un link para que estudies un ratito

http://clubdelphi.com/foros/showthre...=trasnformadas

Saludos

[[José Luis Garcí]]

Javier estuve probando lo de dar a los mese para atrás desde la fecha actual, a mi no me funcionaba del todo bien, así que estuve manoseando un poco y la modifique, quedándome así, dime si estoy equivocado, por favor

Código SQL [-]
select
CLIENTES.CODIGO, CLIENTES.NOMBRE,
SUM(FACTURA.TOTAL) AS TOTAL,

/* a partir de  aqui se ponen las columnas que se quieran */


sum( case when FACTURA.FECHA between dateadd( month , -2, current_date - EXTRACT(DAY FROM current_date) + 1) and
                                     dateadd( month , -1, current_date - EXTRACT(DAY FROM current_date) + 1)
               then FACTURA.TOTAL else 0 end ) as Mes_2,

sum( case when FACTURA.FECHA between dateadd( month , -1, current_date - EXTRACT(DAY FROM current_date) + 1) and
                                     dateadd( month , -0, current_date - EXTRACT(DAY FROM current_date) + 1)
               then FACTURA.TOTAL else 0 end ) as Mes_1,


sum( case when FACTURA.FECHA between dateadd( month , 0, current_date - EXTRACT(DAY FROM current_date) + 1) and
                                     dateadd( month , +1, current_date - EXTRACT(DAY FROM current_date) + 1)
               then FACTURA.TOTAL else 0 end ) as Mes_Actual

from FACTURA
inner join Clientes on ( Clientes.CODIGO = Factura.COdigoCliente)
where
Factura.fecha between current_date -365 and current_Date /* ajustar los días a restar al nº de columnas */
group by   CLIENTES.ID,CLIENTES.CODIGO, CLIENTES.NOMBRE

Lo que me ocurría, era que no me arrancaba desde el mes actual, sino desde el anterior tal como lo tenias, corrígeme si estoy equivocado.

[[fjcg02]]

Sí señor, eres un crack
En mi ejemplo yo cogía desde el día 1 del mes actual hacia atrás, lo has bordado.

Respecto a la consulta que ha puesto ecfisa tiene que funcionar perfectamente, en cualquier versión desde que los romanos patrullaban en cuádriga. Estarás haciendo algo mal o se te escapará algún detalle .

Un saludo a todos

[ecfisa]

Hola fjcg02.

Gracias por sacarme la duda

Aunque creo que la mayor parte de los gestores soportan esa sintáxis, yo comenzé a usar Firebird a partir de la versión 2.0 y podía ser que antes no fuera soportada.

Saludos

[ecfisa]

Hola José.

Primero que nada buenísimo que solucionaras el tema .

Empezado por José Luis Garcí

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

En mis pruebas funcionan ambas, pero lo que comentas me genera una duda ¿ Que versión de Firebird estas usando ? por que con la 2.5 no dá nigún error el uso del alias internos (como FA2). Me interesaría saber si da problemas con versiones anteriores.

Saludos