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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
2 | |
2 | |
2 | |
2 | |
2 | |
2 | |
2 | |
2 |