Ventas Por Años, sin tabla temporales

[Former Member]

Hola a todos,

He realizo una query que me muestra las ventas por año, en las cual ya me tiene descontadas las NC de esas ventas, la query me funciona muy bien, pero esta query la necesito para montarla en Crystal Report y me pone mucho problema con las tablas temporales, como puedo manejar las Tablas temporales en en Crystal, o como hago este informe sin útilizar las tablas temporales,

Yo intente hacerlo con subconsultas pero no me dio.

Este es la query

DECLARE @ANO INT
DECLARE @ANO1 INT
DECLARE @ANO2 INT

SET @ANO=2011
SET @ANO1={?Ano}-1 --'2010'
SET @ANO2={?Ano}-2 --'2009'
--DROP TABLE #Ventas1
CREATE TABLE #Ventas1
(       [DocDate][numeric](19) NULL,
        [Neto][numeric](19, 6) NULL,
)
--2010
INSERT INTO #Ventas1
--2009
SELECT MONTH(P1.DocDate),SUM(P1.Neto)
FROM (
--Devolución artículo
SELECT  T0.DocDate,T0.[DocNum],T1.[Price], (T1.[LineTotal]+T1.[VatSum])*-1as Neto
FROM ORDN T0  INNER JOIN RDN1 T1 ON T0.DocEntry = T1.DocEntry
WHERE   T1.[OcrCode]='{?CentCost}' AND YEAR(T0.DocDate)=@ANO2--'2009'-- BETWEEN '04/01/2011' AND '04/30/2011'
UNION
--Factura de venta artículo
SELECT  T0.DocDate,T0.[DocNum], T1.[Price],(T1.[LineTotal]+T1.[VatSum])as Neto
FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.[OcrCode]='{?CentCost}' AND YEAR(T0.DocDate)=@ANO2--'2009'-- BETWEEN '04/01/2011' AND '04/30/2011'
UNION
--Notas crédito artículo
SELECT  T0.DocDate,T0.[DocNum],T1.[Price], (T1.[LineTotal]+T1.[VatSum])*-1as Neto
FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
WHERE   T1.[OcrCode]='{?CentCost}' AND YEAR(T0.DocDate)=@ANO2) P1 
GROUP BY MONTH(P1.DocDate)
ORDER BY MONTH(P1.DocDate)


--DROP TABLE #Ventas2
CREATE TABLE #Ventas2
(       [DocDate][numeric](19) NULL,
        [Neto][numeric](19, 6) NULL,
)
--2010
INSERT INTO #Ventas2
SELECT MONTH(P1.DocDate),SUM(P1.Neto)
FROM (
--Devolución artículo
SELECT  T0.DocDate,T0.[DocNum],T1.[Price], (T1.[LineTotal]+T1.[VatSum])*-1as Neto
FROM ORDN T0  INNER JOIN RDN1 T1 ON T0.DocEntry = T1.DocEntry
WHERE   T1.[OcrCode]='{?CentCost}' AND YEAR(T0.DocDate)=@ANO1--'2010'-- BETWEEN '04/01/2011' AND '04/30/2011'
UNION
--Factura de venta artículo
SELECT  T0.DocDate,T0.[DocNum], T1.[Price],(T1.[LineTotal]+T1.[VatSum])as Neto
FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.[OcrCode]='{?CentCost}' AND YEAR(T0.DocDate)=@ANO1--'2010'-- BETWEEN '04/01/2011' AND '04/30/2011'
UNION
--Notas crédito artículo
SELECT  T0.DocDate,T0.[DocNum],T1.[Price], (T1.[LineTotal]+T1.[VatSum])*-1as Neto
FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
WHERE   T1.[OcrCode]='{?CentCost}' AND YEAR(T0.DocDate)=@ANO1) P1 
GROUP BY MONTH(P1.DocDate)
ORDER BY MONTH(P1.DocDate)

--DROP TABLE #Ventas3
CREATE TABLE #Ventas3
(       [DocDate][numeric](19) NULL,
        [Neto][numeric](19, 6) NULL,
)
INSERT INTO #Ventas3
--2011
SELECT MONTH(P1.DocDate),SUM(P1.Neto)
FROM (
--Devolución artículo
SELECT  T0.DocDate,T0.[DocNum],T1.[Price], (T1.[LineTotal]+T1.[VatSum])*-1as Neto
FROM ORDN T0  INNER JOIN RDN1 T1 ON T0.DocEntry = T1.DocEntry
WHERE   T1.[OcrCode]='{?CentCost}' AND YEAR(T0.DocDate)={?Ano}--'2011'-- BETWEEN '04/01/2011' AND '04/30/2011'
UNION
--Factura de venta artículo
SELECT  T0.DocDate,T0.[DocNum], T1.[Price],(T1.[LineTotal]+T1.[VatSum])as Neto
FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.[OcrCode]='{?CentCost}' AND YEAR(T0.DocDate)={?Ano}--'2011'-- BETWEEN '04/01/2011' AND '04/30/2011'
UNION
--Notas crédito artículo
SELECT  T0.DocDate,T0.[DocNum],T1.[Price], (T1.[LineTotal]+T1.[VatSum])*-1as Neto
FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
WHERE   T1.[OcrCode]='{?CentCost}' AND YEAR(T0.DocDate)={?Ano}) P1 
GROUP BY MONTH(P1.DocDate)
ORDER BY MONTH(P1.DocDate)


SELECT T0.DocDate,T2.Neto'2009',T0.Neto'2010',T1.Neto'2011'
FROM #Ventas2 T0 LEFT JOIN #Ventas3 T1 ON T0.DocDate=T1.DocDate 
                 LEFT JOIN #Ventas1 T2 ON T1.DocDate=T2.DocDate

JUAN CAMILO GOMEZ

[felipe_loyolarodriguez · Accepted solution]

Yo habia generado algo mas simple

SELECT DISTINCT MONTH(T0.DocDate)'Mes',
(ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2010 AND X.DocType = 'I'),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2010 AND Y.DocType = 'I'),0))'Ventas Año Pasado',
(ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2011 AND X.DocType = 'I'),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2011 AND Y.DocType = 'I'),0))'Ventas Año Actual',
CASE
WHEN (ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2011 AND X.DocType = 'I'),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2011 AND Y.DocType = 'I'),0)) >= (ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2010 AND X.DocType = 'I'),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2010 AND Y.DocType = 'I'),0))
THEN ((ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2011 AND X.DocType = 'I'),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2011 AND Y.DocType = 'I'),0))/(ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2010 AND X.DocType = 'I'),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2010 AND Y.DocType = 'I'),0)))*100
WHEN (ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2011 AND X.DocType = 'I'),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2011 AND Y.DocType = 'I'),0)) < (ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2010 AND X.DocType = 'I'),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2010 AND Y.DocType = 'I'),0))
THEN ((ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2011 AND X.DocType = 'I'),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2011 AND Y.DocType = 'I'),0))/(ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2010 AND X.DocType = 'I'),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2010 AND Y.DocType = 'I'),0)))*-100
END '%'
FROM OINV T0
ORDER BY MONTH(T0.DocDate)

Atte.

FLR

[Former Member · Accepted solution]

Gracias Felipe,

Pero el informe que tu me muestras no me descuenta las Devoluciones, y las Notas Crédito, para que así me muestre cual fue el valor real de las ventas.

JUAN CAMILO

[felipe_loyolarodriguez · Accepted solution]

Juan

El calculo hecho en el query es el siguiente

Neto Facturas (OINV.DocTotal-OINV.VatSum) - Neto de Notas de credito (ORIN.DocDocTota-ORIN.VatSum)

Lo unico que no reste fue el OINV/ORIN.TotalExpns porque no lo usamos aqui

Las devoluciones no entran dentro del calculo, porque no afectan la venta, al igual que las entregas.

Atte.

Edited by: Floyola on May 26, 2011 4:55 PM

[Former Member · Accepted solution]

Si lo de las devoluciones tienes toda la razón,

Ya me dio la query gracias

[felipe_loyolarodriguez · Accepted solution]

mmmm, quizas porque estaba tomando solo las facturas de articulos

SELECT DISTINCT MONTH(T0.DocDate)'Mes',
(ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2010),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2010),0))'Ventas Año Pasado',
(ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2011),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2011),0))'Ventas Año Actual',
CASE
WHEN (ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2011),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2011),0)) >= (ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2010),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2010),0))
THEN ((ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2011),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2011),0))/(ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2010),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2010),0)))*100
WHEN (ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2011),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2011),0)) < (ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2010),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2010),0))
THEN ((ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2011),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2011),0))/(ISNULL((SELECT SUM(X.DocTotal-X.VatSum) FROM OINV X WHERE MONTH(X.DocDate) = MONTH(T0.DocDate) AND YEAR(X.DocDate) = 2010),0)-ISNULL((SELECT SUM(Y.DocTotal-Y.VatSum) FROM ORIN Y WHERE MONTH(Y.DocDate) = MONTH(T0.DocDate) AND YEAR(Y.DocDate) = 2010),0)))*-100
END '%'
FROM OINV T0
ORDER BY MONTH(T0.DocDate)

Pruebala asi

Slds