AYUDA EN CONSULTA

[Former Member]

Saludos!!
tengo una consulta (CONSULTA 1) donde muestra las partidas abiertas en ordenes de compra por titular. es necesario agregar algunos campos como lo muestra la "CONSULTA 2" el problema es que esta repite las partidas, me gustaría que quedara como la consulta 1 con los campos de la consulta 2 ojala y me de a explicar por favor necesito de su valioso apoyo.

Gracias!!

CONSULTA 1:

SELECT T0.[DocNum], T0.[CardName], T2.ITEMCODE, T2.[Dscription], T2.[Quantity], T2.[OpenQty], T0.[submitted], T0.[DocCur], T2.[Price], T2.[OpenSum], T2.[OpenSumFC], T2.[ShipDate], (T1.[firstName]+' '+T1.[lastName])AS'Titular',T0.[DocDate]

FROM OPOR T0 LEFT JOIN OHEM T1 ON T0.[OwnerCode] = T1.[empID] left join POR1 T2 ON T0.DOCENTRY=T2.DOCENTRY

WHERE T0.[DocStatus] = 'O' AND T2.LINESTATUS= 'O'

CONSULTA 2:

SELECT T0.[DocNum], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[OpenQty], T2.[OnHand], (T3.[IsCommited])AS'Comprometido', T2.[OnOrder], T3.[OnOrder], T0.[DocCur], T1.[Price], T1.[OpenSum], T1.[OpenSumFC], T1.[ShipDate], (T0.[firstName]+' '+T0.[lastName])AS'Titular', T0.[DocDate]

FROM OPOR T0 LEFT JOIN OHEM T1 ON T0.[OwnerCode] = T1.[empID] left join POR1 T2 ON T0.DOCENTRY=T1.DOCENTRY

WHERE T0.[DocStatus] = 'O' AND  T1.[LineStatus] = 'O'

[former_member203638 · Accepted solution]

Intenta así:

SELECT DISTINCT 
T0.[DocNum], 
T0.[CardName], 
T2.[ItemCode], 
T2.[Dscription], 
T2.[Quantity], 
T2.[OpenQty], 
T3.[OnHand], 
(T3.[IsCommited])AS'Comprometido', 
T4.[OnOrder], 
T3.[OnOrder], 
T0.[DocCur], 
T2.[Price], 
T2.[OpenSum], 
T2.[OpenSumFC], 
T2.[ShipDate], 
(T1.[firstName]+' '+T1.[lastName])AS'Titular', 
T0.[DocDate]
FROM OPOR T0 
LEFT JOIN OHEM T1 ON T0.[OwnerCode] = T1.[empID] 
left join POR1 T2 ON T0.DOCENTRY=T2.DOCENTRY
INNER JOIN OITM T3 ON T2.ItemCode = T3.ItemCode 
INNER JOIN OITW T4 ON T3.ItemCode = T4.ItemCode
WHERE T0.[DocStatus] = 'O' AND  T2.[LineStatus] = 'O'

Saludos

Alessandro.

[Former Member · Accepted solution]

Muy buena Alessandro gracias!!

oye es posible que comience de la fecha mas reciente??

[former_member203638 · Accepted solution]

Hola César, con un order by, intenta así:

SELECT DISTINCT   
    T0.[DocNum],   
    T0.[CardName],   
    T2.[ItemCode],   
    T2.[Dscription],   
    T2.[Quantity],   
    T2.[OpenQty],   
    T3.[OnHand],   
    (T3.[IsCommited])AS'Comprometido',   
    T4.[OnOrder],   
    T3.[OnOrder],   
    T0.[DocCur],   
    T2.[Price],   
    T2.[OpenSum],   
    T2.[OpenSumFC],   
    T2.[ShipDate],   
    (T1.[firstName]+' '+T1.[lastName])AS'Titular',   
    T0.[DocDate]  
    FROM OPOR T0   
    LEFT JOIN OHEM T1 ON T0.[OwnerCode] = T1.[empID]   
    left join POR1 T2 ON T0.DOCENTRY=T2.DOCENTRY  
    INNER JOIN OITM T3 ON T2.ItemCode = T3.ItemCode   
    INNER JOIN OITW T4 ON T3.ItemCode = T4.ItemCode  
    WHERE T0.[DocStatus] = 'O' AND  T2.[LineStatus] = 'O'  
    Order by T0.DocDate DESC

Saludos

Alessandro.

[Former Member · Accepted solution]

Gracias de nuevo me funciono muy bien

salu2