Task 23 - Joana Jablonska

SELECT

TO_CHAR(ATASEND.VONLADEDATUM, 'IW') AS "WEEK",
AKOPF.AUFTRAGSNUMMER AS "Order number",
SKUNDEN.NAME1 AS "Customer name",
SH.BEZEICHNUNG AS "Order organisation",
ATASEND.VONLADEDATUM AS "Loading target date",
S.LANDNAME AS "Country of Loading",
ATASEND.VONLIEFERDATUM AS "Unloading traget date",
SL.LANDNAME AS "Country of Unloading",
CASE
    WHEN ATASEND.LOCKSTATUS = 0 THEN 'FREE'
    WHEN ATASEND.LOCKSTATUS = 1 THEN 'IN PLANNING'
    WHEN ATASEND.LOCKSTATUS = 2 THEN 'PLANNED'
    WHEN ATASEND.LOCKSTATUS = 3 THEN 'PLANNED'
    WHEN ATASEND.LOCKSTATUS = 4 THEN 'FINISHED'
    ELSE NULL
END AS "Status",
SHI.BEZEICHNUNG AS "Planning organisation",
ATASEND.TANUMMER AS "TO number"

FROM
    ATASEND
LEFT JOIN
    ASENDUNG ON ASENDUNG.SENDUNGID = ATASEND.ASENDUNGID
LEFT JOIN
    AKOPF ON AKOPF.NR = ASENDUNG.AKOPFNR
LEFT JOIN
    SKUNDEN ON SKUNDEN.NR = AKOPF.FRACHTAGNR
LEFT JOIN
    SHIERARCHIE SHI ON SHI.ID = ATASEND.HIERARCHIE
LEFT JOIN
    SHIERARCHIE SH ON SH.ID = AKOPF.HIERARCHIE
LEFT JOIN
    SLAND S ON S.LAND = ATASEND.VON_LAND
LEFT JOIN
    SLAND SL ON SL.LAND = ATASEND.NACH_LAND

WHERE
    SHI.BEZEICHNUNG LIKE 'PL%'
    AND ATASEND.VONLADEDATUM >= TO_DATE('01-01-2024', 'dd-mm-yyyy')
    AND ATASEND.VONLADEDATUM <= TO_DATE('31-12-2024', 'dd-mm-yyyy')