Abfrage um Lücken bestimmter Zeitdauer im Terminkalender zu finden

T

thunderstruck_SELECT

Benutzer
Beiträge
7
Folgendes Problem: Habe eine SQL-Datebank unter SQL-Server 2012 mit folgendem Aufbau:
Behandlungszimmer(int1-4) - Datum(charYYYYMMTT) --- Zeit(char hhmm )--- Dauer (int Minuten) ----- Name(char)
1-20231030-0900-45-Müller
1-20231030-1115-30-Maier
1-20231030-1200-15-Schulze

Hätte gerne eine Abfrage, die mir Lücken (z.B. > 30 Minuten) im Terminkalender für z.B. die nächsten 4 Wochen anzeigt, die ich dann per Hand zur Onlineterminierung freigeben könnte. Im o.g. Beispiel wäre das Kriterium gegeben, da Maier um 09:45h fertig sein sollte und der nächste Patient erst um 11:15h bestellt ist.
Nach Maier wäre keine entsprechend große Lücke, da er um 11:45h fertig wäre, dann hätte ich nur eine Lücke von 15 Minuten.
Hab auch schon ordentlich rumprobiert, aber klappt leider nicht. Hier einer meiner kläglichen Versuche.

SELECT a.Behandlungszimmer, a.Datum, a.Zeit, a.Dauer, b.Datum AS NextDatum, b.Zeit AS NextZeit, b.Dauer AS NextDauer
FROM TerminTabelle a
JOIN TerminTabelle b
ON a.Behandlungszimmer = b.Behandlungszimmer
AND a.Datum * 10000 + a.Zeit + a.Dauer * 100 <= (b.Datum * 10000 + b.Zeit)
AND (b.Datum * 10000 + b.Zeit) - (a.Datum * 10000 + a.Zeit + a.Dauer * 100) > 30
WHERE a.Datum <= b.Datum
AND (a.Datum < b.Datum OR a.Zeit + a.Dauer * 100 <= b.Zeit)
ORDER BY a.Behandlungszimmer, a.Datum, a.Zeit;

Wäre sehr dankbar für eine Lösung.
 
Werbung:
Ich mache das vielleicht immer ein wenig zu genau aber tu dir den Gefallen und arbeite lieber mit echten Datentypen für Datumzeit, das ist ja gruselig.

Hier mal ein Beispiel in drei Schritten:
Code: 
WITH t1 AS (
    SELECT    Behandlungszimmer,
            dateadd(minute,convert(INT,right(Zeit,2)),dateadd(hour,convert(INT,left(Zeit,2)),convert(DATETIME,Datum,112))) AS Datumzeit_von,
            Dauer,
            [Name]
    FROM    tabelle
    ), t2 AS (
    SELECT    ROW_NUMBER() OVER (PARTITION BY Behandlungszimmer ORDER BY Datumzeit_von) AS Zeile,
            *,
            dateadd(minute,Dauer,Datumzeit_von) AS Datumzeit_bis
    FROM    t1
    )
SELECT    t2.Behandlungszimmer,
        t2.Datumzeit_bis AS frei_von,
        t3.Datumzeit_von AS frei_bis
FROM    t2
LEFT JOIN t2 t3
ON        t2.Behandlungszimmer = t3.Behandlungszimmer
AND        t2.Zeile + 1 = t3.Zeile
WHERE    datediff(minute,t2.Datumzeit_bis,t3.Datumzeit_von) >= 30
OR        t3.Zeile IS NULL
1) Datumzeit_von ermitteln
2) Sortieren und Datumzeit_bis ermitteln
3) Selfjoin über die Sortierung und Freiräume ausgeben

Schritt 1 und 2 könnte man auch in einem gehen aber dann steht da sehr viel Konvertierung.
 
Hallo ukulele,
gerade mal bisschen rumgespielt und keine Fehler entdeckt. SPITZE !! Das hilft uns sehr.
Möchte nicht unverschämt sein, aber könnte man die Abfrage noch dahingehend ändern, dass immer mit aktuellem Datum für die nächsten 4 Wochen oder gar das ganze Quartal gesucht wird? Momentan werden mir noch Terminlücken ab 2003 angezeigt :)

Nochmals vielen vielen Dank
 
Ja eigentlich einfach, ich dachte das machst du selbst ;-)
Code: 
WITH t1 AS (
    SELECT    Behandlungszimmer,
            dateadd(minute,convert(INT,right(Zeit,2)),dateadd(hour,convert(INT,left(Zeit,2)),convert(DATETIME,Datum,112))) AS Datumzeit_von,
            Dauer,
            [Name]
    FROM    tabelle
    ), t2 AS (
    SELECT    ROW_NUMBER() OVER (PARTITION BY Behandlungszimmer ORDER BY Datumzeit_von) AS Zeile,
            *,
            dateadd(minute,Dauer,Datumzeit_von) AS Datumzeit_bis
    FROM    t1
    )
SELECT    t2.Behandlungszimmer,
        t2.Datumzeit_bis AS frei_von,
        t3.Datumzeit_von AS frei_bis
FROM    t2
LEFT JOIN t2 t3
ON        t2.Behandlungszimmer = t3.Behandlungszimmer
AND        t2.Zeile + 1 = t3.Zeile
WHERE (    datediff(minute,t2.Datumzeit_bis,t3.Datumzeit_von) >= 30
OR        t3.Zeile IS NULL )
AND    (    t2.Datumzeit_bis BETWEEN getdate() AND dateadd(week,4,getdate())
OR        t3.Datumzeit_von BETWEEN getdate() AND dateadd(week,4,getdate())
OR        t3.Datumzeit_von IS NULL )
 
Werbung:
Zurück
Oben