Skip to Content
author's profile photo Former Member
Former Member

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    Posted on May 26, 2011 at 08:35 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.