Ayuda con Query

[Former Member]

Tengo este query que funciona sin la parte del DECLARE pero necesito el costo del articulo unido a los datos.

Alguien me puede echar la mano con esto. (la parte del declare es tomado de un post de GordonDuran y funciona con algunas cosas mas, yo lo modfique aqui para ver si lo tomaba con mi query)

Declare @costox numeric

SET @costox = SELECT MAX(Price)  FROM PCH1 WHERE ItemCode=T4.ItemCode AND DocDate= MAX(T3.DocDate)

SELECT DISTINCT T3.[CardName] AS Cliente,

T4.ItemCode as Articulo,

T4.[Dscription] as Descripcion,

CASE WHEN T4.Currency='USD' THEN 'USD' WHEN T4.Currency='xGF' THEN 'USD-C' WHEN T4.Currency='$' THEN 'MXP' END as MonedaLinea,

T4.Rate as 'TC-Linea',

T4.LineTotal as TotalLineaMXP,

T4.[TotalFrgn] as TotalLineaUSD,

T4.[GrssProfit] as GananciaMXP,

T4.[GrssProfFC] as GananciaUSD,

@costox,

CASE WHEN T0.[DocCurr]='USD' THEN 'USD' ELSE 'MXP' END as MonedaPago,

T2.DocRate as 'TC-Pago',

T2.[SumApplied] AS PagoMXP,

T2.[AppliedFC] as PagoUSD,

T3.[DocNum] AS Factura,

T3.[DocDate] AS FechaFactura,

T0.[DocNum] AS NumPago,

T0.[DocDate] AS FechaPago,

DATEDIFF(day, T3.docdate, getdate()) -  DATEDIFF(day, T0.docdate, getdate()) as Dias,

T5.[SlpName] AS Vendedor

FROM ORCT T0 LEFT JOIN RCT1 T1 ON T0.DocEntry = T1.DocNum

INNER JOIN RCT2 T2 ON T0.DocEntry = T2.DocNum

INNER JOIN OINV T3 ON T2.DocEntry = T3.DocEntry

INNER JOIN INV1 T4 ON T3.DocEntry = T4.DocEntry 

INNER JOIN OSLP T5 ON T3.SlpCode = T5.SlpCode

WHERE T0.[DocDate] >='[%0]' AND  T0.[DocDate] <='[%1]'

AND T0.[JrnlMemo] <> 'Cancelado'

AND T2.InvType <> '14'

[gerardo_mendez · Accepted solution]

Hola Carlos.

Si lo que necesitas es el costo del artículo no veo la necesidad de una variable, puedes hacerlo con un Select anidado (resalto en negritas y subrayado):

SELECT DISTINCT T3.[CardName] AS Cliente,

T4.ItemCode as Articulo,

T4.[Dscription] as Descripcion,

CASE WHEN T4.Currency='USD' THEN 'USD' WHEN T4.Currency='xGF' THEN 'USD-C' WHEN T4.Currency='$' THEN 'MXP' END as MonedaLinea,

T4.Rate as 'TC-Linea',

T4.LineTotal as TotalLineaMXP,

T4.[TotalFrgn] as TotalLineaUSD,

T4.[GrssProfit] as GananciaMXP,

T4.[GrssProfFC] as GananciaUSD,

(Select TA.AvgPrice FROM OITM TA Where TA.ItemCode = T4.ItemCode) as Costox,

CASE WHEN T0.[DocCurr]='USD' THEN 'USD' ELSE 'MXP' END as MonedaPago,

T2.DocRate as 'TC-Pago',

T2.[SumApplied] AS PagoMXP,

T2.[AppliedFC] as PagoUSD,

T3.[DocNum] AS Factura,

T3.[DocDate] AS FechaFactura,

T0.[DocNum] AS NumPago,

T0.[DocDate] AS FechaPago,

DATEDIFF(day, T3.docdate, getdate()) -  DATEDIFF(day, T0.docdate, getdate()) as Dias,

T5.[SlpName] AS Vendedor

FROM ORCT T0 LEFT JOIN RCT1 T1 ON T0.DocEntry = T1.DocNum

INNER JOIN RCT2 T2 ON T0.DocEntry = T2.DocNum

INNER JOIN OINV T3 ON T2.DocEntry = T3.DocEntry

INNER JOIN INV1 T4 ON T3.DocEntry = T4.DocEntry

INNER JOIN OSLP T5 ON T3.SlpCode = T5.SlpCode

WHERE T0.[DocDate] >= '[%0]' AND  T0.[DocDate] <='[%1]'

AND T0.[JrnlMemo] <> 'Cancelado'

AND T2.InvType <> '14'

Suerte.

[Former Member · Accepted solution]

No me trae informacion con el anidado, todo lo deja en cero, en el query que tome del portal se hacia un Max para tomar el ultimo valor promedio. En este caso no me da resultado.

[felipe_loyolarodriguez · Accepted solution]

Estimado, el costo del articulo se encuentra en la tabla INV1 campo StockPrice,

Bastaría solo con agregar "T4.StockPrice" a la consulta

Saludos

[Former Member · Accepted solution]

Muchas gracias por contestar, no habia visto ese campo.....al sacar el query ya me da iformacion, solo que no es el costo actual, este costo era en el momento de la operacion? Ya que no es el ultimo precio determinado ni el ultimo precio de compra, imagino que es el costo en ese momento de la operacion. ??

[felipe_loyolarodriguez · Accepted solution]

Correcto, es el costo del item al momento de venderlo.

Saludos

[Former Member · Accepted solution]

Muchas gracias por tu ayuda.