Análisis de compras de proveedores con sumatorio

SAP Business One
1

Buenos días a todos,

Esta es mi primera consulta en el foro, por lo que pido perdon si no la realizo correctamente o abuso mucho de vosotros.

Resulta que cogí una consulta de este mismo foro que os la pongo

DECLARE @Venta2021 AS NUMERIC

/*
Consulta para ver las ventas de los clientes durante un periodo y da % de las ventas
*/
SET @Venta2021= ( (SELECT Sum (T3.linetotal)
                   FROM   OINV T2
                          INNER JOIN INV1 T3
                                  ON T2.docentry = T3.docentry
                   WHERE  T2.docdate BETWEEN '20210101' AND '20211231')
                  + (SELECT CASE
                              WHEN Sum (T5.linetotal) *- 1 < 0 THEN Sum
                              (T5.linetotal) *- 1
                              ELSE 0
                            END
                     FROM   ORIN T4
                            INNER JOIN RIN1 T5
                                    ON T4.docentry = T5.docentry
                     WHERE  T4.docdate BETWEEN '20210101' AND '20211231') )

SELECT T2.cardcode	        'Cliente',
       T8.cardname       'Nombre Cliente',
       Sum (T3.linetotal)
       'Total Facturado 2021',
       (SELECT CASE
                 WHEN Sum (T5.linetotal) *- 1 < 0 THEN Sum (T5.linetotal) *- 1
                 ELSE 0
               END
        FROM   ORIN T4
               INNER JOIN RIN1 T5
                       ON T4.docentry = T5.docentry
        WHERE  T4.docdate BETWEEN '20210101' AND '20211231'
               AND T2. cardcode = T4.cardcode)
       'Notas de Crédito 2021',
       Sum (T3.linetotal)
       + (SELECT CASE
                   WHEN Sum (T5.linetotal) *- 1 < 0 THEN Sum (T5.linetotal) *- 1
                   ELSE 0
                 END
          FROM   orin T4
                 INNER JOIN rin1 T5
                         ON T4.docentry = T5.docentry
          WHERE  T4.docdate BETWEEN '20210101' AND '20211231'
                 AND T2.cardcode = T4.cardcode)
       'Venta Neta 2021',
       ( Sum (T3.linetotal)
         + (SELECT CASE
                     WHEN Sum (T5.linetotal) *- 1 < 0 THEN
                     Sum (T5.linetotal) *- 1
                     ELSE 0
                   END
            FROM   ORIN T4
                   INNER JOIN RIN1 T5
                           ON T4.docentry = T5.docentry
            WHERE  T4.docdate BETWEEN '20210101' AND '20211231'
                   AND T2.cardcode = T4.cardcode) ) / @Venta2021 * 100 AS '% del total vendido'
FROM   OINV T2
       INNER JOIN INV1 T3
               ON T2.docentry = T3.docentry
       INNER JOIN OCRD T8
               ON T8.[cardcode] = T2.[cardcode]
WHERE  T2.docdate BETWEEN '20210101' AND '20211231'
GROUP  BY T2.cardcode,
          T8.cardname
ORDER  BY 5 DESC

y el resultado es este

onexión a Escritorio remoto
onexión a Escritorio remoto1039×395 28.7 KB

Me gustaría hacer mismo pero con LAS COMPRAS pero soy nuevo y no se, si hubiera alguien que conozca si hay una query ya hecha o me pueda echar una mano estaré eternamente agradecido.

Un saludo a tod@s

2

Buenos días de nuevo, lo he conseguido cambiando las siguientes tablas en la consulta

OINV = OPCH
INV1 = PCH1
ORIN = ORPC
RIN1 = RPC1

Les dejo el código completo por si les sirve de ayuda

DECLARE @Compra2021 AS NUMERIC

/*
Consulta para ver las compras de los Proveedores durante un periodo y da % de las Compras
*/
SET @Compra2021= ( (SELECT Sum (T3.linetotal)
                   FROM   OPCH T2
                          INNER JOIN PCH1 T3
                                  ON T2.docentry = T3.docentry
                   WHERE  T2.docdate BETWEEN '20210101' AND '20211231')
                  + (SELECT CASE
                              WHEN Sum (T5.linetotal) *- 1 < 0 THEN Sum
                              (T5.linetotal) *- 1
                              ELSE 0
                            END
                     FROM   ORPC T4
                            INNER JOIN RPC1 T5
                                    ON T4.docentry = T5.docentry
                     WHERE  T4.docdate BETWEEN '20210101' AND '20211231') )

SELECT T2.cardcode	        'Proveedor',
       T8.cardname       'Nombre Proveedor',
       Sum (T3.linetotal)
       'Total Comprado 2021',
       (SELECT CASE
                 WHEN Sum (T5.linetotal) *- 1 < 0 THEN Sum (T5.linetotal) *- 1
                 ELSE 0
               END
        FROM   ORPC T4
               INNER JOIN RPC1 T5
                       ON T4.docentry = T5.docentry
        WHERE  T4.docdate BETWEEN '20210101' AND '20211231'
               AND T2. cardcode = T4.cardcode)
       'Notas de Crédito 2021',
       Sum (T3.linetotal)
       + (SELECT CASE
                   WHEN Sum (T5.linetotal) *- 1 < 0 THEN Sum (T5.linetotal) *- 1
                   ELSE 0
                 END
          FROM   ORPC T4
                 INNER JOIN RPC1 T5
                         ON T4.docentry = T5.docentry
          WHERE  T4.docdate BETWEEN '20210101' AND '20211231'
                 AND T2.cardcode = T4.cardcode)
       'Venta Neta 2021',
       ( Sum (T3.linetotal)
         + (SELECT CASE
                     WHEN Sum (T5.linetotal) *- 1 < 0 THEN
                     Sum (T5.linetotal) *- 1
                     ELSE 0
                   END
            FROM   ORPC T4
                   INNER JOIN RPC1 T5
                           ON T4.docentry = T5.docentry
            WHERE  T4.docdate BETWEEN '20210101' AND '20211231'
                   AND T2.cardcode = T4.cardcode) ) / @Compra2021 * 100 AS '% del total comprado'
FROM   OPCH T2
       INNER JOIN PCH1 T3
               ON T2.docentry = T3.docentry
       INNER JOIN OCRD T8
               ON T8.[cardcode] = T2.[cardcode]
WHERE  T2.docdate BETWEEN '20210101' AND '20211231'
GROUP  BY T2.cardcode,
          T8.cardname
ORDER  BY 5 DESC
2 Me gusta