15/03/2012, 14:41
|
| | | Fecha de Ingreso: agosto-2008
Mensajes: 17
Antigüedad: 16 años, 3 meses Puntos: 0 | |
 Calcular Promedio de 6 Meses
Buenas Tardes... Tengo el siguiente query alli se calcula el consumo mensual, pero necesito calcular el promedio de 6 mes de un periodo de fecha dado...
DECLARE @AÑO_ACTUAL SMALLINT
SET @AÑO_ACTUAL =2012
SELECT DISTINCT T0.ItemCode AS 'Código', T0.ItemName AS 'Descripción',
ISNULL((SELECT SUM(Y1.OutQty) FROM OINM Y1 WHERE Y1.ItemCode=T0.ITemCode AND YEAR(Y1.DocDate)>=@AÑO_ACTUAL AND MONTH(Y1.DocDate)<=1),0)'Enero',
ISNULL((SELECT SUM(Y1.OutQty) FROM OINM Y1 WHERE Y1.ItemCode=T0.ITemCode AND YEAR(Y1.DocDate)>=@AÑO_ACTUAL AND MONTH(Y1.DocDate)=2),0)'Febrero',
ISNULL((SELECT SUM(Y1.OutQty) FROM OINM Y1 WHERE Y1.ItemCode=T0.ITemCode AND YEAR(Y1.DocDate)>=@AÑO_ACTUAL AND MONTH(Y1.DocDate)=3),0)'Marzo',
ISNULL((SELECT SUM(Y1.OutQty) FROM OINM Y1 WHERE Y1.ItemCode=T0.ITemCode AND YEAR(Y1.DocDate)>=@AÑO_ACTUAL AND MONTH(Y1.DocDate)=4),0)'Abril',
ISNULL((SELECT SUM(Y1.OutQty) FROM OINM Y1 WHERE Y1.ItemCode=T0.ITemCode AND YEAR(Y1.DocDate)>=@AÑO_ACTUAL AND MONTH(Y1.DocDate)=5),0)'Mayo',
ISNULL((SELECT SUM(Y1.OutQty) FROM OINM Y1 WHERE Y1.ItemCode=T0.ITemCode AND YEAR(Y1.DocDate)>=@AÑO_ACTUAL AND MONTH(Y1.DocDate)=6),0)'Junio',
ISNULL((SELECT SUM(Y1.OutQty) FROM OINM Y1 WHERE Y1.ItemCode=T0.ITemCode AND YEAR(Y1.DocDate)>=@AÑO_ACTUAL AND MONTH(Y1.DocDate)=7),0)'Julio',
ISNULL((SELECT SUM(Y1.OutQty) FROM OINM Y1 WHERE Y1.ItemCode=T0.ITemCode AND YEAR(Y1.DocDate)>=@AÑO_ACTUAL AND MONTH(Y1.DocDate)=8),0)'Agosto',
ISNULL((SELECT SUM(Y1.OutQty) FROM OINM Y1 WHERE Y1.ItemCode=T0.ITemCode AND YEAR(Y1.DocDate)>=@AÑO_ACTUAL AND MONTH(Y1.DocDate)=9),0)'Septiembre',
ISNULL((SELECT SUM(Y1.OutQty) FROM OINM Y1 WHERE Y1.ItemCode=T0.ITemCode AND YEAR(Y1.DocDate)>=@AÑO_ACTUAL AND MONTH(Y1.DocDate)=10),0)'Octubre',
ISNULL((SELECT SUM(Y1.OutQty) FROM OINM Y1 WHERE Y1.ItemCode=T0.ITemCode AND YEAR(Y1.DocDate)>=@AÑO_ACTUAL AND MONTH(Y1.DocDate)=11),0)'Noviembre',
ISNULL((SELECT SUM(Y1.OutQty) FROM OINM Y1 WHERE Y1.ItemCode=T0.ITemCode AND YEAR(Y1.DocDate)>=@AÑO_ACTUAL AND MONTH(Y1.DocDate)=12),0)'Diciembre',
ISNULL((SELECT SUM(Y1.OutQty) FROM OINM Y1 WHERE Y1.ItemCode=T0.ITemCode AND YEAR(Y1.DocDate)=@AÑO_ACTUAL),0)'Total',
ISNULL((SELECT SUM(Y1.OutQty) FROM OINM Y1 WHERE Y1.ItemCode=T0.ITemCode AND YEAR(Y1.DocDate)=@AÑO_ACTUAL)/3,0)'Promedio 3M' ?---NECESITO SOLO SUMAR LOS ULTIMOS SEIS O TRES MESES Y SACAR EL PROMEDIO DIVIDIRLO ENTRE 3 O 6
FROM OITM T0
WHERE T0.DfltWH = '001'
ORDER BY T0.ItemCode |