Query en Reporte de Ventas

[former_member211460]

Buenas tardes , tengo 2 preguntas, la primera:

Como hago un query para que por ejemplo me arroje estos datos

Mes Ventas Año Pasado Ventas año Actual Porcentaje

1 $738,254 $605,975 -1792%

2 $681,520 $608,225 -1075%

3 $635,631 $534,272 -1595%

y asi sucesivamente los demas meses

y la seguna es es posible hacer los querys iguales a los de analisis de ventas y compras?

Gracias

[former_member211460 · Accepted solution]

Muchas Gracias Floyola

Mes sirvio mucho el query, solo tengo un par de preguntas, para que cuando la venta sea menor que el año anterior nos ponga el % en negativo donde ponemos el parametro y la otra para que el resultado arroje solo documentos de articulos normalemte la filtro asi

WHERE (DocType = 'I') donde lo pondria en el query

Y vel la informacion de los reportes

Gracias nuevamente

[felipe_loyolarodriguez · Accepted solution]

Hola denuevo

Bueno el doctype se deberia colocar dentro de las subquerys que hacen la suma, tanto en el OINV como ORIN

aqui te lo dejo

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)

Revisa si los porcentajes aparecen en negativo cuando corresponda

Slds

PD: No olvides puntuar la respuesta

Edited by: Floyola on Apr 6, 2011 11:42 AM

[Former Member · Accepted solution]

Encontre tu querie y me parece muy interesante. Una consulta si deseo que en lugar de que aparezca los meses como numero y que figure Enereo, Febrero, ... , Diciembre como lo podria colocar.

Segun lo que leo tu reporte suma todo lo facturado de la OINV (lo de las ordenes o ya la facturacion en si)

[felipe_loyolarodriguez]

Estimado, buenas noches

Aqui tienes un query que te muestra eso

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',
((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'%'
FROM OINV T0
ORDER BY MONTH(T0.DocDate)

Para el analisis de Ventas, sigue este link

Slds