on 05-16-2012 9:41 PM
Jovenes expertos tengo el siguiente dilema, y no se si se pueda realizar tengo un query que me refleja las ordenes de compra y de venta que tienes status abierto, pero hay Ordenes de compra y de venta que se ingresan al sistema con fechas futuras por ejemplo enero 2013, me gustaria saber si hay alguna forma que crear un reporte que la columna coloque el año y el mes en automatica, si tuvieran una idea de como realizar esto se los agradeceria bastante.
Hola.
¿Podrías por favor colocar el query y explicar otro poco más para entender bien tu necesidad?.
Saludos cordiales.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Gracias hector por tu respuesta el Query es el siguiente:
SELECT DISTINCT T0.[ItemCode], T0.[ItemName], T0.SWW, T0.[OnHand],
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0)as 'Ene_OC',
isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0)as 'Ene_OV',
T0.[OnHand]-(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0)
as 'Total_Ene',
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '02' and Y.itemcode = T0.itemcode),0)as 'Feb_OC',
isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '02' and Y.itemcode = T0.itemcode),0)as 'Feb_OV',
T0.[OnHand]-(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0) -
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '02' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '02' and Y.itemcode = T0.itemcode),0)
as 'Total_Feb',
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '03' and Y.itemcode = T0.itemcode),0)as 'Mrz_OC',
isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '03' and Y.itemcode = T0.itemcode),0)as 'mrz_OV',
T0.[OnHand]-(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0) -
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '02' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '02' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '03' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '03' and Y.itemcode = T0.itemcode),0)
as 'Total_Mrz',
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '04' and Y.itemcode = T0.itemcode),0)as 'Abr_OC',
isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '04' and Y.itemcode = T0.itemcode),0)as 'Abr_OV',
T0.[OnHand]-(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0) -
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '02' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '02' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '03' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '03' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '04' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '04' and Y.itemcode = T0.itemcode),0)
as 'Total_Abr',
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '05' and Y.itemcode = T0.itemcode),0)as 'May_OC',
isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '05' and Y.itemcode = T0.itemcode),0)as 'May_OV',
T0.[OnHand]-(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0) -
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '02' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '02' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '03' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '03' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '04' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '04' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '05' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '05' and Y.itemcode = T0.itemcode),0)
as 'Total_May',
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '06' and Y.itemcode = T0.itemcode),0)as 'JN_OC',
isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '06' and Y.itemcode = T0.itemcode),0)as 'JN_OV',
T0.[OnHand]-(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0) -
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '02' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '02' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '03' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '03' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '04' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '04' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '05' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '05' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '06' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '06' and Y.itemcode = T0.itemcode),0)
as 'Total_JN',
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '07' and Y.itemcode = T0.itemcode),0)as 'JL_OC',
isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '07' and Y.itemcode = T0.itemcode),0)as 'JL_OV',
T0.[OnHand]-(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0) -
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '02' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '02' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '03' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '03' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '04' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '04' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '05' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '05' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '06' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '06' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '07' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '07' and Y.itemcode = T0.itemcode),0)
as 'Total_JL',
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '08' and Y.itemcode = T0.itemcode),0)as 'AG_OC',
isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '08' and Y.itemcode = T0.itemcode),0)as 'AG_OV',
T0.[OnHand]-(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '01' and Y.itemcode = T0.itemcode),0) -
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '02' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '02' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '03' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '03' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '04' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '04' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '05' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '05' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '06' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '06' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '07' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '07' and Y.itemcode = T0.itemcode),0)-
(isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '08' and Y.itemcode = T0.itemcode),0))+
isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '08' and Y.itemcode = T0.itemcode),0)
as 'Total_AG'
FROM OITM T0 INNER JOIN
POR1 T1 ON T0.ItemCode = T1.ItemCode INNER JOIN
RDR1 T2 ON T0.ItemCode = T2.ItemCode
Where T0.OnHand > '0'
Group BY T0.[ItemCode], T0.[ItemName], T0.SWW, T0.[OnHand], T1.[LineStatus], T2.[LineStatus],
T1.ItemCode,T2.ItemCode, T1.Quantity, T2.Quantity
lo que me muestra el query es:
Codigo Articulo Nombre del Articulo ID Adicional En stock Ene_OC Ene_OV
asi sucesivamente hasta diciembre, esto en el año 2012, pero en los meses de octubre a diciembre ya existen OC y OV para el año 2013 y desean que se refleje en una columna aparte ejemplo Ene_OC_2013 Ene_OV_2013 sucesivamente,
se puede realizar esto.
De antemano muy agradecido por su ayuda y tiempo.
Saludos
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.