Abfragen einer Protokolltabelle mit nichtlinearen data

Ich versuche, einige Abfragen auf einem Protokoll-Stil Tabelle, die eine Reihe von nichtlinearen data enthält laufen. Ich habe folgendes Schema:

Signouts +------------+----------------+------------+----------+ | signout_id | environment_id | date_start | date_end | +------------+----------------+------------+----------+ | int | int | datetime | datetime | +------------+----------------+------------+----------+ Environments +-----+---------+ | id | name | +-----+---------+ | int | varchar | +-----+---------+ 

Signouts ist die Log-Tabelle (und ich sage "Log-Tabelle", weil datasätze werden nie aktualisiert, nur als "deaktiviert" markiert und neu hinzugefügt). Wenn ein Benutzer eine Umgebung abzeichnet, wird die gewählte Start- und Endzeit in die Signaturtabelle eingetragen. Zur time, um zu sehen, ob eine Umgebung unterzeichnet ist, überprüfe ich einfach, ob das aktuelle date zwischen date_start und date_end fällt. Wenn ein anderer Benutzer diese Umgebung abmelden möchte, ist die Mindestzeit, die sie wählen können, das Enddatum des aktuellen Signouts.

Ich habe jetzt eine neue Herausforderung. Ich muss nun ein Reservierungssystem implementieren. Ganz plötzlich können Termine überall in der Zukunft sein, und eine Umgebung kann jederzeit reserviert werden. Jetzt muss ich wissen, wann ein Umfeld noch abgemeldet werden kann und was diese minimalen (und jetzt maximalen) Werte sind!

Ich habe es auf diesen naiven Plan bekommen, aber ich habe Schwierigkeiten, es in SQL zu bringen:

 get all signouts where start < curdate & end > curdate if there is no current signout, get the min start of all signouts where start > curdate if there is a signout, get the max end 

Hier ist die nächste, die ich bekommen habe, unter vielen anderen verschrotteten Abfragen:

 SELECT s.date_start_unavailable, s.date_available, e.id AS environment_id FROM Environments AS e LEFT OUTER JOIN ( SELECT TOP (100) PERCENT signout_id, environment_id, username, date_start, date_end, project, notes, in_use, max(date_end) as date_available, min(date_start) as date_start_unavailable FROM dbo.Signouts WHERE date_end >= GETDATE() GROUP BY signout_id, environment_id, username, date_start, date_end, project, notes, in_use ORDER BY date_start DESC ) AS s ON s.environment_id = e.id 

Das funktioniert fast . date_start_unavailable ist die time, in der das System für eine Signatur nicht verfügbar ist, und dave_available ist die time, in der es keine weiteren Signouts gibt. Das hat aber immer noch Probleme. jemand könnte ein Umfeld jahre in die Zukunft für einen Monat reservieren, und normale Benutzer würden nicht sehen können, dass die meiste time nicht zugewiesen ist. Ich muss einen path finden, um das zu beschränken, aber ich kann mir später noch Sorgen machen.

Die Signaturen dauern für beliebige, vom Benutzer eingegebene timeräume, ansonsten wäre ein zeitbausteinsystemes trivial. Wenn jemand etwas DBA Weisheit anbieten kann, wäre es sehr geschätzt!

Ich setze meine Testumgebung so ein:

 create table environment (id int, name varchar(255)); insert into environment values (1, 'DVD'); insert into environment values (2, 'BluRay'); create table signout (id int, environment_id int, date_start date, date_end date); insert into signout values (1, 1, '01.11.2015', '09.11.2015'); insert into signout values (2, 1, '10.11.2015', '12.11.2015'); insert into signout values (3, 1, '01.12.2015', '24.12.2015'); insert into signout values (4, 2, '01.12.2015', '02.12.2015'); insert into signout values (5, 2, '04.12.2015', '07.12.2015'); insert into signout values (6, 2, '11.12.2015', '13.12.2015'); insert into signout values (7, 2, '14.12.2015', '23.12.2015'); 

Nun ist die Auswahl der gebuchten timeen trivial:

 select e.name, s.date_start d_start, s.date_end d_end, 'booked' as d_status FROM signout s inner join environment e ON e.id = s.environment_id 

Bu was über freie timeen? Dies wäre die timeen, in denen keine Buchung vorhanden ist – also steigst du den Tisch mit sich selbst in einer bestimmten Reihenfolge:

 select e.name, dateadd(DAY, 1, s.date_end) d_start, COALESCE(dateadd(day, -1, s2.date_start), '31.12.2025') d_end, 'free' FROM signout s OUTER APPLY ( SELECT TOP 1 date_start, date_end from signout sx WHERE sx.environment_id = s.environment_id AND sx.date_start > s.date_end ORDER BY sx.date_start ) s2 inner join environment e ON e.id = s.environment_id WHERE (s2.date_end is NULL OR s2.date_start > dateadd(DAY, 1, s.date_end)) 

Jetzt UNION diese zusammen und fügen sort je nach Umgebung und date:

 select e.name, s.date_start d_start, s.date_end d_end, 'booked' as d_status FROM signout s inner join environment e ON e.id = s.environment_id AND s.date_start > getdate() UNION select e.name, dateadd(DAY, 1, s.date_end) d_start, COALESCE(dateadd(day, -1, s2.date_start), '31.12.2025') d_end, 'free' FROM signout s OUTER APPLY ( SELECT TOP 1 date_start, date_end from signout sx WHERE sx.environment_id = s.environment_id AND sx.date_start > s.date_end ORDER BY sx.date_start ) s2 inner join environment e ON e.id = s.environment_id WHERE (s2.date_end is NULL OR s2.date_start > dateadd(DAY, 1, s.date_end)) AND s.date_start > getdate() ORDER BY 1, 2 

Hier ist das, was mir das bringt:

 BluRay 2015-12-01 2015-12-02 booked BluRay 2015-12-03 2015-12-03 free BluRay 2015-12-04 2015-12-07 booked BluRay 2015-12-08 2015-12-10 free BluRay 2015-12-11 2015-12-13 booked BluRay 2015-12-14 2015-12-23 booked BluRay 2015-12-24 2025-12-31 free DVD 2015-11-10 2015-11-12 booked DVD 2015-11-13 2015-11-30 free DVD 2015-12-01 2015-12-24 booked DVD 2015-12-25 2025-12-31 free