cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Unir dos bases de datos

Former Member

on ‎04-04-2012 9:19 PM

0 Kudos

Jovenes expertos, tengo el siguiente problemita, les explico tengo un query que me refleja las OC y OV que esten abiertas en dos empresas distintas pero en el mismo servidor de SQL y SAP me solicitan que una este query y que se refleje en uno solo, se podra hacer esto y que sea visualizado desde SAP.

Disculpen las molestias y de antemano muy agradecido por la ayuda y su tiempo.

Show replies
Show replies
Answer

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member203638
Active Contributor
‎04-05-2012 6:24 PM
0 Kudos

Para unir informaciond e distintas bases,

SELECT DISTINCT A.CardCode, A.CardName

FROM [EMPRESA_AAA].[dbo].[OCRD]

UNION ALL

SELECT DISTINCT A.CardCode, A.CardName

FROM [EMPRESA_BBB].[dbo].[OCRD]

UNION ALL

SELECT DISTINCT A.CardCode, A.CardName

FROM [EMPRESA_CCC].[dbo].[OCRD]

Mira este link como referencia:

http://forums.sdn.sap.com/thread.jspa?threadID=2079013&tstart=0

Saludos.

Alessandro.

Show replies
Show replies
Former Member
‎04-06-2012 12:45 AM
0 Kudos

Gracias Alessandro voy a revisar el ejemplo que me das y voy a realizar pruebas, gracias por tu tiempo y ayuda.

former_member188471
Active Contributor
‎04-05-2012 6:18 PM
0 Kudos

Podrias poner tus querys e irme a algo mas real con tu consulta?

Show replies
Show replies
Former Member
‎04-06-2012 12:42 AM
0 Kudos

Gracias CAB CANUL JULIAN este es el query's en las dos bases de datos, es el mismo:

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',

        isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '09' and Y.itemcode = T0.itemcode),0)as 'SEP_OC',

        isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '09' and Y.itemcode = T0.itemcode),0)as 'SEP_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)-

                   (isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '09' and Y.itemcode = T0.itemcode),0))+

                   isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '09' and Y.itemcode = T0.itemcode),0)

                   as 'Total_SEP',

        isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '10' and Y.itemcode = T0.itemcode),0)as 'OCT_OC',

        isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '10' and Y.itemcode = T0.itemcode),0)as 'OCT_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)-

                   (isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '09' and Y.itemcode = T0.itemcode),0))+

                   isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '09' and Y.itemcode = T0.itemcode),0)-

                   (isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '10' and Y.itemcode = T0.itemcode),0))+

                   isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '10' and Y.itemcode = T0.itemcode),0)

                   as 'Total_OCT',

        isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '11' and Y.itemcode = T0.itemcode),0)as 'Nov_OC',

        isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '11' and Y.itemcode = T0.itemcode),0)as 'Nov_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)-

                   (isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '09' and Y.itemcode = T0.itemcode),0))+

                   isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '09' and Y.itemcode = T0.itemcode),0)-

                   (isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '10' and Y.itemcode = T0.itemcode),0))+

                   isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '10' and Y.itemcode = T0.itemcode),0)-

                   (isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '11' and Y.itemcode = T0.itemcode),0))+

                   isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '11' and Y.itemcode = T0.itemcode),0)

                   as 'Total_NOV',

        isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '12' and Y.itemcode = T0.itemcode),0)as 'Dic_OC',

        isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '12' and Y.itemcode = T0.itemcode),0)as 'Dic_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)-

                   (isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '09' and Y.itemcode = T0.itemcode),0))+

                   isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '09' and Y.itemcode = T0.itemcode),0)-

                   (isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '10' and Y.itemcode = T0.itemcode),0))+

                   isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '10' and Y.itemcode = T0.itemcode),0)-

                   (isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '11' and Y.itemcode = T0.itemcode),0))+

                   isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '11' and Y.itemcode = T0.itemcode),0)-

                   (isnull((Select sum(isnull(Y.Quantity,0)) FROM RDR1 Y WHERE Y.[LineStatus] ='O' and Month(y.[DocDate])= '12' and Y.itemcode = T0.itemcode),0))+

                   isnull((Select sum(isnull(Y.Quantity,0)) FROM POR1 Y WHERE Y.[LineStatus] ='O' and Month(Y.[DocDate])= '12' and Y.itemcode = T0.itemcode),0)

                   as 'Total_Dic'

    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

De antemano muy agradecido por la ayuda.

Ask a Question