Ayuda con Query fechas de pedido e ingreso de pedido

[Former Member]

Hola, necesito una query que me muestre las fechas de realización de una orden de pedido como la fecha de ingreso del mismo pedido, uniendo ambas tablas para finalmente utilizar estos plazos para evaluar proveedores.
Los dejo con lo que he logrado hacer, pero me sigue tirando errores en los join.

SELECT T0.[DocNum], T1.[DocDate], T2.[DocNum], T3.[DocDate],

FROM OPOR T0 INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OPDN T2 ON T2.[DocEntry] = T1.[TrgetEntry]

INNER JOIN PDN1 T3 ON T2.[DocEntry] = T3.[DocEntry]

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

Espero encontrar alguna ayuda aca con los expertos.

Saludos,

Erich.

[zal_parchem2]

Hello Erich - here you go - try this:

SELECT DISTINCT T0.DocEntry, T1.DocEntry, T2.BaseEntry, T3.DocEntry, '' AS 'SQL on Forum Follows', T1.DocNum AS 'PO Numb', T1.DocDate AS 'PO Post Date', T3.DocNum AS 'Delivery Numb', T3.DocDate AS 'Delivery Post Date', T2.VisOrder + 1 AS 'Delivery Line Num', T2.ShipDate AS 'Line Delivery Date' FROM POR1 T0 LEFT OUTER JOIN OPOR T1 ON T0.DocEntry = T1.DocEntry LEFT OUTER JOIN PDN1 T2 ON T1.DocEntry = T2.BaseEntry AND T0.LineNum = T2.BaseLine LEFT OUTER JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry WHERE T1.DocDate> = '[% 0]' AND T1.DocDate <= '[% 1]'

Was not exactly sure what date you wanted (Delivery Line has two fields for date = DocDate and ShipDate - they are not always the same) but here are the results from the above SQL:

Notice that the SQL results you were looking for are on the right hand side of the screen print above and the "connection" is shown on the left hand side of the screen print. Did this as an example for you to think about.

There are three points important to this SQL. 1) All JOINs must be LEFT OUTER JOIN, 2) the arrangement of how the tables are JOINED. First POR1, then OPOR, then DLN1, and finally ODLN, and most important 3) OPOR.DocEntry = PDN1Base Entry and POR1.LineNum = PDN1.BaseLine gets you the lines above where PO Number 201993 was fullfilled by TWO Deliveries 625 and 626. Not sure if that happens at your Company.

You can remove some data from the SQL to get your report - remove SQL data fields T0.DocEntry to 'SQL on Forum Follows' and then run the SQL for your report with the remaining data fields.

Also see why I used VisOrder - LineNum or VisOrder - Which One to Use???

And combining two tables the same as on Relationship Map - Recreate Relationship Map

However, if I did not guess correctly on what you need, please leave a comment and we can work it out.

Please let me know if this is what you are looking for...

Regards,

Zal

[Former Member]

hola, gracias por la respuesta, pero sigue acusando un error, me imagino que el error debe estar en la unión de las tablas, pero lo lo logro decifrar aun.

[agustin_marcoscividanes]

Hola

prueba esta query:

SELECT T0.[DocNum], T1.[DocDate], T2.[DocNum], T3.[DocDate],

FROM OPOR T0 INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OPDN T2 ON T2.[DocEntry] = T3.[DocEntry]

INNER JOIN PDN1 T3 ON T1.[DocEntry] = T3.[BaseEntry]

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

Agustín