71 lines
1.2 KiB
Plaintext
71 lines
1.2 KiB
Plaintext
DECLARE @IDReise bigint =
|
|
{{ Termin.selectedOptionValue }};
|
|
|
|
SELECT
|
|
@IDReise AS AusgewaehlteIDReise,
|
|
|
|
R.Code AS Reisecode,
|
|
R.Bezeichnung AS Reise,
|
|
P.Bezeichnung AS Produkt,
|
|
R.TerminVon,
|
|
R.TerminBis,
|
|
|
|
COUNT(DISTINCT B.IDBuchung)
|
|
AS AnzahlBuchungen,
|
|
|
|
COUNT(BT.IDBuchungTN)
|
|
AS AnzahlTeilnehmende,
|
|
|
|
SUM(
|
|
CASE
|
|
WHEN BT.Status = N'F'
|
|
THEN 1
|
|
ELSE 0
|
|
END
|
|
) AS Status_F,
|
|
|
|
SUM(
|
|
CASE
|
|
WHEN BT.Status = N'O'
|
|
THEN 1
|
|
ELSE 0
|
|
END
|
|
) AS Status_O,
|
|
|
|
SUM(
|
|
CASE
|
|
WHEN COALESCE(BT.Status, N'') = N''
|
|
THEN 1
|
|
ELSE 0
|
|
END
|
|
) AS Status_Leer,
|
|
|
|
SUM(
|
|
CASE
|
|
WHEN BT.Status NOT IN (N'F', N'O')
|
|
AND COALESCE(BT.Status, N'') <> N''
|
|
THEN 1
|
|
ELSE 0
|
|
END
|
|
) AS Andere_Status
|
|
|
|
FROM dbo.Reise R
|
|
|
|
INNER JOIN dbo.Produkt P
|
|
ON P.IDProdukt = R.IDProdukt_FS
|
|
|
|
LEFT JOIN dbo.Buchung B
|
|
ON B.IDReise_FS = R.IDReise
|
|
|
|
LEFT JOIN dbo.BuchungTN BT
|
|
ON BT.IDBuchung_FS = B.IDBuchung
|
|
|
|
WHERE
|
|
R.IDReise = @IDReise
|
|
|
|
GROUP BY
|
|
R.Code,
|
|
R.Bezeichnung,
|
|
P.Bezeichnung,
|
|
R.TerminVon,
|
|
R.TerminBis; |