Skip to Content

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

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. rodolfo felix Frias

    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.

    (0) 
    1. Gustavo Adolfo Castro Torres 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  

      (0) 
    2. rodolfo felix Frias

      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

      (0) 
      1. Gustavo Adolfo Castro Torres 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

        (0) 
  2. GERVACIO VARGAS

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

    (0) 
    1. rodolfo felix Frias

      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 

      (0) 
        1. GERVACIO VARGAS

          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

          (0) 

Leave a Reply