Skip to Content
0
Dec 16, 2015 at 10:20 PM

Añadir mas tablas a consulta generada

23 Views

Estimados expertos espero se encuentren bien, otra vez pidiendo ayuda de su gran conocimiento, he generado una consulta la cual saca varios reportes pero ahora me pidieron agregar mas tablas para ser explicito de CRD1 los campos State, County y ZipCode y de OITM LstEvlPric como agregarlos en la consulta anexa:

SELECT T0.[CardCode] AS 'Código SN', T0.[CardName] AS 'Socio de Negocio', T0.[DocDate], T0.[CreateDate], 'FACTURA' as Documento, T0.[DocNum], T1.[U_Sucursal] AS 'Sucursal',

T2.[ItemCode] AS 'Código de Producto', T2.[Dscription] AS 'Nombre del Producto', T2.[Quantity] AS 'Cantidad', T2.[Price] AS 'Precio U', T2.[LineTotal], T3.[U_Factor] AS 'Factor de Conversión',

T4.[SlpName] AS 'Ruta', (T5.[firstName] +' '+ T5.[lastName])AS 'Nombre de Vendedor', T4.[U_SEGMENTO] AS 'Segmento', T3.[U_Grup] AS 'LOB',

T3.[U_Clasif] AS 'Clasificación', T3.[U_Marca] AS 'Marca', (T2.[Quantity] * T3.[U_Factor]) as Litros_Piezas, T2.[LineTotal] * (1-(T0.DiscPrcnt/100)) as 'Importe total',

T6.[PymntGroup] AS 'Condición de Pago', T0.[DiscPrcnt]

FROM OINV T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN INV1 T2 ON T0.DocEntry = T2.DocEntry INNER JOIN OITM T3 ON T2.ItemCode =

T3.ItemCode INNER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode INNER JOIN OHEM T5 ON T0.OwnerCode = T5.empID INNER JOIN OCTG T6 ON T0.GroupNum =

T6.GroupNum where T0.[CreateDate] between [%0] and [%1]

Union all

SELECT T0.[CardCode], T0.[CardName], T0.[DocDate], T0.[CreateDate], 'NC', T0.[DocNum], T1.[U_Sucursal], T2.[ItemCode], T2.[Dscription],

T2.[Quantity] * -1, T2.[Price], T2.[LineTotal] * -1, T3.[U_Factor], T4.[SlpName], (T5.[firstName] +' '+ T5.[lastName]), T4.[U_SEGMENTO], T3.[U_Grup], T3.[U_Clasif], T3.[U_Marca],

(T2.[Quantity] * T3.[U_Factor]) * -1 as Litros_Piezas, T2.[LineTotal] * -1 * (1-(T0.DiscPrcnt/100)) as 'Importe total', T6.[PymntGroup], T0.[DiscPrcnt]

FROM ORIN T0 INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN RIN1 T2 ON T0.DocEntry = T2.DocEntry INNER JOIN OITM T3 ON T2.ItemCode =

T3.ItemCode INNER JOIN OSLP T4 ON T0.SlpCode = T4.SlpCode INNER JOIN OHEM T5 ON T0.OwnerCode = T5.empID INNER JOIN OCTG T6 ON T0.GroupNum =

T6.GroupNum where T0.[CreateDate] between [%0] and [%1]

de antemano les agradezco de su gran ayuda.

Saludos.