Ayuda para montar una consulta

[[José Luis Garcí]]

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.

[[José Luis Garcí]]

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

[[fjcg02]]

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

[[José Luis Garcí]]

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.

[[José Luis Garcí]]

Empezado por fjcg02

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.

[[José Luis Garcí]]

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

[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