Skip to Content
Author's profile photo Former Member

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

Assigned tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      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  

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Felix dime cual es el objetivo de tu informe para ayudarte

      Author's profile photo Former Member
      Former Member

      Hola Gustavo es tener un reporte comparativo entre años de las ventas por grupo de artículos saludos

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      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

      Author's profile photo Former Member
      Former Member

      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]%'

      Author's profile photo Former Member
      Former Member

      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 

      Author's profile photo Former Member
      Former Member

      Hola Gervacio ya modifique el query y me aparece correcta la información como la requiero muchas gracias por tu apoyo saludos

      Author's profile photo Former Member
      Former Member

      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