Skip to Content

Consulta Ventas por Grupo

Les dejo la siguiente consulta para consultar ventas netas por grupo de inventario

espero que les sea útil

SELECT     Tipo,

                      CASE Tz.Mes WHEN 1 THEN ’01-Ene’ WHEN 2 THEN ’02-Feb’ WHEN 3 THEN ’03-Mar’ WHEN 4 THEN ’04-Abr’ WHEN 5 THEN ’05-May’ WHEN 6 THEN ’06-Jun’ WHEN 7

                       THEN ’07-Jul’ WHEN 8 THEN ’08-Ago’ WHEN 9 THEN ’09-Sep’ WHEN 10 THEN ’10-Oct’ WHEN 11 THEN ’11-Nov’ WHEN 12 THEN ’12-Dic’ END + ‘-‘ + CAST(Year AS char)

                       AS MesNombre,

                      CASE Tz.Mes WHEN 1 THEN ’01-ENERO’ WHEN 2 THEN ’02-FEBRERO’ WHEN 3 THEN ’03-MARZO’ WHEN 4 THEN ’04-ABRIL’ WHEN 5 THEN ’05-MAYO’ WHEN 6 THEN

                       ’06-JUNIO’ WHEN 7 THEN ’07-JULIO’ WHEN 8 THEN ’08-AGOSTO’ WHEN 9 THEN ’09-SEPTIEMBRE’ WHEN 10 THEN ’10-OCTUBRE’ WHEN 11 THEN ’11-NOVIEMBRE’ WHEN

                       12 THEN ’12-DICIEMBRE’ END AS MesNom, FECHA, Year, Mes, CardCode, CardName, SalPackMsr, SalPackUn, ItmsGrpCod, ItmsGrpNam, ItemCode, ItemName,

                      SUM(Quantity) AS Cantidad, SUM(LineTotal) AS Total, CASE WHEN SUM(Quantity) = 0 THEN 0 ELSE SUM(Quantity) / SalPackUn END AS Unidad_Venta

FROM         (SELECT     ‘FACTURACION’ AS Tipo, T0.DocDate – (DAY(T0.DocDate) – 1) AS FECHA, YEAR(T0.DocDate) AS Year, MONTH(T0.DocDate) AS Mes, T3.CardCode,

                                              T3.CardName, T2.SalPackMsr, T2.SalPackUn, T4.ItmsGrpCod, T4.ItmsGrpNam, T1.ItemCode, T2.ItemName, T1.Quantity * T1.NumPerMsr AS Quantity,

                                              T1.LineTotal

                       FROM          dbo.OINV AS T0 INNER JOIN

                                              dbo.INV1 AS T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER JOIN

                                              dbo.OITM AS T2 ON T1.ItemCode = T2.ItemCode LEFT OUTER JOIN

                                              dbo.OCRD AS T3 ON T2.CardCode = T3.CardCode LEFT OUTER JOIN

                                              dbo.OITB AS T4 ON T2.ItmsGrpCod = T4.ItmsGrpCod

                       UNION ALL

                       SELECT     ‘FACTURACION’ AS Tipo, T0.DocDate – (DAY(T0.DocDate) – 1) AS FECHA, YEAR(T0.DocDate) AS Year, MONTH(T0.DocDate) AS Mes, T3.CardCode,

                                             T3.CardName, T2.SalPackMsr, T2.SalPackUn, T4.ItmsGrpCod, T4.ItmsGrpNam, T1.ItemCode, T2.ItemName, – (T1.Quantity * T1.NumPerMsr) AS Quantity,

                                             – T1.LineTotal AS Expr2

                       FROM         dbo.ORIN AS T0 INNER JOIN

                                             dbo.RIN1 AS T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER JOIN

                                             dbo.OITM AS T2 ON T1.ItemCode = T2.ItemCode LEFT OUTER JOIN

                                             dbo.OCRD AS T3 ON T2.CardCode = T3.CardCode LEFT OUTER JOIN

                                             dbo.OITB AS T4 ON T2.ItmsGrpCod = T4.ItmsGrpCod

                       WHERE     (T0.U_TipoNota <> ‘D’)

                       UNION ALL

                       SELECT     ‘DEVOLUCION’ AS Tipo, T0.DocDate – (DAY(T0.DocDate) – 1) AS FECHA, YEAR(T0.DocDate) AS Year, MONTH(T0.DocDate) AS Mes, T3.CardCode,

                                             T3.CardName, T2.SalPackMsr, T2.SalPackUn, T4.ItmsGrpCod, T4.ItmsGrpNam, T1.ItemCode, T2.ItemName, – (T1.Quantity * T1.NumPerMsr) AS Quantity,

                                             – T1.LineTotal AS Expr2

                       FROM         dbo.ORIN AS T0 INNER JOIN

                                             dbo.RIN1 AS T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER JOIN

                                             dbo.OITM AS T2 ON T1.ItemCode = T2.ItemCode LEFT OUTER JOIN

                                             dbo.OCRD AS T3 ON T2.CardCode = T3.CardCode LEFT OUTER JOIN

                                             dbo.OITB AS T4 ON T2.ItmsGrpCod = T4.ItmsGrpCod

                       WHERE     (T0.U_TipoNota = ‘D’)) AS TZ

GROUP BY Tipo, FECHA, Year, Mes, CardCode, CardName, SalPackMsr, SalPackUn, ItmsGrpCod, ItmsGrpNam, ItemCode, ItemName

10 Comments
You must be Logged on to comment or reply to a post.
  • Hola buenas tardes al correr la consulta me manda el siguiente error:

    1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name ‘U_TipoNota’. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid column name ‘U_TipoNota’. 3). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement ‘Alertas recibidas’ (OAIB) (s) could not be prepared.

    • Este campo U_TipoNota se utiliza en Colombia para la localización, la cual nos dice si na nota Crédito es por anulación o Devolución Seria que quitaras de este campo la consulta si no tienes la localización para Colombia  

    • Hola Gustavo te comento que borre las líneas del campo pero ahora me manda el siguiente error te anexo código como lo deje:

      1). [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near ‘ItemName’. 2). [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement ‘Acuerdo global’ (OOAT) (s) could not be prepared.

      SELECT     Tipo,

                            CASE Tz.Mes WHEN 1 THEN ’01-Ene’ WHEN 2 THEN ’02-Feb’ WHEN 3 THEN ’03-Mar’ WHEN 4 THEN ’04-Abr’ WHEN 5 THEN ’05-May’ WHEN 6 THEN ’06-Jun’ WHEN 7

                             THEN ’07-Jul’ WHEN 8 THEN ’08-Ago’ WHEN 9 THEN ’09-Sep’ WHEN 10 THEN ’10-Oct’ WHEN 11 THEN ’11-Nov’ WHEN 12 THEN ’12-Dic’ END + ‘-‘ + CAST(Year AS char)

                             AS MesNombre,

                            CASE Tz.Mes WHEN 1 THEN ’01-ENERO’ WHEN 2 THEN ’02-FEBRERO’ WHEN 3 THEN ’03-MARZO’ WHEN 4 THEN ’04-ABRIL’ WHEN 5 THEN ’05-MAYO’ WHEN 6 THEN

                             ’06-JUNIO’ WHEN 7 THEN ’07-JULIO’ WHEN 8 THEN ’08-AGOSTO’ WHEN 9 THEN ’09-SEPTIEMBRE’ WHEN 10 THEN ’10-OCTUBRE’ WHEN 11 THEN ’11-NOVIEMBRE’ WHEN

                             12 THEN ’12-DICIEMBRE’ END AS MesNom, FECHA, Year, Mes, CardCode, CardName, SalPackMsr, SalPackUn, ItmsGrpCod, ItmsGrpNam, ItemCode, ItemName,

                            SUM(Quantity) AS Cantidad, SUM(LineTotal) AS Total, CASE WHEN SUM(Quantity) = 0 THEN 0 ELSE SUM(Quantity) / SalPackUn END AS Unidad_Venta

      FROM         (SELECT     ‘FACTURACION’ AS Tipo, T0.DocDate – (DAY(T0.DocDate) – 1) AS FECHA, YEAR(T0.DocDate) AS Year, MONTH(T0.DocDate) AS Mes, T3.CardCode,

                                                    T3.CardName, T2.SalPackMsr, T2.SalPackUn, T4.ItmsGrpCod, T4.ItmsGrpNam, T1.ItemCode, T2.ItemName, T1.Quantity * T1.NumPerMsr AS Quantity,

                                                    T1.LineTotal

                             FROM          dbo.OINV AS T0 INNER JOIN

                                                    dbo.INV1 AS T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER JOIN

                                                    dbo.OITM AS T2 ON T1.ItemCode = T2.ItemCode LEFT OUTER JOIN

                                                    dbo.OCRD AS T3 ON T2.CardCode = T3.CardCode LEFT OUTER JOIN

                                                    dbo.OITB AS T4 ON T2.ItmsGrpCod = T4.ItmsGrpCod

                             UNION ALL

                             SELECT     ‘FACTURACION’ AS Tipo, T0.DocDate – (DAY(T0.DocDate) – 1) AS FECHA, YEAR(T0.DocDate) AS Year, MONTH(T0.DocDate) AS Mes, T3.CardCode,

                                                   T3.CardName, T2.SalPackMsr, T2.SalPackUn, T4.ItmsGrpCod, T4.ItmsGrpNam, T1.ItemCode, T2.ItemName, – (T1.Quantity * T1.NumPerMsr) AS Quantity,

                                                   – T1.LineTotal AS Expr2

                             FROM         dbo.ORIN AS T0 INNER JOIN

                                                   dbo.RIN1 AS T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER JOIN

                                                   dbo.OITM AS T2 ON T1.ItemCode = T2.ItemCode LEFT OUTER JOIN

                                                   dbo.OCRD AS T3 ON T2.CardCode = T3.CardCode LEFT OUTER JOIN

                                                   dbo.OITB AS T4 ON T2.ItmsGrpCod = T4.ItmsGrpCod

                           

                             UNION ALL

                             SELECT     ‘DEVOLUCION’ AS Tipo, T0.DocDate – (DAY(T0.DocDate) – 1) AS FECHA, YEAR(T0.DocDate) AS Year, MONTH(T0.DocDate) AS Mes, T3.CardCode,

                                                   T3.CardName, T2.SalPackMsr, T2.SalPackUn, T4.ItmsGrpCod, T4.ItmsGrpNam, T1.ItemCode, T2.ItemName, – (T1.Quantity * T1.NumPerMsr) AS Quantity,

                                                   – T1.LineTotal AS Expr2

                             FROM         dbo.ORIN AS T0 INNER JOIN

                                                   dbo.RIN1 AS T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER JOIN

                                                   dbo.OITM AS T2 ON T1.ItemCode = T2.ItemCode LEFT OUTER JOIN

                                                   dbo.OCRD AS T3 ON T2.CardCode = T3.CardCode LEFT OUTER JOIN

                                                   dbo.OITB AS T4 ON T2.ItmsGrpCod = T4.ItmsGrpCod

                            

      GROUP BY Tipo, FECHA, Year, Mes, CardCode, CardName, SalPackMsr, SalPackUn, ItmsGrpCod, ItmsGrpNam, ItemCode, ItemName

      • Prueba con este

        SELECT     Tipo,

                              CASE Tz.Mes WHEN 1 THEN ’01-Ene’ WHEN 2 THEN ’02-Feb’ WHEN 3 THEN ’03-Mar’ WHEN 4 THEN ’04-Abr’ WHEN 5 THEN ’05-May’ WHEN 6 THEN ’06-Jun’ WHEN 7

                               THEN ’07-Jul’ WHEN 8 THEN ’08-Ago’ WHEN 9 THEN ’09-Sep’ WHEN 10 THEN ’10-Oct’ WHEN 11 THEN ’11-Nov’ WHEN 12 THEN ’12-Dic’ END + ‘-‘ + CAST(Year AS char)

                               AS MesNombre,

                              CASE Tz.Mes WHEN 1 THEN ’01-ENERO’ WHEN 2 THEN ’02-FEBRERO’ WHEN 3 THEN ’03-MARZO’ WHEN 4 THEN ’04-ABRIL’ WHEN 5 THEN ’05-MAYO’ WHEN 6 THEN

                               ’06-JUNIO’ WHEN 7 THEN ’07-JULIO’ WHEN 8 THEN ’08-AGOSTO’ WHEN 9 THEN ’09-SEPTIEMBRE’ WHEN 10 THEN ’10-OCTUBRE’ WHEN 11 THEN ’11-NOVIEMBRE’ WHEN

                               12 THEN ’12-DICIEMBRE’ END AS MesNom, FECHA, Year, Mes, CardCode, CardName, SalPackMsr, SalPackUn, ItmsGrpCod, ItmsGrpNam, ItemCode, ItemName,

                              SUM(Quantity) AS Cantidad, SUM(LineTotal) AS Total, CASE WHEN SUM(Quantity) = 0 THEN 0 ELSE SUM(Quantity) / SalPackUn END AS Unidad_Venta

        FROM         (SELECT     ‘FACTURACION’ AS Tipo, T0.DocDate – (DAY(T0.DocDate) – 1) AS FECHA, YEAR(T0.DocDate) AS Year, MONTH(T0.DocDate) AS Mes, T3.CardCode,

                                                      T3.CardName, T2.SalPackMsr, T2.SalPackUn, T4.ItmsGrpCod, T4.ItmsGrpNam, T1.ItemCode, T2.ItemName, T1.Quantity * T1.NumPerMsr AS Quantity,

                                                      T1.LineTotal

                               FROM          dbo.OINV AS T0 INNER JOIN

                                                      dbo.INV1 AS T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER JOIN

                                                      dbo.OITM AS T2 ON T1.ItemCode = T2.ItemCode LEFT OUTER JOIN

                                                      dbo.OCRD AS T3 ON T2.CardCode = T3.CardCode LEFT OUTER JOIN

                                                      dbo.OITB AS T4 ON T2.ItmsGrpCod = T4.ItmsGrpCod

                               UNION ALL

                               SELECT     ‘FACTURACION’ AS Tipo, T0.DocDate – (DAY(T0.DocDate) – 1) AS FECHA, YEAR(T0.DocDate) AS Year, MONTH(T0.DocDate) AS Mes, T3.CardCode,

                                                     T3.CardName, T2.SalPackMsr, T2.SalPackUn, T4.ItmsGrpCod, T4.ItmsGrpNam, T1.ItemCode, T2.ItemName, – (T1.Quantity * T1.NumPerMsr) AS Quantity,

                                                     – T1.LineTotal AS Expr2

                               FROM         dbo.ORIN AS T0 INNER JOIN

                                                     dbo.RIN1 AS T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER JOIN

                                                     dbo.OITM AS T2 ON T1.ItemCode = T2.ItemCode LEFT OUTER JOIN

                                                     dbo.OCRD AS T3 ON T2.CardCode = T3.CardCode LEFT OUTER JOIN

                                                     dbo.OITB AS T4 ON T2.ItmsGrpCod = T4.ItmsGrpCod

                              

                               UNION ALL

                               SELECT     ‘DEVOLUCION’ AS Tipo, T0.DocDate – (DAY(T0.DocDate) – 1) AS FECHA, YEAR(T0.DocDate) AS Year, MONTH(T0.DocDate) AS Mes, T3.CardCode,

                                                     T3.CardName, T2.SalPackMsr, T2.SalPackUn, T4.ItmsGrpCod, T4.ItmsGrpNam, T1.ItemCode, T2.ItemName, – (T1.Quantity * T1.NumPerMsr) AS Quantity,

                                                     – T1.LineTotal AS Expr2

                               FROM         dbo.ORIN AS T0 INNER JOIN

                                                     dbo.RIN1 AS T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER JOIN

                                                     dbo.OITM AS T2 ON T1.ItemCode = T2.ItemCode LEFT OUTER JOIN

                                                     dbo.OCRD AS T3 ON T2.CardCode = T3.CardCode LEFT OUTER JOIN

                                                     dbo.OITB AS T4 ON T2.ItmsGrpCod = T4.ItmsGrpCod) AS TZ

        GROUP BY Tipo, FECHA, Year, Mes, CardCode, CardName, SalPackMsr, SalPackUn, ItmsGrpCod, ItmsGrpNam, ItemCode, ItemName

  • les dejo las ventas por fecha y grupos y vendedor : ,Una manera no tan larga espero que les sea util

    SELECT T0.[DocNum], T0.[CardName], datename(month,T0.[DocDate]) Mes, T1.[ItemCode], T1.[Dscription], T1.[Price], T1.[Quantity], T1.[LineTotal], T1.[VendorNum], T1.[SWW], T2.[SlpName],T6.[ItmsGrpNam],T5.[U_Lin]

    FROM OINV T0 

    LEFT JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

    LEFT  JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

    INNER JOIN OITM T5 ON T1.ItemCode = T5.ItemCode

    INNER JOIN OITB T6 ON T5.ItmsGrpCod = T6.ItmsGrpCod

    WHERE T0.[DocDate] between [%0] and [%1] and T6.[ItmsGrpNam] LIKE  ‘[%2]%’ and T2.[SlpName] LIKE ‘[%3]%’

    • Hola Gervacio buen día realice la consulta pero me percate que trae el precio unitario del articulo y si este tiene algún gravamen (iva) no lo pone y no cuadra con la cantidad real de ventas me podrías apoyar de favor 

        • disculpa que no te habia respondido el primer correo pero como ya resolviste esta demas decirte que como son paises diferentes la moneda cambia y me alegra mucho saber que te ayudo el reporte cualquier otra cosa que nos podamos ayudar no dudes en decirlo, para eso estamos para darno mutuo apoyo