cancel
Showing results for 
Search instead for 
Did you mean: 

Ayuda con Query

Former Member
0 Kudos

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.

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member210784
Active Contributor
0 Kudos

Hola.

¿Podrías por favor colocar el query y explicar otro poco más para entender bien tu necesidad?.

Saludos cordiales.

Former Member
0 Kudos

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