Task 17 - SQL

SELECT
    ATOUR.TOURNUMMER AS "Tour number",
    MAX(ATASEND.VON_LAND || '/' || ATASEND.VON_ORT1 || '/' || ATASEND.VON_PLZ) KEEP (DENSE_RANK FIRST ORDER BY ATASEND.VONLADEDATUM) AS "First Full Address of Loading",
    MAX(ATASEND.NACH_LAND || '/' || ATASEND.NACH_ORT1 || '/' || ATASEND.NACH_PLZ) KEEP (DENSE_RANK LAST ORDER BY ATASEND.BISLIEFERDATUM) AS "Last Full Address of Unloading",
    MIN(ATASEND.VONLADEDATUM) AS "First loading date",
    MAX(ATASEND.BISLIEFERDATUM) AS "Last unloading date",
    NAME1 AS "Carrier Name",
    MAINLAND AS "Country of the carrier",
    SUM(BETRAG) AS "Tour Amount",
    CFDATETIME7 AS "Shipeo Pair Date"

FROM
    ATOUR
JOIN
    ATASEND ON ATOUR.NR = ATASEND.TOURNR
JOIN
    SKUNDEN ON SKUNDEN.NR = ATOUR.UNTERNEHMER
JOIN
    ASDGABRECHNUNG ON ATASEND.NR = ASDGABRECHNUNG.ATAID
JOIN
    atourcustomfieldv ON atourcustomfieldv.id = atour.nr

WHERE
    ATOUR.TOURNUMMER = 437883

GROUP BY
    ATOUR.TOURNUMMER,
    SKUNDEN.NAME1,
    SKUNDEN.MAINLAND,
    CFDATETIME7
HAVING
    MIN(ATASEND.VONLADEDATUM) >= TO_DATE('01-03-2024', 'DD-MM-YYYY');