|
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
Y facturas
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. |
Hola compañeros, de momento tengo la siguiente consulta
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 |
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 |
A ver hijito :D:D:D:D
HE intentado, como me dices cambiando el por o
Pero sigue en las mismas, de hecho he quitado incluso la tabla empleados, para facilitarlo, el código esta como te pongo aquí
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. |
Cita:
|
Te comento, he intentado lo de los grupos y no funciona, el código lo he puesto como sigue
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 |
Hola José.
Cita:
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 :) |
Para estos casos ayudaria mucho tener ejemplo de datos!
Y por ejemplo, usando: http://sqlfiddle.com/ |
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 como siempre ecfisa, tienes razón a que sobra datos, pero como no tengo el CLIENTE_ID, lo he intentado modificar por
Pero me da el error "Dinamyc SQL error SQL error code = -206 Ciumn unknown Fa.CODIGO At line 3 Column 58." |
Cita:
|
Cita:
|
Cita:
Aunque se trate de la misma tabla son distintas referencias:
La consulta debería ser:
O, si lo preferís:
Saludos :) |
Cita:
|
Hola de nuevo,
a ver si ahora podemos dar un poco de juego al tema.... Esta es la query de la que partimos... 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.
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 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... |
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.
|
Cita:
Cita:
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
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
Me gustaría, poder especificar el años, así que investigare, pero para ello deberé crear datos falsos, pero lo investigaré Cita:
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
Gracias a todos, he aprendido muchas cosas, espero no olvidarlas, o no perderlas, ya que siempre suelo guardarlas |
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
Por
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 |
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:
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 |
Bueno,
los ultimos 5 meses que llevo explotando datos de diferentes fuentes me están haciendo mucho daño cerebral :D:D. 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....:D Un saludo |
| La franja horaria es GMT +2. Ahora son las 17:24:54. |
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