Skip to Content
avatar image
Former Member

Ultimo precio de compra en Lista de Materiales

Hola que tal,

Tengo un query que me trae cierta información de un articulo padre(Lista de Materiales) con varios articulos hijo, pero quiero agregar una columna con el ultimo precio de compra de cada articulo padre, ese campo se encuentra en oitm (articulos) pero no encuentro la manera de traerlo y relacionarlo con el query actual, ¿alguien tiene una idea como? les pongo el query:

Select

T2.[WhsName],

T1.[ItemCode],

T1.[Dscription],

Sum(T1.[Quantity]) As [Cantidad],

T1.[Price],

Sum(T1.[GTotal]) As [Total],T3.U_Turno, T0.[DocTime] as [Creado]

From

ODLN T0 

Inner Join DLN1 T1 ON T0.DocEntry = T1.DocEntry

Inner Join OWHS T2 ON T1.WhsCode = T2.WhsCode

              left outer join [@CORTEPV]  T3 ON T0.U_NumCorte = T3.DocEntry

Where

T1.[TreeType] <> 'I' And (T0.[DocDate] >= [%0] And T0.DocDate <= [%1])

Group By

T2.[WhsName],T1.[ItemCode],T1.[Dscription],T1.[Price],T3.U_Turno,T0.[DocTime]

Muchas gracias de antemano, saludos!

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • avatar image
    Former Member
    Aug 23, 2012 at 10:53 PM

    Hola, aqui te lo dejo, saludos !

    Select T2.[WhsName],T1.[ItemCode],T1.[Dscription],Sum(T1.[Quantity]) As [Cantidad], T1.[Price],Sum(T1.[GTotal]) As [Total],T0.[DocTime] as [Creado],t4.[lastpurprc] From ODLN T0  Inner Join DLN1 T1 ON T0.DocEntry = T1.DocEntry Inner Join OWHS T2 ON T1.WhsCode = T2.WhsCode Inner Join OITM T4 ON T1.iTEMCODE = T4.ITEMCODE left outer join [@CORTEPV]  T3 ON T0.U_NumCorte = T3.DocEntry Where T1.[TreeType] <> 'I' And (T0.[DocDate] >= [%0] And T0.DocDate <= [%1]) Group By T2.[WhsName],T1.[ItemCode],T1.[Dscription],T1.[Price],T3.U_Turno,T0.[DocTime], t4.[lastpurprc]

    saludos !!!

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Ya pude resolverlo, quedó así:

      Select   

      T2.[WhsName], 

      T1.[ItemCode], 

      T1.[Dscription], 

      Sum(T1.[Quantity]) As [Cantidad],  

      T1.[Price], 

      Sum(T1.[GTotal]) As [Total],T3.U_turno, T0.[DocTime] as [Creado],

      CASE WHEN(T1.ItemCode IN (SELECT CODE FROM OITT)) THEN

      (Sum(T1.[Quantity])*(SELECT SUM(ROUND(B.LastPurPrc*A.Quantity,2))

      FROM ITT1 A RIGHT JOIN OITM B ON A.Code = B.ItemCode

      WHERE A.Father = T1.ItemCode)) ELSE

      (Sum(T1.[Quantity])*T4.LastPurPrc) END AS [Ultimo Precio de Compra]

      From   

      ODLN T0    

      Inner Join DLN1 T1 ON T0.DocEntry = T1.DocEntry   

      Inner Join OWHS T2 ON T1.WhsCode = T2.WhsCode   

      Inner Join OITM T4 ON T1.iTEMCODE = T4.ITEMCODE   

      LEFT JOIN ITT1 T5 ON T4.ItemCode = T5.Code

      left outer join [@CORTEPV]  T3 ON T0.U_NumCorte = T3.DocEntry

        

      Where   

      T1.[TreeType] <> 'I' And (T0.[DocDate] >= [%0] And T0.DocDate <= [%1])   

      Group By   

      T2.[WhsName],T1.[ItemCode],T1.[Dscription],T1.[Price],T3.U_Turno,T0.[DocTime], t4.[lastpurprc]

      Muchas gracias!