Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Ventas Por Años, sin tabla temporales

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

Former Member
Former Member replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question