Skip to Content
0
Dec 01, 2016 at 02:53 AM

Query de Ventas agregar campo de la tabla OINM

507 Views

estimados todos una vez mas ando pidiendo de su gran y valiosa ayuda tengo un query para ventas pero me pidieron agregar el campo CalcPrice de la tabla OINM, al ejecutarlo tarda demasiado y me da información tergiversada, anexo mi consulta:

Set Language spanish

SELECT

T0.[CardCode] AS 'Código SN',

T0.[CardName] AS 'Socio de Negocio',

T0.[DocDate], T0.[DocDueDate],

T0.[CreateDate], DateName(m, T0.[CreateDate]) AS 'Mes', DateName(year, T0.[CreateDate]) AS 'Año',

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

T7.[CalcPrice] AS 'Costo',

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] AS '% De Descuento',

T0.[Comments],

T1.[County] AS 'Municipio',

T1.[State1] AS 'Estado',

T1.[ZipCode] AS 'Código Postal'

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

INNER JOIN OINM T7 ON T2.ItemCode = T7.ItemCode

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

Union All

SELECT

T0.[CardCode],

T0.[CardName],

T0.[DocDate],

T0.[DocDueDate],

T0.[CreateDate], DateName(m, T0.[CreateDate]) AS 'Mes', DateName(year, T0.[CreateDate]) AS 'Año',

'NC',

T0.[DocNum],

T1.[U_Sucursal],

T2.[ItemCode],

T2.[Dscription],

T2.[Quantity] * -1,

T2.[Price],

T2.[LineTotal] * -1,

T7.[CalcPrice],

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

T0.[Comments],

T1.[County],

T1.[State1],

T1.[ZipCode]

FROM [dbo].[ORIN] T0

INNER JOIN [dbo].[OCRD] T1 ON T0.CardCode = T1.CardCode

INNER JOIN [dbo].[RIN1] T2 ON T0.DocEntry = T2.DocEntry

INNER JOIN [dbo].[OITM] T3 ON T2.ItemCode = T3.ItemCode

INNER JOIN [dbo].[OSLP] T4 ON T0.SlpCode = T4.SlpCode

INNER JOIN [dbo].[OHEM] T5 ON T0.OwnerCode = T5.empID

INNER JOIN [dbo].[OCTG] T6 ON T0.GroupNum = T6.GroupNum

INNER JOIN [dbo].[OINM] T7 ON T2.ItemCode = T7.ItemCode

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

los campos están remarcados en negrita.

de antemano mil gracias por su gran ayuda.

Saludos,

Noé González