I have a application that contains undelivered customer orders grouped by article no and warehouse.
I would like to join in the purchase order with the nearest delivery date and its confirmation type (D-Def, P- Prel, X- Not confirmed).
I have managed to get the lowest date but cant get the confirmation type.
Problem is that INKÖPSSTOCK2.QVD can contain to Purchas orders with the same OJBKLT. Then I want only the one with lowest OJDAT1 (created date).
OJBKLT has format YYYYWWD that converts to date YYYYMMDD in the end.
The result should be like:
Artno_warehouseno, Low_conf_date, Confimationtype
Ex: 100-01, 2009-12-08, D
Beräknadinleverans_tmp: LOAD Artnr&'-'&OJLST as Artno_warehouseno, Artnr as Articleno, OJLST as warehouseno, min(OJBKLT) as Low_conf_date FROM (qvd) group by Artnr, OJLST;
Left join (Beräknadinleverans_tmp) LOAD Artnr&'-'&OJLST as Artno_warehouseno, OJBKLT as Low_conf_date, Confirmationtype FROM (qvd);
Beräknadinleverans_tmp2: LOAD Artno_warehouseno, OJAID, OJLST, Low_conf_date, left(Low_conf_date,4) as Year, mid(Low_conf_date,5,2) as Vecka, Right(Low_conf_date,1) as Dag, Confirmationtype Resident Beräknadinleverans_tmp;
Drop table Beräknadinleverans_tmp;
Beräknadinleverans: LEFT JOIN (Restorders) LOAD Artno_warehouseno, OJAID, OJLST, MakeWeekDate(År, Vecka, Dag) as Low_conf_date, Confirmationtype Resident Beräknadinleverans_tmp2;
