Skip to Content
0

Query de Ventas agregar campo de la tabla OINM

Dec 01, 2016 at 02:53 AM

60

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
DIEGO LOTHER Dec 01, 2016 at 12:02 PM
1

Hola Noé,

Probar este:

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
	LEFT JOIN OINM T7 ON T2.ItemCode = T7.ItemCode AND T7.TransType = T0.ObjType
					  AND T7.DocLineNum = T2.LineNum AND T7.CreatedBy = T0.DocEntry
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
	LEFT JOIN OINM T7 ON T2.ItemCode = T7.ItemCode AND T7.TransType = T0.ObjType
					  AND T7.DocLineNum = T2.LineNum AND T7.CreatedBy = T0.DocEntry
WHERE 
	T0.[CreateDate] between [%0] and [%1]

Atte,

Diego Lother

Share
10 |10000 characters needed characters left characters exceeded
Noé González Dec 01, 2016 at 03:52 PM
0

Thank you so much Diego Lother.

Regards,

Noé González

Share
10 |10000 characters needed characters left characters exceeded