T-SQL

Question

Guten Abend,

hört sich eigentlich ganz einfach an; ich habe aber nicht den richtigen Ansatz gefunden.

Ich möchte von diesen Datensätzen:

USE tempdb
GO
IF OBJECT_ID(N'dbo.daten', 'U') IS NOT NULL
    DROP TABLE dbo.daten
GO

CREATE TABLE dbo.daten (
	id int NOT NULL,
    standort int NOT NULL,
	teilenummer int NOT NULL,
    Start datetime NOT NULL,
    Ende datetime NOT NULL);

INSERT INTO dbo.daten (id, standort, teilenummer, Start, Ende)


SELECT			1, 1234, 982, '11.01.2007', '18.05.2007'    
UNION ALL SELECT	2, 1234, 586, '20.11.2007', '26.08.2014'
UNION ALL SELECT	3, 1234, 111, '11.01.2008', '18.03.2008'
UNION ALL SELECT	4, 1234, 666, '26.03.2008', '28.09.2022'
UNION ALL SELECT	5, 1234, 586, '21.04.2011', '26.05.2014'
UNION ALL SELECT	6, 1234, 222, '25.02.2011', '26.03.2011'
UNION ALL SELECT	7, 1234, 456, '28.05.2011', '01.03.2022'
UNION ALL SELECT	8, 1234, 333, '28.05.2024', '01.01.2099'

jeweils den folgenden Datensatz, wenn ein Zeitraum 3 Monate oder länger nicht belegt war.

Hier also id 2 zwischen 18.05.2007 und 20.11.2007 und id 8 zwischen 01.03.2022 und 28.05.2024.

Schon mal Danke.

Answer 1

Hallo Jörg,

Der direkteste Ansatz und zugleich sicherlich auch langsamste Ansatz wäre einfach das Enddatum des letzten Datensatzes wegzuschreiben und mit dem Startdatum des nächsten zu vergleichen. Das ganze lässt sich z.B. mit Cursor umsetzen.

Besser wäre aber sicher ein selfjoin auf den jeweils nächsten Datensatz zu machen. Somit kannst Du die schnellen set-operationen nutzen.

select t2.*,
datediff(month,t1.ende,t2.start) as Differenz

from dbo.daten t1
left join
dbo.daten t2
on t1.id = t2.id-1

where datediff(month,t1.ende,t2.start) >= 3

Hoffe, das Hilft Dir. ;-)

VG Tarek

Answer 2

Hallo Jörg, falls Du SQL Server 2012 oder höher einsetzt, kannst Du die LAG() Funktion verwenden:

With Aufbereitung as
(
Select id, standort, teilenummer, Start, Ende, LAG(Ende) OVER(PARTITION BY
standort ORDER BY START) as st1
from dbo.daten
)
Select id, standort, teilenummer, Start, Ende, st1
from Aufbereitung
where datediff(MONTH, st1, Start) > 3;

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP - http://www.insidesql.org/blogs/cmu

Answer 3

Hallo Christoph,

vielen Dank. Die LAG Funktion kannte ich noch nicht; sehr flexibel. Das sieht gut aus.

Auch Dir Tarek vielen Dank. ich hatte bereits am Wochenende mit Selfjoin und Crossjoin probiert.

Das Problem ist aber, dass die Datensätze zur Ermittlung des 'nicht belegten Zeitraumes' nicht in eine eindeutige Reihenfolge zu bringen sind - weder über Sortierung nach Start noch über Ende.

Einen schönen Restabend.

Answer 4

Hi Jörg,

Ich verstehe sowieso noch nicht so ganz den Sinn der Abfrage, da du ja nur die beiden (zufällig) nebeneinander stehenden Elemente vergleichst. Müsstest Du nicht auch z.B. Element 1 mit 7 vergleichen?

Nach welchem Kriterium willst Du denn eine Reihenfolge festlegen? Wenn Du nach einer schon bestehenden Spalte der Tabelle ordnen willst, könntest Du dies einfach dadurch erreichen, indem du ein übergeordnetes select durchführst.

select *
from
(select ..... (innere, eigentliche Abfrage)...)
order by ID (z.B.)

VG Tarek

@Christoph: Die Lag-Funktion ist tatsächlich sehr interessant und ich kannte sie noch nicht. Allerdings bin ich bei "over partition by" statements bei großen Datenmengen immer ein wenig misstrauisch. Hast Du damit Erfahrung, welche Operation mehr kostet? der selfjoin oder die lag funktion?

Answer 5

Hallo Tarek,

ich habe zusammen mit Uwe Ricken eine Session zu dem Thema auf dem letzten SQL Saturday gemacht. Dort haben wir demonstriert, wie man durch die Verwendung von Window Functions nicht nur die Statements einfacher gestalten kann, sondern auch die Zugriffszeiten dramatisch verbessern kann.

Answer 6

Hallo Tarek,

nur kurz zur Erklärung - Du hast die Frage schon richtig formuliert - 'Nach welchem Kriterium willst Du denn eine Reihenfolge festlegen?'

Für eine Gruppe ist die Reihenfolge nach Ende richtig; für eine andere Gruppe die nach Start. Dies liegt daran, weil von beiden Seiten 'Überlagerungen' des 3-Monats-Zeitraumes erfolgen können, die aber von einem außerhalb der Rangfolge liegenden Datensatz verursacht werden. Die beiden Sortierungen haben auch keinen identischen Rang, weil Start und Ende nicht fortlaufend, sondern frei sind. Das gilt für jeden Datensatz. Nun wäre es mit CASE noch möglich, die Datenpaare aus jeder Rangfolgen-Bildung korrekt auszulesen. Aber spätestens dann wird es komisch, weil das Datenpaar ja zu zwei Teilenummern gehört, folglich ein Vorgänger bei dieser Fallgestaltung nie eindeutig sein kann, es sein denn, man würde die Treffer als kommaseparierte Liste ausgeben.

Ich werde das jetzt etwas pausieren. Manchmal hat man danach noch eine andere Idee, die mir jetzt spontan nicht einfällt.