Abfrage zu belegten Zeiträumen

Hallo,

Ich benötige einmal einen kleinen Denkanstoß für eine Datenbankabfrage.

Ich habe zwei Tabellen. In einer sind Zeiträume (von/bis) enthalten und in einer zweiten Tabelle n Einträge, ebenfalls von/bis, die zeitlich innerhalb der ersten Tabelle liegen. Aufgabe ist nun, alle Zeitabschnitte auszugeben, die entweder von der zweiten Tabelle belegt oder auch nicht belegt sind. Das ganze soll ohne Hilfstabellen realisiert werden.

Beispiel:
Tabelle A enthält 08:00 bis 20:00 Uhr
Tabelle B zwei Zeilen, 09:00 bis 10:00 und 18:00 bis 19:00 Uhr. Im Ergebnis sollen nun fünf Zeiträume herauskommen:

08:00 bis 09:00 --> frei
09:00 bis 10:00 --> belegt
10:00 bis 18:00 --> frei
18:00 bis 19:00 --> belegt
19:00 bis 20:00 --> frei

Habt ihr da eine Idee?

Viele Grüße
Michael

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 43097681271

Url: https://administrator.de/contentid/43097681271

Ausgedruckt am: 22.11.2024 um 06:11 Uhr

10 Kommentare

Neuester Kommentar

Moin,

Steckt die Uhrzeit von sowie Uhrzeit bis jeweils in einem eigenen Feld oder steht in einem einzigen Feld 09:00 Uhr bis 10:00 Uhr?

Moin,

in beiden Tabellen sind die jeweiligen Zeiten von/bis in gesonderten Feldern.

Gruß
Michael

Hallo,

immer 1 Std. oder auch länger? Wenn es länger ist, müsste man ggf. als Vorarbeit im Select doch die Differenz ausrechnen. Da bis immer höher ist und wir nicht 0 Uhr haben sollte das gehen.

Wenn wir uns im ersten Schritt nur Von ansehen, sind es ja nur 2 Spalten. Da könnte eine WHERE-Clause mit IN helfen

.... Tab1Von IN ( Tab2von )

Würde ja schon mal alle Zeiten markieren, die von der 2. Tabelle belegt werden. Ich hab grad keinen SQL zur Hand. Time ist ja nicht Integer. Ggf. müsste man hier also noch konvertieren. Theoretisch könnt es auch direkt gehen, da die Werte in den beiden Tabellen den gleichen Typ haben. IN hab ich glaub ich noch nie für Uhrzeit benutzt.

Eine Mehtode wäre also IN und NOT IN dann die Ergebnisse mit UNION vereinigen?

Mit WITH kannst du ja auch komplexe Dinge abwickeln.

Wenn es nur um 1 Std. geht müsste es mit Von ausreichend sein. Ist die Zeitspanne größer, würde die IN Abfrage hier nicht mehr ausreichen. Darum nochmal die Frage, ob es nur um 1 Std. geht.

mfg Crusher

Hi!

Es geht tatsächlich um beliebige Zeiträume - das Beispiel hatte ich nur gewählt, um es möglichst leicht nachvollziehbar darzustellen. Ganz grundsätzlich können also vor und nach den jeweiligen Einträgen in Tabelle B freie, unbelegte Zeiten existieren, in Grenzfällen auch nicht bis dahin, dass es nur einen Eintrag in Tabelle B gibt, der exakt 1:1 den gleichen Zeitraum wie in Tabelle A enthält. Um die Sache noch (etwas) zu verkomplizieren, können die jeweiligen Zeiten (DateTime) auch über die 0-Uhr-Grenze gehen. Ändert an der Systematik nichts, allerdings kann man eben nicht einfach auf Stunden/Minuten abstellen.

Ein UNION sehe ich auch auf jeden Fall, allerdings wirds's dann wirklich hakelig. Meine Grundidee dabei ist, dass ich zunächst einmal je Eintrag in B drei Ergebnisse bilde, nämlich den Zeitraum

- zwischen Beginn A und Beginn B (kein Ergebnis, wenn Beginn A = Beginn B)
- zwischen Beginn B und Ende B und
- zwischen Ende B und Ende A (keine Ergebnis, wenn Ende B = Ende A)

So hätte ich dann je Eintrag in B bis zu drei Einträge (generiert durch ein Union von drei Abfragen) und müsste diese dann wiederum - ebenfalls durch ein weiteres Union - ingesamt zusammenfassen. Klingt mächtig kompliziert und alles andere als elegant.

Gruß
Michael

Oh Gott....

Wenn die Zeiten in B leer sind, kann man das im WHERE-Part des Queries berücksichtigen. Oder man macht eine Sub-Query. Damit kann man schon mal alle leeren Zeiten eliminieren. Dann wären wir hier wieder am Anfang. Bei der Nutzung von IN hätten wir zumindest erstmal alle Anfänge sauber herausgeholt.

Du willst ja keine Tabellen. Wie sieht es mit Procedures und Functions aus? Zumindest könnte das einiges vereinfachen.

Tabelle A weißt nur eine Taktung von jeweils 1 Std. auf oder auch über mehrere?

18:00 bis 19:00 --> belegt
19:00 bis 20:00 --> belegt

wenn in B

18:00 bis 20:00 --> belegt

Mit Bsp. Daten wäre das ganze einfacher. Wenn der Abstand in A bei 1 Std. bleiben soll, müsste man eine Duration berechnen. < 0 Uhr kein Problem. bei Mitternacht muss man mehr tun. Wenn man eine Duration von 3 Std hat, müsste man bei der Taktung von 1 Std. 3 Rows auf "besetzt" setzen.

Wäre auch mit SELECT ... WHERE .. möglich. In dem du Tabelle B Von Bis zur Eingrenzung nutzt. Dann kommen nur Std. innerhalb dieser Range heraus, die man dann wiederum auf besetzt sezten kann.

Etwas blöd so. Gibt ja genug Online Seiten um seinen Code zu posten: https://tio.run/ oder auch um Daten abzulegen. Wäre einfacher wenn du uns mal eine Ausgangsituation gibst, die ALLE Aspekte was vorkommen kann abdeckt.

So rät man zur Hälfte, bzw. übersieht wieder was.

Noch ein Tip: Du kannst es auch zergliedern und dir Hilfs-Spalten machen

IIF oder CASE

SELECT Street, Postcode, City, IIF(City IN  ('Hamburg','Berlin'),'x','') as [Stadt-Marker] FROM table  

Hier mit IN. Geht auch mit = wenn man direkt vergelichen will. Wen du es innerhalt von WITH.... AS machst, erhälst du eine vollwertige Tabelle zurück, die du wieder einfach anzapfen kannst.

Aber Server 2012 haben wir IIF. Ansonsten kann man es auch mit CASE abbilden.

Statt nur mit UNION kann man sich ggf. hier mit auch weiterhelfen, wenn man Probleme hat die Datensätze abzugrenzen.

https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-ex ...

Blöde Frage aber es geht um SQL korrekt? Was für ein DBMS ist das in welcher Version?

Welches Format haben die Zeitspalten, TIME?

Hat jeder Datensatz auch eine DATE oder DATETIME-Spalte?

Sind die Daten in B dahingehend konsistent das B immer innerhalb von A liegt oder muss dahingehend auch noch geprüft werden?

Hallo Michael,

nette Denksportaufgabe

Hier mein Lösungsvorschlag für SQL Server 2017:

drop table if exists #TabelleA
drop table if exists #TabelleB
create table #TabelleA (ID int identity, ZeitVon datetime, ZeitBis datetime)
create table #TabelleB (ID int identity, ZeitVon datetime, ZeitBis datetime)

insert into #TabelleA (ZeitVon, ZeitBis) values (convert (datetime, '2023-01-01 08:00', 120), convert (datetime, '2023-01-01 20:00', 120))  
insert into #TabelleB (ZeitVon, ZeitBis) values	(convert (datetime, '2023-01-01 09:00', 120), convert (datetime, '2023-01-01 10:00', 120))  
insert into #TabelleB (ZeitVon, ZeitBis) values	(convert (datetime, '2023-01-01 18:00', 120), convert (datetime, '2023-01-01 19:00', 120))  

select * from #TabelleA
select * from #TabelleB

-- belegte Zeiten, direkt aus Tabelle B
select	ZeitVon, ZeitBis, 'belegt' as Art  
from	#TabelleB
where	ZeitBis > ZeitVon
union all
-- freie Zeit: Start des Zeitraums - Start der ersten belegten Zeit
select	a.ZeitVon, b.ZeitVon, 'frei'  
from	#TabelleA a
	join (select top (1) * from #TabelleB order by ZeitVon) b on b.ZeitVon > a.ZeitVon
union all
-- freie Zeit: Ende einer belegten Zeit - Start der naechsten belegten Zeit
select	ZeitVon, ZeitBis, 'frei'  
from	(select	ZeitBis as ZeitVon, lead (ZeitVon, 1) over (order by ZeitVon) as ZeitBis
	from	#TabelleB) t
where	ZeitBis > ZeitVon
union all
-- freie Zeit: Ende der letzten belegten Zeit - Ende des Zeitraums
select	b.ZeitBis, a.ZeitBis, 'frei'  
from	#TabelleA a
	join (select top (1) * from #TabelleB order by ZeitBis desc) b on a.ZeitBis > b.ZeitBis
order by ZeitVon

Das berücksichtigt nicht:
- mehrere Zeitbereiche in Tabelle A
- Überschneidungen innnerhalb der Tabelle B
- Überschneidungen der Zeitbereiche der Tabellen A und B
- ZeitVon > ZeitBis

Aber als Denkanstoß vielleicht zu gebrauchen und ausbaufähig.

Gruß, Mad Max

👍Vielen Dank, das ist eine sehr gute Basis zum weiteren Ausprägen! Klasse!

Die Lösung von @MadMax ist sicherlich machbar. Ich würde allerdings anmerken das, je nach Datenmenge, die Subselects keine gute Wahl sind. Jeder Subselect macht erstmal einen Full Table Scan da keine WHERE Bedingung und ein ORDER BY über alle Daten., zusätzlich zu den eigentlichen Abfragen. Bei 3 Zeilen ist das egal, bei 3000 vermutlich auch, aber so richtig gut finde ich das nicht.

Ein eleganter Weg ohne UNION ALL fällt mir jetzt auch nicht ein aber mit UNION ALL würde ich erstmal alles in eine Tabelle bringen und dann mit lead() arbeiten. Das hat auch den Vorteil das man viel prüfen kann, z.B. auch zu kleine Zeiträume direkt filtern kann:

drop table if exists #TabelleA
drop table if exists #TabelleB
create table #TabelleA (ID int identity, ZeitVon datetime, ZeitBis datetime)
create table #TabelleB (ID int identity, ZeitVon datetime, ZeitBis datetime)

insert into #TabelleA (ZeitVon, ZeitBis) values (convert (datetime, '2023-01-01 08:00', 120), convert (datetime, '2023-01-01 20:00', 120))    
insert into #TabelleB (ZeitVon, ZeitBis) values	(convert (datetime, '2023-01-01 09:00', 120), convert (datetime, '2023-01-01 10:00', 120))    
insert into #TabelleB (ZeitVon, ZeitBis) values	(convert (datetime, '2023-01-01 18:00', 120), convert (datetime, '2023-01-01 19:00', 120))    

select * from #TabelleA
select * from #TabelleB

SET STATISTICS TIME ON;

WITH tabelle(Zeit,Ereignis) AS (
	SELECT	ZeitVon,
			'frei'  
	FROM	#TabelleA
	UNION ALL
	SELECT	ZeitBis,
			'unfrei'  
	FROM	#TabelleA
	UNION ALL
	SELECT	ZeitVon,
			'gebucht'  
	FROM	#TabelleB
	UNION ALL
	SELECT	ZeitBis,
			'frei'  
	FROM	#TabelleB
	)
SELECT	t.*
FROM	(

SELECT	tabelle.Zeit AS ZeitVon,
		lead(tabelle.Zeit) OVER (ORDER BY tabelle.Zeit) AS ZeitBis,
		tabelle.Ereignis
FROM	tabelle
WHERE	tabelle.Ereignis != 'unfrei'  

	) t
WHERE	t.ZeitVon != t.ZeitBis
ORDER BY t.ZeitVon