none
Detalles, grupo y condiciones RRS feed

  • Pregunta

  • Buenas tardes, no sé atacar la consulta.

    Típica base de datos de Clientes, Facturas y LineasFaturas

    He de listar las facturas totalizadas es decir, si una factura consta de 7 líneas mostrar su total

    Cliente1 F1 1.500

    Cliente1 F2 1.750

    Cliente2 F3 2.589

    Cliente2 F4 150

    Cliente3 F5 2.369

    Cliente4 F6 4.569

    Cliente5 F7 250

    Cliente5 F8 350

    Cliente6 F9 1.000

    Cliente6 F10 1.000

    Cliente6 F11 2.000

    Como es natural cada Cliente tendrán muchas facturas, pues del listado anterior sólo mostrar los registros que cumplan que la suma de sus facturas sea superior, por ejemplo a 3.000, en este caso debería mostrar: facturas del cliente1, facturas del cliente4 y facturas del Cliente6

    La Consulta debería mostrar estos datos (la suma de sus facturas es superior, por ejemplo a 3.000)

    Cliente1 F1 1.500

    Cliente1 F2 1.750

    Cliente4 F6 4.569

    Cliente6 F9 1.000

    Cliente6 F10 1.000

    Cliente6 F11 2.000

    Muchísimas gracias por su tiempo y disculpas por ....

    viernes, 9 de abril de 2021 11:28

Todas las respuestas

  • Esta consulta no funciona:

    SELECT

    Tlineas.num,
    sum(Tlineas.cantidad * Tlineas.precio) AS Total

    FROM
    Tfacturas INNER JOIN Tlineas ON Tfacturas.num = Tlineas.num

    WHERE
    Tfacturas.NIF is not null
    AND
    Tfacturas.FECHA BETWEEN '01/01/2021' AND '31/12/2021'

    GROUP BY Tlineas.num

    HAVING SUM(Tlineas.cantidad * Tlineas.precio)>=3005.06

    NO MUESTRA LOS CLIENTES CON VARIAS, VARIAS, VARIAS FACTURAS QUE SU SUMA si supera las 3005,06, el CLIENTE1, CLIENTE6 LO deja fuera, 

    viernes, 9 de abril de 2021 11:31
  • El detalle es que el listado debe mostrar

    una relación de facturas de los clientes que tengan facturas (su suma) por encima de un valor y entonces MOSTRAR todas las facturas que su suma sea superior a una cantidad

    viernes, 9 de abril de 2021 11:35
  • Hola dgironal:

    Existen varias posibilidades de hacer lo que indicas. Una posibilidad.

    drop table if exists TClientes;
    drop table if exists TFacturas;
    drop table if exists Tlineas;
    Create table TClientes (idCliente int, Nombre varchar(100), Nif varchar(10));
    Create table TFacturas (Num int, idCliente int, fecha date);
    Create table TLineas(Num int, Linea int, Cantidad int, Precio float);
    GO
    INSERT INTO TClientes (idCliente, Nombre, Nif)
    values
    (1,'Cliente1','1000001'),
    (2,'Cliente2','1000002'),
    (3,'Cliente3','1000003');
    
    Insert into TFacturas(Num, idCliente, Fecha)
    values
    (1,1,'20210101'),
    (2,1,'20210101'),
    (3,2,'20210201'),
    (4,2,'20210301'),
    (5,3,'20210401');
    Insert into TLineas (Num, Linea, Cantidad, Precio)
    values
    (1,1,10,100),
    (1,2,5,100),
    (2,1,10,100),
    (2,2,75,10),
    (3,1,20,100),
    (3,2,1,589),
    (4,2,10,100),
    (4,2,5,10),
    (5,1,10,100),
    (5,2,10,100);

    Como ves, el cliente, está en otra tabla, no en la de facturas. En muchos escenarios, la factura, (cabecera), suele contener el total de la suma de las lineas, con lo cual no es necesario, hacer lo que propones.

    En el ejemplo, solo he construido facturas y lineas para 3 clientes, donde el cliente 1 y el cliente 2, la suma de las lineas de sus facturas computan más de 3000, pero el cliente 3 no llega.

    Al vuelo una posible solución puede ser.

    With r As (Select Sum(l.cantidad * l.precio) As totfactura , t.num , t.idcliente From tfacturas As t Join tlineas As l On t.num = l.num Where t.fecha >= '20210101' And t.fecha < '20220101' Group By t.idcliente , t.num), r2 As (Select Sum(r.totfactura) As total , r.idcliente From r Group By r.idcliente Having Sum(r.totfactura) > 3000) Select  tclientes.idCliente

    , TClientes.Nombre

    , TClientes.Nif

    , r.Num

    , r.totfactura

    , r2.total as TotalSumaCliente From r Join r2 On r.idcliente = r2.idcliente Join tclientes On r.idcliente = tclientes.idcliente;

    En el conjunto r leemos agrupadas, las facturas por cliente.

    En el conjunto r2 leemos la suma total de facturas por cliente, que sean superiores a 3000.

    A la salida de ambos conjuntos relacionamos ambos por idCliente y luego con clientes para obtener la información a presentar.

    Tablas de expresión común correlativas

    https://javifer2.wordpress.com/2018/12/18/with-cte-tablas-de-expresion-comun-2-correlativos/

    • Propuesto como respuesta Alejandro 0991 viernes, 9 de abril de 2021 19:05
    viernes, 9 de abril de 2021 15:13
  • Muchísimas gracias, te agradezco el interés. Insisto el compartir, el ayudar siempre es de agradecer. Voy a probarlo y comento en el foro qué tal.
    sábado, 10 de abril de 2021 8:29
  • Javier, funciona tal y como necesitaba, la verdad es que ni por asoma hubiera llegado a implementar una solución tal elegante. 

    Ahora, viene lo peor (por mi parte), sé que es ABUSAR, pero ... ¿sería posible incluir en la consulta el campo FechaFactura? (está almacenada en la Tabla Tfacturas)

    Insisto que no tengo perdón de dios por abusar de esta manera. PROMETO que pase lo que pase no abusaré más ;-)

    Gracias!, cualquiera que se encuentre en esta situación la propuesta de Javier es PERFECTA, didáctica, elegante...

    sábado, 10 de abril de 2021 8:46
  • Hola dgironal:

    Claro, el cambio es muy pequeño.

    With r
    	 As (Select Sum(l.cantidad * l.precio) As totfactura
    			  , t.num
    			  , t.fecha
    			  , t.idcliente
    				From tfacturas As t
    					 Join tlineas As l On t.num = l.num
    				Where t.fecha >= '20210101' And t.fecha < '20220101'
    				Group By t.idcliente
    					   , t.num
    					   , t.fecha
    					   ),
    	 r2
    	 As (Select Sum(r.totfactura) As total
    			  , r.idcliente
    				From r
    				Group By r.idcliente
    				Having Sum(r.totfactura) > 3000)
    	 Select  tclientes.idCliente
                   , TClientes.Nombre
                   , TClientes.Nif
                   , r.Num
    			   , r.fecha
                   , r.totfactura
                   , r2.total as TotalSumaCliente
    			From r
    				 Join r2 On r.idcliente = r2.idcliente
    				 Join tclientes On r.idcliente = tclientes.idcliente;

    En el conjunto r, incluyes la fecha y la incluyes en el Group By. 

    Luego en la salida, la mencionas.

    No cambia nada, porque el group by por numero de factura, ya hace la separación única de facturas, por tanto no te va a influir en la consulta.

    Group by quizás la clausula más conflictiva

    https://javifer2.wordpress.com/2019/10/04/group-by-quizas-la-clausula-mas-conflictiva/

    Nota adicional: Recuerda que si la fecha no la visualizas en el formato que te interese, porque la devuelve como yyyy-MM-dd, la puedes cambiar utilizando la función Convert o Format y dándole el formato que quieras

    • Propuesto como respuesta Alejandro 0991 lunes, 12 de abril de 2021 13:26
    sábado, 10 de abril de 2021 10:34
  • Mil gracias, siempre hay personas que desinteresadamente ceden parte de sus conocimientos y TIEMPO.

    Tú solución es elegante.

    sábado, 10 de abril de 2021 11:05