Club Delphi  
    FTP   CCD     Buscar   Trucos   Trabajo   Foros

Retroceder   Foros Club Delphi > Principal > SQL
Registrarse FAQ Miembros Calendario Guía de estilo Buscar Temas de Hoy Marcar Foros Como Leídos

Respuesta
 
Herramientas Buscar en Tema Desplegado
  #1  
Antiguo 26-04-2014
Avatar de José Luis Garcí
[José Luis Garcí] José Luis Garcí is offline
Miembro Premium
 
Registrado: may 2003
Ubicación: Las Palmas de G.C.
Posts: 1.372
Poder: 22
José Luis Garcí Va camino a la fama
Ayuda para montar una consulta

Hola compañeros me encuentro un poco torpe no se si es por la hora o por que aun no me he acostado, perro llevo un rato y no consigo lo que quiero y os pido ayuda para que me ayudéis a como montar la siguiente consulta.

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.

La estructura de las tablas Cliente y Facturas os la detallo a continuación:

CLIENTES

Código Delphi [-]
CREATE TABLE CLIENTES (
    ID                 INTEGER NOT NULL,
    CODIGO             CODIGO /* CODIGO = VARCHAR(20) NOT NULL */,
    NOMBRE             V80 /* V80 = VARCHAR(80) */,
    IMAGEN             IMAGEN /* IMAGEN = BLOB SUB_TYPE 0 SEGMENT SIZE 80 */,
    OBSERVACIONES      MEMO /* MEMO = BLOB SUB_TYPE 1 SEGMENT SIZE 80 */,
    FECHAALTA          FECHA /* FECHA = DATE */,
    ACTIVO             LOGIC /* LOGIC = CHAR(1) */,
    DESCUENTOMAXIMO    POR /* POR = NUMERIC(15,4) */,
    CODIGOFORMAPAGO    CODIGO /* CODIGO = VARCHAR(20) NOT NULL */,
    CODIGOIMPUESTO     CODIGO /* CODIGO = VARCHAR(20) NOT NULL */,
    DOCUMENTO          V20 /* V20 = VARCHAR(20) */,
    TIPODOCUMENTO      V20 /* V20 = VARCHAR(20) */,
    RETENCIONES        POR /* POR = NUMERIC(15,4) */,
    REQ                POR /* POR = NUMERIC(15,4) */,
    TAFIRA             INTEGER,
    DIASPRESENTACION   V20 /* V20 = VARCHAR(20) */,
    DIASCOBRO          V20 /* V20 = VARCHAR(20) */,
    AVISOS             MEMO /* MEMO = BLOB SUB_TYPE 1 SEGMENT SIZE 80 */,
    LIMITECREDITO      POR /* POR = NUMERIC(15,4) */,
    PENDIENTEPAGO      POR /* POR = NUMERIC(15,4) */,
    AGENTE             V20 /* V20 = VARCHAR(20) */,
    SECTOR             V40 /* V40 = VARCHAR(40) */,
    DIRECCION          V80 /* V80 = VARCHAR(80) */,
    CODIGOPOSTAL       V5 /* V5 = VARCHAR(5) */,
    POBLACION          V40 /* V40 = VARCHAR(40) */,
    PROVINCIA          V40 /* V40 = VARCHAR(40) */,
    PAIS               V20 /* V20 = VARCHAR(20) */,
    TELEFONO           V20 /* V20 = VARCHAR(20) */,
    FAX                V20 /* V20 = VARCHAR(20) */,
    MOVIL              V20 /* V20 = VARCHAR(20) */,
    PERSONADECONTACTO  V80 /* V80 = VARCHAR(80) */,
    WEB                V150 /* V150 = VARCHAR(150) */,
    MAIL               V80 /* V80 = VARCHAR(80) */,
    LIBRE              V80 /* V80 = VARCHAR(80) */,
    USARREQ            LOGIC /* LOGIC = CHAR(1) */,
    DISTRIBUIDOR       LOGIC /* LOGIC = CHAR(1) */
);

Y facturas

Código Delphi [-]
CREATE TABLE FACTURA (
    ID                   INTEGER NOT NULL,
    NUMERO               CODIGO /* CODIGO = VARCHAR(20) NOT NULL */,
    CODIGOCLIENTE        CODIGO /* CODIGO = VARCHAR(20) NOT NULL */,
    CODIGOAGENTE         CODIGO /* CODIGO = VARCHAR(20) NOT NULL */,
    TOTALCOMISION        POR /* POR = NUMERIC(15,4) */,
    CODIGOFORMAPAGO      CODIGO /* CODIGO = VARCHAR(20) NOT NULL */,
    IMPUESTO1            POR /* POR = NUMERIC(15,4) */,
    IMPUESTO2            POR /* POR = NUMERIC(15,4) */,
    IMPUESTO3            POR /* POR = NUMERIC(15,4) */,
    IMPUESTO4            POR /* POR = NUMERIC(15,4) */,
    IMPUESTO5            POR /* POR = NUMERIC(15,4) */,
    PESOTOTAL            POR /* POR = NUMERIC(15,4) */,
    SUBTOTAL             POR /* POR = NUMERIC(15,4) */,
    TOTALIMPUESTOS       POR /* POR = NUMERIC(15,4) */,
    TOTAL                POR /* POR = NUMERIC(15,4) */,
    TOTALDESCUENTOS      POR /* POR = NUMERIC(15,4) */,
    SERIE                V5 /* V5 = VARCHAR(5) */,
    NOTA                 MEMO /* MEMO = BLOB SUB_TYPE 1 SEGMENT SIZE 80 */,
    FECHA                FECHA /* FECHA = DATE */,
    FECHAPAGO            FECHA /* FECHA = DATE */,
    COBRADA              LOGIC /* LOGIC = CHAR(1) */,
    RECARGOEQUIVALENCIA  POR /* POR = NUMERIC(15,4) */,
    LIBRE                V80 /* V80 = VARCHAR(80) */,
    PENDIENTECOBRO       POR /* POR = NUMERIC(15,4) */,
    RF                   V20 /* V20 = VARCHAR(20) */
);

El que los clientes que no hyan comprado entre las fechas dadas lo ponga a 0 es importante, ya que en el grid, los marcara en rojo, mientras que al resto en verde.

Claro esta que mejor si sólo aparece un registro por cliente con el total de todas las facturas que tenga en ese mes

Desde ya os doy las gracias, ahora me voy a dormir.
__________________
Un saludo desde Canarias, "El abuelo Cebolleta"
Responder Con Cita
  #2  
Antiguo 26-04-2014
Avatar de José Luis Garcí
[José Luis Garcí] José Luis Garcí is offline
Miembro Premium
 
Registrado: may 2003
Ubicación: Las Palmas de G.C.
Posts: 1.372
Poder: 22
José Luis Garcí Va camino a la fama
Hola compañeros, de momento tengo la siguiente consulta

Código Delphi [-]
SELECT
  CLIENTES.CODIGO,
  CLIENTES.NOMBRE,
  FACTURA.NUMERO,
  FACTURA.SERIE,
  FACTURA.FECHA,
  FACTURA.CODIGOAGENTE,
  EMPLEADOS.NOMBRE,
  FACTURA.TOTAL,
  FACTURA.COBRADA
FROM
 CLIENTES
 INNER JOIN FACTURA ON (CLIENTES.CODIGO=FACTURA.CODIGOCLIENTE)
 INNER JOIN EMPLEADOS ON (FACTURA.CODIGOAGENTE=EMPLEADOS.CODIGO)
WHERE
  (FACTURA.FECHA BETWEEN '04/01/2014' AND '04/30/2014')
ORDER BY
  CLIENTES.ID

Pero esta sólo me da los clientes que han comprado, en en esas fechas, y además me da un registro por cada factura, cuando lo que quiero, es que si el cliente no ha comprado, rellene el total con un 0 y además totalice todas las facturas en un sólo registro, sigo trabajando en ello, si voy consiguiendo algo más lo iré publicando
__________________
Un saludo desde Canarias, "El abuelo Cebolleta"
Responder Con Cita
  #3  
Antiguo 26-04-2014
Avatar de fjcg02
[fjcg02] fjcg02 is offline
Miembro Premium
 
Registrado: dic 2003
Ubicación: Zamudio
Posts: 1.408
Poder: 22
fjcg02 Va camino a la fama
A ver abuelete ,
Para qué la consulta te devuelva los registros que no están en la otra tabla, tienes que utilizar LEFT JOIN en lugar de INNER JOIN. LEFT JOIN o rigth JOIN dependiendo del caso.

Para que además te agrupe por cliente, tendrás que agrupar los registros. Para ello utiliza GROUP BY , teniendo en cuenta que aquellos campos que no estén en el GROUP by deberán tener una función de agregado: sum, avrg , min., max .
Ahora bien, si añades al vendedor, te agrupara por cliente y vendedor, que me parece que no es lo que quieres.
Juega un poco con esto que te he dicho a ver si das con la solución. En caso negativo, ya te responderé más concretamente, pero es que ahora desde la tableta no puedo hacer pruebas.

Otra cosa que me gusta mucho a mi es sacar los meses en columnas, y una columna con el total.

Para eso una vez consigas la consulta, tienes que hacer algo así:
Select campos...
Sum(factura.total) as total,
Sum (case when month(factura.fecha)=1 then factura.total else 0 end ) as Enero,
...
Sum (case when month(factura.fecha)=12 then factura.total else 0 end ) as diciembre

From
Clientes
LEFT JOIN facturas on factura.idcliente =cliente.id
GROUP by campo1, ...

Espero que te sirva.

Saludos
__________________
Cuando los grillos cantan, es que es de noche - viejo proverbio chino -
Responder Con Cita
  #4  
Antiguo 26-04-2014
Avatar de José Luis Garcí
[José Luis Garcí] José Luis Garcí is offline
Miembro Premium
 
Registrado: may 2003
Ubicación: Las Palmas de G.C.
Posts: 1.372
Poder: 22
José Luis Garcí Va camino a la fama
A ver hijito
HE intentado, como me dices cambiando el

Código Delphi [-]
INNER JOIN FACTURA ON (CLIENTES.CODIGO=FACTURA.CODIGOCLIENTE)
por
Código Delphi [-]
LEFT JOIN FACTURA ON (CLIENTES.CODIGO=FACTURA.CODIGOCLIENTE)
o
Código Delphi [-]
RIGHT JOIN FACTURA ON (CLIENTES.CODIGO=FACTURA.CODIGOCLIENTE)

Pero sigue en las mismas, de hecho he quitado incluso la tabla empleados, para facilitarlo, el código esta como te pongo aquí

Código Delphi [-]
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

Pero sigue en las mismas ni aparecen los clientes que no han comprado, ya sabes tirarme de las orejas si estoy equivocado, voy a probar ahora lo de agruparlos y cuando puedas ya me indicaras si no te molesta.
__________________
Un saludo desde Canarias, "El abuelo Cebolleta"
Responder Con Cita
  #5  
Antiguo 26-04-2014
Avatar de José Luis Garcí
[José Luis Garcí] José Luis Garcí is offline
Miembro Premium
 
Registrado: may 2003
Ubicación: Las Palmas de G.C.
Posts: 1.372
Poder: 22
José Luis Garcí Va camino a la fama
Cita:
Empezado por fjcg02 Ver Mensaje
Otra cosa que me gusta mucho a mi es sacar los meses en columnas, y una columna con el total.

Para eso una vez consigas la consulta, tienes que hacer algo así:
Select campos...
Sum(factura.total) as total,
Sum (case when month(factura.fecha)=1 then factura.total else 0 end ) as Enero,
...
Sum (case when month(factura.fecha)=12 then factura.total else 0 end ) as diciembre

From
Clientes
LEFT JOIN facturas on factura.idcliente =cliente.id
GROUP by campo1, ...
Esto me parece muy interesante, cuando puedas sube el código y si puedes una imagen de ejemplo, como siempre gracias Javier.
__________________
Un saludo desde Canarias, "El abuelo Cebolleta"
Responder Con Cita
  #6  
Antiguo 26-04-2014
Avatar de José Luis Garcí
[José Luis Garcí] José Luis Garcí is offline
Miembro Premium
 
Registrado: may 2003
Ubicación: Las Palmas de G.C.
Posts: 1.372
Poder: 22
José Luis Garcí Va camino a la fama
Te comento, he intentado lo de los grupos y no funciona, el código lo he puesto como sigue

Código Delphi [-]
SELECT
  CLIENTES.ID,
  CLIENTES.CODIGO,
  CLIENTES.NOMBRE,
  FACTURA.NUMERO,
  FACTURA.SERIE,
  FACTURA.FECHA,
  FACTURA.CODIGOAGENTE,
  FACTURA.COBRADA,
  SUM(FACTURA.TOTAL) AS TOTALFINAL
FROM
 CLIENTES
 RIGHT OUTER JOIN FACTURA ON (CLIENTES.CODIGO=FACTURA.CODIGOCLIENTE)
WHERE
  (FACTURA.FECHA BETWEEN '04/01/2014' AND '04/30/2014')
GROUP BY
  CLIENTES.ID,
  CLIENTES.NOMBRE,
  FACTURA.NUMERO,
  FACTURA.SERIE,
  FACTURA.FECHA,
  FACTURA.CODIGOAGENTE,
  FACTURA.COBRADA,
  CLIENTES.CODIGO

y este es el resultado




Te he marcado en rojo, tanto el código que es el del cliente, puedes ver que en vez de haber un único registro hay varios y en los totales, la cantidad de cada factura
__________________
Un saludo desde Canarias, "El abuelo Cebolleta"
Responder Con Cita
  #7  
Antiguo 26-04-2014
Avatar de ecfisa
ecfisa ecfisa is offline
Moderador
 
Registrado: dic 2005
Ubicación: Tres Arroyos, Argentina
Posts: 10.508
Poder: 36
ecfisa is a splendid one to beholdecfisa is a splendid one to beholdecfisa is a splendid one to beholdecfisa is a splendid one to beholdecfisa is a splendid one to beholdecfisa is a splendid one to beholdecfisa is a splendid one to behold
Hola José.
Cita:
Empezado por José Luis Garcí Ver Mensaje
...
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
__________________
Daniel Didriksen

Guía de estilo - Uso de las etiquetas - La otra guía de estilo ....

Última edición por ecfisa fecha: 26-04-2014 a las 19:34:52. Razón: ortografía
Responder Con Cita
  #8  
Antiguo 26-04-2014
Avatar de mamcx
mamcx mamcx is offline
Moderador
 
Registrado: sep 2004
Ubicación: Medellín - Colombia
Posts: 3.911
Poder: 25
mamcx Tiene un aura espectacularmamcx Tiene un aura espectacularmamcx Tiene un aura espectacular
Para estos casos ayudaria mucho tener ejemplo de datos!

Y por ejemplo, usando:

http://sqlfiddle.com/
__________________
El malabarista.
Responder Con Cita
  #9  
Antiguo 26-04-2014
Avatar de fjcg02
[fjcg02] fjcg02 is offline
Miembro Premium
 
Registrado: dic 2003
Ubicación: Zamudio
Posts: 1.408
Poder: 22
fjcg02 Va camino a la fama
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
__________________
Cuando los grillos cantan, es que es de noche - viejo proverbio chino -
Responder Con Cita
  #10  
Antiguo 26-04-2014
Avatar de José Luis Garcí
[José Luis Garcí] José Luis Garcí is offline
Miembro Premium
 
Registrado: may 2003
Ubicación: Las Palmas de G.C.
Posts: 1.372
Poder: 22
José Luis Garcí Va camino a la fama
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."
__________________
Un saludo desde Canarias, "El abuelo Cebolleta"
Responder Con Cita
  #11  
Antiguo 26-04-2014
Avatar de José Luis Garcí
[José Luis Garcí] José Luis Garcí is offline
Miembro Premium
 
Registrado: may 2003
Ubicación: Las Palmas de G.C.
Posts: 1.372
Poder: 22
José Luis Garcí Va camino a la fama
Cita:
Empezado por fjcg02 Ver Mensaje
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.
__________________
Un saludo desde Canarias, "El abuelo Cebolleta"
Responder Con Cita
  #12  
Antiguo 26-04-2014
Avatar de José Luis Garcí
[José Luis Garcí] José Luis Garcí is offline
Miembro Premium
 
Registrado: may 2003
Ubicación: Las Palmas de G.C.
Posts: 1.372
Poder: 22
José Luis Garcí Va camino a la fama
Cita:
Empezado por mamcx Ver Mensaje
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?.
__________________
Un saludo desde Canarias, "El abuelo Cebolleta"
Responder Con Cita
  #13  
Antiguo 26-04-2014
Avatar de ecfisa
ecfisa ecfisa is offline
Moderador
 
Registrado: dic 2005
Ubicación: Tres Arroyos, Argentina
Posts: 10.508
Poder: 36
ecfisa is a splendid one to beholdecfisa is a splendid one to beholdecfisa is a splendid one to beholdecfisa is a splendid one to beholdecfisa is a splendid one to beholdecfisa is a splendid one to beholdecfisa is a splendid one to behold
Cita:
Empezado por José Luis Garcí Ver Mensaje
...
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
__________________
Daniel Didriksen

Guía de estilo - Uso de las etiquetas - La otra guía de estilo ....

Última edición por ecfisa fecha: 26-04-2014 a las 21:08:59.
Responder Con Cita
  #14  
Antiguo 26-04-2014
Avatar de mamcx
mamcx mamcx is offline
Moderador
 
Registrado: sep 2004
Ubicación: Medellín - Colombia
Posts: 3.911
Poder: 25
mamcx Tiene un aura espectacularmamcx Tiene un aura espectacularmamcx Tiene un aura espectacular
Cita:
Empezado por José Luis Garcí Ver Mensaje
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
__________________
El malabarista.
Responder Con Cita
  #15  
Antiguo 26-04-2014
Avatar de fjcg02
[fjcg02] fjcg02 is offline
Miembro Premium
 
Registrado: dic 2003
Ubicación: Zamudio
Posts: 1.408
Poder: 22
fjcg02 Va camino a la fama
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...
__________________
Cuando los grillos cantan, es que es de noche - viejo proverbio chino -
Responder Con Cita
  #16  
Antiguo 27-04-2014
Avatar de mamcx
mamcx mamcx is offline
Moderador
 
Registrado: sep 2004
Ubicación: Medellín - Colombia
Posts: 3.911
Poder: 25
mamcx Tiene un aura espectacularmamcx Tiene un aura espectacularmamcx Tiene un aura espectacular
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.
__________________
El malabarista.
Responder Con Cita
  #17  
Antiguo 27-04-2014
Avatar de José Luis Garcí
[José Luis Garcí] José Luis Garcí is offline
Miembro Premium
 
Registrado: may 2003
Ubicación: Las Palmas de G.C.
Posts: 1.372
Poder: 22
José Luis Garcí Va camino a la fama
Cita:
Empezado por ecfisa Ver Mensaje
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

Cita:
Empezado por fjcg02 Ver Mensaje
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é

Cita:
Empezado por mamcx Ver Mensaje
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"
Responder Con Cita
  #18  
Antiguo 27-04-2014
Avatar de José Luis Garcí
[José Luis Garcí] José Luis Garcí is offline
Miembro Premium
 
Registrado: may 2003
Ubicación: Las Palmas de G.C.
Posts: 1.372
Poder: 22
José Luis Garcí Va camino a la fama
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
__________________
Un saludo desde Canarias, "El abuelo Cebolleta"

Última edición por José Luis Garcí fecha: 27-04-2014 a las 10:49:26.
Responder Con Cita
  #19  
Antiguo 27-04-2014
Avatar de fjcg02
[fjcg02] fjcg02 is offline
Miembro Premium
 
Registrado: dic 2003
Ubicación: Zamudio
Posts: 1.408
Poder: 22
fjcg02 Va camino a la fama
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
__________________
Cuando los grillos cantan, es que es de noche - viejo proverbio chino -
Responder Con Cita
  #20  
Antiguo 27-04-2014
Avatar de fjcg02
[fjcg02] fjcg02 is offline
Miembro Premium
 
Registrado: dic 2003
Ubicación: Zamudio
Posts: 1.408
Poder: 22
fjcg02 Va camino a la fama
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
__________________
Cuando los grillos cantan, es que es de noche - viejo proverbio chino -
Responder Con Cita
Respuesta


Herramientas Buscar en Tema
Buscar en Tema:

Búsqueda Avanzada
Desplegado

Normas de Publicación
no Puedes crear nuevos temas
no Puedes responder a temas
no Puedes adjuntar archivos
no Puedes editar tus mensajes

El código vB está habilitado
Las caritas están habilitado
Código [IMG] está habilitado
Código HTML está deshabilitado
Saltar a Foro

Temas Similares
Tema Autor Foro Respuestas Último mensaje
Servicios gratuitos para montar una empresa (WEB) MAXIUM La Taberna 9 05-11-2013 09:18:02
Solicito ayuda en cuestiones de montar un TPV José Luis Garcí Varios 10 14-03-2012 10:05:49
Seleccion multiple para montar query tgsistemas SQL 8 22-04-2010 19:48:19
Montar un apache para dominio publico, que necesito... alexander66 PHP 2 28-03-2007 01:52:33
Montar red para 2 PCs alfredosg19 Redes 5 17-07-2006 10:07:23


La franja horaria es GMT +2. Ahora son las 19:27:36.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Traducción al castellano por el equipo de moderadores del Club Delphi
Copyright 1996-2007 Club Delphi