Ventas netas acumuladas por vendedor y por mes

[Former Member]

Estimados,

Tengo el siguiente Query que me genera las ventas netas por Vendedor, acumulado para un rango de fechas; la necesidad que tengo es poder agregarle que me muestre la informacion acumulada por cada mes segun la fecha de los movimientos Ej.

Si genero el informe del 01-01-2011 al 30-06-2011 el resultado esperado seria asi :

MES 01 MES 02 MES 03 MES 04 MES 05 MES 06 TOT. ACUMULADO

VENDEDOR 01 XXXXX XXXXX XXXXX XXXXX XXXXX XXXXX XXXXX

VENDEDOR 02 XXXXX XXXXX XXXXX XXXXX XXXXX XXXXX XXXXX

VENDEDOR 03 XXXXX XXXXX XXXXX XXXXX XXXXX XXXXX XXXXX

Agradezco la ayuda.

JAMS

[Former Member · Accepted solution]

y no tienes en cuenta las notas credito??

[felipe_loyolarodriguez · Accepted solution]

Sucede que solo publique un ejemplo de como deberia quedar el query y como Jairo no compartio su codigo.

Pero si lo que quieres es el query completo...

SELECT T0.SlpCode, T0.SlpName,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 1 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 1 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode),0)'Enero',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 2 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 2 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode),0)'Febrero',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 3 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 3 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode),0)'Marzo',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 4 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 4 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode),0)'Abril',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 5 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 5 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode),0)'Mayo',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 6 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 6 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode),0)'Junio',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 7 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 7 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode),0)'Julio',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 8 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 8 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode),0)'Agosto',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 9 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 9 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode),0)'Septiembre',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 10 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 10 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode),0)'Octubre',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 11 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 11 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode),0)'Noviembre',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 12 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 12 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode),0)'Diciembre',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode),0)'Acumulado 2011'
FROM OSLP T0
ORDER BY T0.SlpCode

Atte.

Edited by: Floyola on Jul 18, 2011 11:44 AM

Edited by: Floyola on Jul 18, 2011 11:49 AM

[Former Member · Accepted solution]

sorry, olvide colocar el codigo del query(ventas netas por vendedor, excluye unas condciones de pago) que tengo actualmente y al que necesito agregar codigo para que me presente la informacion clasificada por mes segun el rango de fecha capturado para generarse.

DECLARE @Ventas TABLE ( Vendedor varchar(100)

, Vlr_venta numeric(16,2)

)

Insert Into @Ventas

SELECT T2.[SlpName], SUM(T0.[DocTotal] - T0.[VatSum])'VLR FACTURAS'

FROM [dbo].[OINV] T0 INNER JOIN [dbo].[OCTG] T1 ON T0.GroupNum = T1.GroupNum INNER JOIN [dbo].[OSLP] T2 ON T0.SlpCode = T2.SlpCode

WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND (T1.[GroupNum] <> -1 AND T1.[GroupNum] <> 6)

GROUP BY T2.[SlpName]

Insert Into @Ventas

SELECT T2.[SlpName], -SUM(T0.[DocTotal] - T0.[VatSum])'VLR NOTAS'

FROM ORIN T0 INNER JOIN OCTG T1 ON T0.GroupNum = T1.GroupNum INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode

WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1] AND (T1.[GroupNum] <> -1 AND T1.[GroupNum] <> 6)

GROUP BY T2.[SlpName]

Select Vendedor 'Asesor Comercial', SUM(Vlr_Venta)'Ventas Netas'

FROM @Ventas

GROUP BY Vendedor

gracias.

JAMS

[Former Member · Accepted solution]

Estimado,

Al ultimo query que publicaste como le excluyo las condiciones de pago -1 y 6 y defino rango para fechas?? WHERE T0.DocDate >=%0 AND T0.DocDate <=%1 AND (T1.GroupNum <> -1 AND T1.GroupNum 6)

gracias.

[felipe_loyolarodriguez · Accepted solution]

Pruebala asi, aunque estaba trabajando en una funcion PIVOT para hacer la sumatoria, cuando termine igual la publicare si finciona

DECLARE @Fechas DATETIME, @FechaINI DATETIME, @FechaFin DATETIME
SET @Fechas = (SELECT TOP 1 A.DocEntry FROM OINV A WHERE A.DocDate >= '[%0]' AND A.DocDate <= '[%1]')
SET @FechaIni = '[%0]'
SET @FechaFin = '[%1]'

SELECT T0.SlpCode, T0.SlpName,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 1 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode AND T.GroupNum NOT IN ('6','-1')),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 1 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode AND Y.GroupNum NOT IN ('6','-1')),0)'Enero',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 2 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode AND T.GroupNum NOT IN ('6','-1')),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 2 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode AND Y.GroupNum NOT IN ('6','-1')),0)'Febrero',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 3 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode AND T.GroupNum NOT IN ('6','-1')),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 3 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode AND Y.GroupNum NOT IN ('6','-1')),0)'Marzo',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 4 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode AND T.GroupNum NOT IN ('6','-1')),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 4 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode AND Y.GroupNum NOT IN ('6','-1')),0)'Abril',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 5 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode AND T.GroupNum NOT IN ('6','-1')),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 5 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode AND Y.GroupNum NOT IN ('6','-1')),0)'Mayo',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 6 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode AND T.GroupNum NOT IN ('6','-1')),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 6 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode AND Y.GroupNum NOT IN ('6','-1')),0)'Junio',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 7 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode AND T.GroupNum NOT IN ('6','-1')),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 7 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode AND Y.GroupNum NOT IN ('6','-1')),0)'julio',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 8 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode AND T.GroupNum NOT IN ('6','-1')),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 8 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode AND Y.GroupNum NOT IN ('6','-1')),0)'Agosto',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 9 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode AND T.GroupNum NOT IN ('6','-1')),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE MONTH(Y.DocDate) = 9 AND YEAR(Y.DocDate)=2011 AND Y.SlpCode = T0.SlpCode AND Y.GroupNum NOT IN ('6','-1')),0)'Septiembre',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE T.DocDate >= @FechaIni AND T.DocDate <= @FechaFin AND T.SlpCode = T0.SlpCode AND T.GroupNum NOT IN ('6','-1')),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.Vatsum) FROM ORIN Y WHERE Y.DocDate >= @FechaIni AND Y.DocDate <= @FechaFin AND Y.SlpCode = T0.SlpCode AND Y.GroupNum NOT IN ('6','-1')),0) 'Acumulado'
FROM OSLP T0
ORDER BY T0.SlpCode

Slds

Habra que agregar mas meses....no cabe el query

Edited by: Floyola on Jul 18, 2011 4:38 PM

[Former Member · Accepted solution]

Estimado,

Ya me funciona tal como lo necesito, solucionado ¡

Muchas gracias.

JAMS

[felipe_loyolarodriguez]

Te servira algo asi? solo deberias completar hacia abajo

SELECT T0.SlpCode, T0.SlpName,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 1 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)'Enero',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 2 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)'Febrero'
FROM OSLP T0

Saludos

Edited by: Floyola on Jul 18, 2011 10:49 AM

-

---

Deberia quedar asi

SELECT T0.SlpCode, T0.SlpName,
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 1 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)'Enero',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 2 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)'Febrero',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 3 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)'Marzo',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 4 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)'Abril',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 5 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)'Mayo',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 6 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)'Junio',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 7 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)'Julio',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 8 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)'Agosto',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 9 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)'Septiembre',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 10 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)'Octubre',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 11 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)'Noviembre',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE MONTH(T.DocDate) = 12 AND YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)'Diciembre',
ISNULL((SELECT SUM(T.DocTotal-T.Vatsum) FROM OINV T WHERE YEAR(T.DocDate)=2011 AND T.SlpCode = T0.SlpCode),0)'Acumulado 2011'
FROM OSLP T0
ORDER BY T0.SlpCode

Sldos

Edited by: Floyola on Jul 18, 2011 10:54 AM