DECLARE @IDReise bigint =
    {{ Termin.selectedOptionValue }};

SELECT
    BT.IDBuchungTN AS TeilnehmerID,
    B.Vorgang,
    AP.Name,
    AP.Vorname,

    COALESCE(
        NULLIF(E.Mailadresse, N''),
        N''
    ) AS [E-Mail],

    CONVERT(
        varchar(10),
        AP.Geburtsdatum,
        104
    ) AS [Geb.-Datum],

    COALESCE(
        W.Zus_Wunsch,
        N''
    ) AS Zus_Wunsch,

    COALESCE(
        LJSON.LeistungenJSON,
        N'[]'
    ) AS LeistungenJSON,

    COALESCE(
        SP.Skipassnummer,
        N''
    ) AS Skipassnummer

FROM dbo.Buchung B

INNER JOIN dbo.BuchungTN BT
    ON BT.IDBuchung_FS = B.IDBuchung

INNER JOIN dbo.AdressePerson AP
    ON AP.IDAdressePerson =
       BT.IDAdressePerson_FS

OUTER APPLY
(
    SELECT TOP (1)
        AK.Wert AS Mailadresse
    FROM dbo.AdresseKommunikation AK
    WHERE
        AK.IDAdressePerson_FS =
            AP.IDAdressePerson

        AND AK.Wert LIKE N'%@%'

    ORDER BY
        AK.IDAdresseKommunikation
) E

OUTER APPLY
(
    SELECT
        STRING_AGG(
            NULLIF(
                LTRIM(RTRIM(BTW.Text)),
                N''
            ),
            N' · '
        ) AS Zus_Wunsch

    FROM dbo.BuchungTNWunsch BTW

    WHERE
        BTW.IDBuchungTN_FS =
            BT.IDBuchungTN
) W

OUTER APPLY
(
    SELECT
        (
            SELECT
                X.Bezeichnung AS [name],
                X.Wert AS [value]

            FROM
            (
                SELECT DISTINCT
                    L.Bezeichnung,

                    CASE
                        WHEN NULLIF(
                            LTRIM(RTRIM(BTL.Nummer)),
                            N''
                        ) IS NOT NULL
                        THEN BTL.Nummer

                        ELSE N'1'
                    END AS Wert

                FROM dbo.BuchungTNLeistung BTL

                INNER JOIN dbo.Leistung L
                    ON L.IDLeistung =
                       BTL.IDLeistung_FS

                WHERE
                    BTL.IDBuchungTN_FS =
                        BT.IDBuchungTN

                    AND COALESCE(
                        L.Status,
                        N''
                    ) <> N'L'

                    AND EXISTS
                    (
                        SELECT 1

                        FROM dbo.BuchungTNLeistungPreis BTLP

                        INNER JOIN dbo.BuchungTNPreis BTP
                            ON BTP.IDBuchungTNPreis =
                               BTLP.IDBuchungTNPreis_FS

                        WHERE
                            BTLP.IDBuchungTNLeistung_FS =
                                BTL.IDBuchungTNLeistung

                            AND BTP.Status IN
                                (N'F', N'O')
                    )
            ) X

            ORDER BY
                X.Bezeichnung

            FOR JSON PATH
        ) AS LeistungenJSON
) LJSON

OUTER APPLY
(
    SELECT TOP (1)
        BTL.Nummer AS Skipassnummer

    FROM dbo.BuchungTNLeistung BTL

    INNER JOIN dbo.Leistung L
        ON L.IDLeistung =
           BTL.IDLeistung_FS

    WHERE
        BTL.IDBuchungTN_FS =
            BT.IDBuchungTN

        AND L.Bezeichnung LIKE N'%Skipass%'

        AND NULLIF(
            LTRIM(RTRIM(BTL.Nummer)),
            N''
        ) IS NOT NULL

        AND EXISTS
        (
            SELECT 1

            FROM dbo.BuchungTNLeistungPreis BTLP

            INNER JOIN dbo.BuchungTNPreis BTP
                ON BTP.IDBuchungTNPreis =
                   BTLP.IDBuchungTNPreis_FS

            WHERE
                BTLP.IDBuchungTNLeistung_FS =
                    BTL.IDBuchungTNLeistung

                AND BTP.Status IN
                    (N'F', N'O')
        )

    ORDER BY
        BTL.IDBuchungTNLeistung
) SP

WHERE
    B.IDReise_FS = @IDReise
    AND BT.Status = N'F'

ORDER BY
    B.Vorgang,
    AP.Name,
    AP.Vorname,
    BT.Position;