Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
Former Member
0 Kudos
411

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
Labels in this area