include_mysqlinfo

Ein häufiger Fall. Wir haben verschiedene Objekte. Und wir haben ein Stundenplan. Zudem haben wir die Reservationen. Nun wollen wir eine schöne Ansicht, wie die Objekte im Moment Belegt sind.

Ein typisches Beispiel sind Spielfelder in einer Tennis-/Squash-Plätze.

Als erste habe ich mal 3 Tabellen: objekte: Alle Plätze perioden: Alle perioden die es pro Tag gibt. Diese definieren sich über die Startzeit belegung: Belegung der Plätze.

[objekte]
id | desc
---|--------
1  | Platz 1
2  | Platz 2

[perioden]
startzeit (TIMESTAMP)
---------
08:00:00
08:30:00
09:00:00
09:30:00
10:00:00
10:30:00
11:00:00
11:30:00
12:00:00
12:30:00
13:00:00
13:30:00
14:00:00
14:30:00
15:00:00
15:30:00
16:00:00
16:30:00
17:00:00

[belegung]
id | object_id | user_id | anfangszeit (TIMESTAMP) | endzeit (TIMESTAMP)
---|-----------|---------|-------------------------|--------------------
 1 |         1 |      12 | 2011-03-30 09:30:00     | 2011-03-30 10:30:00
 2 |         1 |      11 | 2011-03-31 09:30:00     | 2011-03-31 11:00:00
 3 |         1 |      11 | 2011-03-29 16:30:00     | 2011-03-30 08:30:00
 4 |         1 |      13 | 2011-03-28 14:00:00     | 2011-03-28 14:00:00

Was ich jetzt haben will ist die Belegung aller Objekte auf einen Stichtag.

Als Beispiel: Heute ist der 30. März 2011. Dann sieht mit den obigen Daten die Ausgabe wie folgt aus

datum      | object_id | perioden_start | gebucht | user_id
2011-03-30 | 1         | 08:00:00       | belegt  | 11
2011-03-30 | 1         | 08:30:00       | belegt  | 11
2011-03-30 | 1         | 09:00:00       | frei    | 
2011-03-30 | 1         | 09:30:00       | belegt  | 12
2011-03-30 | 1         | 10:00:00       | belegt  | 12
2011-03-30 | 1         | 10:30:00       | belegt  | 12
2011-03-30 | 1         | 11:00:00       | frei    | 
2011-03-30 | 1         | 11:30:00       | frei    | 
2011-03-30 | 1         | 12:00:00       | frei    | 
2011-03-30 | 1         | 12:30:00       | frei    | 
2011-03-30 | 1         | 13:00:00       | frei    | 
2011-03-30 | 1         | 13:30:00       | frei    | 
2011-03-30 | 1         | 14:00:00       | frei    | 
2011-03-30 | 1         | 14:30:00       | frei    | 
2011-03-30 | 1         | 15:00:00       | frei    | 
2011-03-30 | 1         | 15:30:00       | frei    | 
2011-03-30 | 1         | 16:00:00       | frei    | 
2011-03-30 | 1         | 16:30:00       | frei    | 
2011-03-30 | 1         | 17:00:00       | frei    | 
2011-03-30 | 2         | 08:00:00       | frei    | 
2011-03-30 | 2         | 08:30:00       | frei    | 
2011-03-30 | 2         | 09:00:00       | frei    | 
2011-03-30 | 2         | 09:30:00       | frei    | 
2011-03-30 | 2         | 10:00:00       | frei    | 
2011-03-30 | 2         | 10:30:00       | frei    | 
2011-03-30 | 2         | 11:00:00       | frei    | 
2011-03-30 | 2         | 11:30:00       | frei    | 
2011-03-30 | 2         | 12:00:00       | frei    | 
2011-03-30 | 2         | 12:30:00       | frei    | 
2011-03-30 | 2         | 13:00:00       | frei    | 
2011-03-30 | 2         | 13:30:00       | frei    | 
2011-03-30 | 2         | 14:00:00       | frei    | 
2011-03-30 | 2         | 14:30:00       | frei    | 
2011-03-30 | 2         | 15:00:00       | frei    | 
2011-03-30 | 2         | 15:30:00       | frei    | 
2011-03-30 | 2         | 16:00:00       | frei    | 
2011-03-30 | 2         | 16:30:00       | frei    | 
2011-03-30 | 2         | 17:00:00       | frei    | 

Und so sieht eine mögliche Lösung aus:

SELECT
	datum,
	object_id,
	startzeit AS perioden_start,
	IF(SUM(state), 'belegt', 'frei') AS gebucht,
	MAX(user_id) AS user_id
FROM
	(
		SELECT
			var.datum,			
			per.startzeit,
			per.object_id,
			@state := TIMESTAMP(var.datum, per.startzeit) BETWEEN bel.anfangszeit AND bel.endzeit AS state,
			IF(@state, bel.user_id, NULL) AS user_id
		FROM
			-- hier kann man auch ein beliebiges anderes Datum (ohne Zeitstempfel) setzen
			(SELECT CURDATE() AS datum) AS var,
			(
				--Alle Perioden mit allen Objekten kombiniern
				SELECT
					startzeit,
					objekte.id AS object_id
				FROM
					perioden,
					objekte
			) AS per
			--Diese Kombination mit den Belegungen erweitern
			LEFT JOIN belegung AS bel
				ON bel.object_id = per.object_id
	) AS rohdaten
GROUP BY
	object_id,
	startzeit,
	datum

Das ganze ist in drei Schichten aufgebaut Im inneren beginne wir mit einer Kombination aller Perioden mit allen Objekten

[Perioden pro Objekt]
SELECT
	startzeit,
	objekte.id AS object_id
FROM
	perioden,
	objekte

Diese werden nun mittels eines LEFT-JOINS über die object_id mit den Belegungen kombiniert. Hier kommt auch unser Stich-Tag zum Einsatz. Im Subquery [var] wird der Stichtag im Format DATE ausgegeben. Dies hat den Vorteil, dass wir beim auswählen des Stichtages nur an einem einzigen Ort im SQ dies anpassen müssen. Zugleich prüfen wir, ob Periode innerhalb der Belegung ist und geben das als [state] aus. Falls das Objekt belegt ist, geben wir auch die user_id aus, damit wir gleich sehen durch wen.

[Rohdaten]
SELECT
	var.datum,			
	per.startzeit,
	per.object_id,
	-- TIMESTAMP(var.datum, per.startzeit): erstellt aus der startzeit und dem Datum ein TIMESTAMP
	@state := TIMESTAMP(var.datum, per.startzeit) BETWEEN bel.anfangszeit AND bel.endzeit AS state,
	IF(@state, bel.user_id, NULL) AS user_id
FROM
	--hier kann man auch ein beliebiges anderes Datum (Format DATE) setzen
	(SELECT CURDATE() AS datum) AS var,
	([Perioden pro Objekt]) AS per
	--Diese Kombination mit den Belegungen erweitern
	LEFT JOIN belegung AS bel
		ON bel.object_id = per.object_id

Dies erzeugt uns aber noch zu viele Datensätze. Und zwar ist das [Anzahl Perioden pro Objekt]*[Anzahl user_id pro Objekt] Also müssen wir das ganze noch zusammenfassen

SELECT
	datum,
	object_id,
	startzeit AS perioden_start,
	IF(SUM(state), 'belegt', 'frei') AS gebucht,
	MAX(user_id) AS user_id
FROM
	([Rohdaten]) AS rohdaten
GROUP BY
	object_id,
	startzeit,
	datum

Wenn man ganz genau weiss, wieviele Objekte man hat und sich diese nicht verändern, kann man auch eine Kreuztabelle erstellen, welche dann pro Periode eine Zeile hat in der jedes Objekt aufgelistet wird. Dazu müssen wir nur das äusserste SQL umschreiben

	datum,
   startzeit AS perioden_start,
   IF(SUM(state), 'belegt', 'frei') AS gebucht,
	IF(SUM(IF(object_id = 1, state, FALSE)), 'belegt', 'frei') AS gebucht_1,
	IF(SUM(IF(object_id = 2, state, FALSE)), 'belegt', 'frei') AS gebucht_2,
	IF(SUM(IF(object_id = 3, state, FALSE)), 'belegt', 'frei') AS gebucht_3
FROM
	([Rohdaten]) AS rohdaten
GROUP BY
	startzeit,
	datum;

Das Resultat sieht dann folgendermassen aus

datum      | perioden_start | gebucht_1 | gebucht_2 | gebucht_3
-----------|----------------|-----------|-----------|----------
2011-03-30 | 08:00:00       | belegt    | frei      | frei
2011-03-30 | 08:30:00       | belegt    | frei      | frei
2011-03-30 | 09:00:00       | frei      | frei      | frei
2011-03-30 | 09:30:00       | belegt    | frei      | frei
2011-03-30 | 10:00:00       | belegt    | frei      | frei
2011-03-30 | 10:30:00       | belegt    | frei      | frei
2011-03-30 | 11:00:00       | frei      | frei      | frei
2011-03-30 | 11:30:00       | frei      | frei      | frei
2011-03-30 | 12:00:00       | frei      | frei      | frei
2011-03-30 | 12:30:00       | frei      | frei      | frei
2011-03-30 | 13:00:00       | frei      | frei      | frei
2011-03-30 | 13:30:00       | frei      | frei      | frei
2011-03-30 | 14:00:00       | frei      | frei      | frei
2011-03-30 | 14:30:00       | frei      | frei      | frei
2011-03-30 | 15:00:00       | frei      | frei      | frei
2011-03-30 | 15:30:00       | frei      | frei      | frei
2011-03-30 | 16:00:00       | frei      | frei      | frei
2011-03-30 | 16:30:00       | frei      | frei      | frei
2011-03-30 | 17:00:00       | frei      | frei      | frei