6
SQL: Zeitraum über mehrere Tage prüfen
Hallo zusammen,
ich scheitere derzeit an einer Abfrage.
Es geht um folgendes:
In meiner Datenbank gibt was zwei Spalten Von und Bis jeweils mit Tage/Monat/Jahr Stunden/Minuten/Sekunden. Nun möchte ich alle Datensätze anzeigen, welche sich nur ein einem bestimmten intervall befinden.
Beispiel:
Vorgabezeitraum (wird später dynamisch sein, und von Usereingaben abhängig)
07.08.2022 21:00:00 - 13.08.2022 05:00:00
Nun erhalte ich x-Datensätze, welche auch vor 21 oder nach 5 Uhr existieren sowie vor dem 07 oder nach dem 13. Genauso gibt es datensätze, welche in diesem "Intervall" / "Zeitspanne" passen würde.
Bei meiner Abfrage bekomme ich es hin, entweder innerhalb des ZEIT-Wertes alle Datensätze zu bekommen, hierbei sind aber dann auch Zeiten wie 13,14,15Uhr dabei ODER DATUMS-Werte, bei denen aber dann wiederum der 05,06 August, oder 15,16 August auftauchen.
Prüfe ich beide Bedingungen mit "UND" erhalte ich keine Datensätze. Warum? Ich verstehe das so:
Zeige mir alle Datensätze an welche sich mit Datumwert x-y befinden und mit dem Zeitwert v-w.
Auch habe ich probiert direkt mit DATUM & ZEIT-Werten zu arbeiten aber damit erhalte ich auch Werte außerhalb und innerhalb der Zeitspanne.
Unten mal ein Auszug meiner Abfrage:
tblNachtschicht:
Vielen Dank vorab!!
ich scheitere derzeit an einer Abfrage.
Es geht um folgendes:
In meiner Datenbank gibt was zwei Spalten Von und Bis jeweils mit Tage/Monat/Jahr Stunden/Minuten/Sekunden. Nun möchte ich alle Datensätze anzeigen, welche sich nur ein einem bestimmten intervall befinden.
Beispiel:
Vorgabezeitraum (wird später dynamisch sein, und von Usereingaben abhängig)
07.08.2022 21:00:00 - 13.08.2022 05:00:00
Nun erhalte ich x-Datensätze, welche auch vor 21 oder nach 5 Uhr existieren sowie vor dem 07 oder nach dem 13. Genauso gibt es datensätze, welche in diesem "Intervall" / "Zeitspanne" passen würde.
Bei meiner Abfrage bekomme ich es hin, entweder innerhalb des ZEIT-Wertes alle Datensätze zu bekommen, hierbei sind aber dann auch Zeiten wie 13,14,15Uhr dabei ODER DATUMS-Werte, bei denen aber dann wiederum der 05,06 August, oder 15,16 August auftauchen.
Prüfe ich beide Bedingungen mit "UND" erhalte ich keine Datensätze. Warum? Ich verstehe das so:
Zeige mir alle Datensätze an welche sich mit Datumwert x-y befinden und mit dem Zeitwert v-w.
Auch habe ich probiert direkt mit DATUM & ZEIT-Werten zu arbeiten aber damit erhalte ich auch Werte außerhalb und innerhalb der Zeitspanne.
Unten mal ein Auszug meiner Abfrage:
select * from tblNachtschicht
where
(format(cast(eZeitVon_FULL as date),'yyyy-MM-dd')>=format(cast(pVon_FULL as date),'yyyy-MM-dd') and format(cast(eZeitBis_FULL as date),'yyyy-MM-dd')<=format(cast(pBis_FULL as date),'yyyy-MM-dd'))
and -- mit "and" bekomme ich nichts, führe ich die obere oder untere WHERE Bedingung einzeln aus, dann bekomme ich das Ergebnis. Ich benötige aber die Kombination beider.
(Von_HHmm>=uVon_HHmm and Bis_HHmm <= uBis_HHmm)
order by perID desctblNachtschicht:
declare @start datetime = '07.08.2022 21:00:00'; -- Wird mal Usereingabe
declare @ende datetime = '13.08.2022 05:00:00';-- Wird mal Usereingabe
WITH tblNachtschicht (
Von_HHmm
, Bis_HHmm
, uVon_HHmm
, uBis_HHmm
, eZeitVon_FULL
, eZeitBis_FULL
, pVon_FULL
, pBis_FULL
, DifferenzUser
, perID
, perMC
, Tätigkeit
, TagMonatJahr
)
AS
(
--Aufteilung des Datums in HH:mm von Voragen und Usereingabe
select
format(cast(convert(time,convert(varchar,datepart(HOUR,eB.Zeit_Von))+':'+convert(varchar,datepart(MINUTE,eB.Zeit_Von))) as datetime2),'HH:mm') 'Von_HHmm'
, format(cast(convert(time,convert(varchar,datepart(HOUR,eB.Zeit_Bis))+':'+convert(varchar,datepart(MINUTE,eB.Zeit_Bis))) as datetime2),'HH:mm') 'Bis_HHmm'
, format(cast(convert(time,convert(varchar,datepart(HOUR,@start))+':'+convert(varchar,datepart(MINUTE,@start))) as datetime2),'HH:mm') 'uVon_HHmm'
, format(cast(convert(time,convert(varchar,datepart(HOUR,@ende))+':'+convert(varchar,datepart(MINUTE,@ende))) as datetime2),'HH:mm') 'uBis_HHmm'
, format(Zeit_Von,'yyyy-MM-dd HH:mm') 'eZeitVon_FULL'
, format(Zeit_Bis,'yyyy-MM-dd HH:mm') 'eZeitBis_FULL'
, format(cast(@start as datetime2),'yyyy-MM-dd HH:mm') 'pVon_FULL'
, format(cast(@ende as datetime2),'yyyy-MM-dd HH:mm') 'pBis_FULL'
--Wenn Tagwechsel weil über 0 Uhr dann negativer Zeitwert in Minuten von 24h in Minuten abziehen
, case when
sign(
datediff(minute,
format(
cast(
convert(time,
convert(varchar,
datepart(HOUR,eB.Zeit_Bis))+':'+convert(varchar,datepart(MINUTE,eB.Zeit_Bis))) as datetime2
)
,'HH:mm'
)
,
format(
cast(
convert(time,
convert(varchar,
datepart(HOUR,eB.Zeit_Von))+':'+convert(varchar,datepart(MINUTE,eB.Zeit_Von))) as datetime2
)
,'HH:mm'
)
)
) = 1
then
-1*datediff(minute,
format(
cast(
convert(time,
convert(varchar,
datepart(HOUR,eB.Zeit_Bis))+':'+convert(varchar,datepart(MINUTE,eB.Zeit_Bis))) as datetime2
)
,'HH:mm'
)
,
format(
cast(
convert(time,
convert(varchar,
datepart(HOUR,eB.Zeit_Von))+':'+convert(varchar,datepart(MINUTE,eB.Zeit_Von))) as datetime2
)
,'HH:mm'
)
)+1440
else
-1*datediff(minute,
format(
cast(
convert(time,
convert(varchar,
datepart(HOUR,eB.Zeit_Bis))+':'+convert(varchar,datepart(MINUTE,eB.Zeit_Bis))) as datetime2
)
,'HH:mm'
)
,
format(
cast(
convert(time,
convert(varchar,
datepart(HOUR,eB.Zeit_Von))+':'+convert(varchar,datepart(MINUTE,eB.Zeit_Von))) as datetime2
)
,'HH:mm'
)
)
end as 'DifferenzUser'
, p.ID 'perID'
, p.Ident 'perIdent'
, case when eb.STAT = 4 then 'Lenkzeit' else 'Arbeitszeit' end as 'Tätigkeit'
-- Gruppenwechselfeld für spätere Auswertung
, format(eB.Zeit_Von,'dd.MM.yyyy') 'TagMonatJahr'
from eb
inner join pon p.ID=eb.ID_p
where eb.STAT in (3,4)
)Vielen Dank vorab!!
Auf Facebook teilen
Auf Twitter teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 3698199391
Url: https://administrator.de/contentid/3698199391
Ausgedruckt am: 22.11.2024 um 00:11 Uhr
6 Kommentare
Neuester Kommentar
Moin,
als allererstes: können wir von einem MS SQL-Server > 2012 ausgehen?
Als nächstes: Was ist denn genau die Absicht hinter deiner Abfrage.
Ich habe das Gefühl, dass du das alles zu sehr verkompliziert hast., insbesondere, weil du die Datetime-Werte alle in einzelne Elemente zergliederst. Das macht es viel komplizierter.
Mit
Und wenn es eine Where-Bedingung sein soll, dann:
Wir arbeiten auch mit Schichttagen (07:00 Uhr bis 07:00 Uhr des Folgetages), die != Kalendertagen sind und haben hier auch verschiedenste Auswertungen zu erstellt. Und mit einer anfache CASE-Abfrage bekomme ich so auch das Schichtdatum heraus
Gruß
em-pie
P.S. formatiere das Query mal etwas besser. Durch die vielen zu großen Tabs/ Leerzeichen wird das unleserlich, wie ich finde.
als allererstes: können wir von einem MS SQL-Server > 2012 ausgehen?
Als nächstes: Was ist denn genau die Absicht hinter deiner Abfrage.
Ich habe das Gefühl, dass du das alles zu sehr verkompliziert hast., insbesondere, weil du die Datetime-Werte alle in einzelne Elemente zergliederst. Das macht es viel komplizierter.
Mit
DATEDIFF(Minutes, Von, Bis) erhältst du ja schon die Angaben in Minuten (oder was auch immer du benötigst)Und wenn es eine Where-Bedingung sein soll, dann:
WHERE Start between @start and @end OR ende between @start or @end)Wir arbeiten auch mit Schichttagen (07:00 Uhr bis 07:00 Uhr des Folgetages), die != Kalendertagen sind und haben hier auch verschiedenste Auswertungen zu erstellt. Und mit einer anfache CASE-Abfrage bekomme ich so auch das Schichtdatum heraus
CASE WHEN DATEPART(hour, table.timestamp) < 7 THEN DATEADD(day, -1, table.timestamp)Gruß
em-pie
P.S. formatiere das Query mal etwas besser. Durch die vielen zu großen Tabs/ Leerzeichen wird das unleserlich, wie ich finde.
1
Hallo robdox,
Es wird viel einfacher wenn du immer auf den Sa 5:00 referenzierst und mit Datediff die Stundenspanne bis zum letzen So 21:00 Uhr nimmst.
Dann hast du alles was du willst.
Ergänzend zu em-pie noch zwei ideen, die dir helfen könnten.
Nur geänderte Datensätze der letzten Woche und nur von Mo und Di
KW
Du könntest später die Usereingabe "KW der Nachtschicht auswählen" nutzen.
grüße vom it-frosch
Auch habe ich probiert direkt mit DATUM & ZEIT-Werten zu arbeiten
Du machst es dir viel zu kompliziert.Es wird viel einfacher wenn du immer auf den Sa 5:00 referenzierst und mit Datediff die Stundenspanne bis zum letzen So 21:00 Uhr nimmst.
Dann hast du alles was du willst.
Ergänzend zu em-pie noch zwei ideen, die dir helfen könnten.
Nur geänderte Datensätze der letzten Woche und nur von Mo und Di
select * from tabelle where datepart(dw,aenderdat) in (6,7) and datediff(day,aenderdat,current_timestamp)<=5 KW
Select CAST(YEAR('02.01.2012') AS VARCHAR) + ' KW ' + RIGHT('0'+ CAST(DATEPART(ISO_WEEK, '02.01.2012') as varchar),2) Du könntest später die Usereingabe "KW der Nachtschicht auswählen" nutzen.
grüße vom it-frosch
Zitat von @robdox:
ich scheitere derzeit an einer Abfrage.
(format(cast(eZeitVon_FULL as date),'yyyy-MM-dd')>=format(cast(pVon_FULL as date),'yyyy-MM-dd') and format(cast(eZeitBis_FULL as date),'yyyy-MM-dd')<=format(cast(pBis_FULL as date),'yyyy-MM-dd'))
and -- mit "and" bekomme ich nichts, führe ich die obere oder untere WHERE Bedingung einzeln aus, dann bekomme ich das Ergebnis. Ich benötige aber die Kombination beider.
(Von_HHmm>=uVon_HHmm and Bis_HHmm <= uBis_HHmm)Das ungekannte Datenbanksystem könnte bei FORMAT eine Zeichenkette zurückliefen. Hierdurch kann der Vergleich beliebig kompliziert werden (wann ist in welcher Sprach- und Sortiereinstellung etwas größer oder kleiner). Ich würde hier erstmal mit richtigen Datumswerten arbeiten.
Hallo robdox,
war Blödsinn, was ich eben geschrieben habe, vielleicht hätte ich mal richtig lesen sollen.
Wenn ich das richtig sehe, dann machst Du Dir das doch viel zu kompliziert. Du hast die Zeiten ja schon komplett mit Datum und Zeit in einem Feld, dann mußt Du doch nur prüfen, ob Deine Zeit_Von und Zeit_Bis in dem Intervall liegt:
Wenn der Bereich nur in dem Intervall anfangen soll, dann läßt Du "and Zeit_Bis between @start and @ende" weg, wenn der Bereich nur in dem Intervall enden soll, dann läßt Du "Zeit_Von between @start and" weg.
Oder hab ich das falsch verstanden?
Gruß, Mad Max
war Blödsinn, was ich eben geschrieben habe, vielleicht hätte ich mal richtig lesen sollen.
Wenn ich das richtig sehe, dann machst Du Dir das doch viel zu kompliziert. Du hast die Zeiten ja schon komplett mit Datum und Zeit in einem Feld, dann mußt Du doch nur prüfen, ob Deine Zeit_Von und Zeit_Bis in dem Intervall liegt:
select *
from eb
where Zeit_Von between @start and @ende and Zeit_Bis between @start and @endeWenn der Bereich nur in dem Intervall anfangen soll, dann läßt Du "and Zeit_Bis between @start and @ende" weg, wenn der Bereich nur in dem Intervall enden soll, dann läßt Du "Zeit_Von between @start and" weg.
Oder hab ich das falsch verstanden?
Gruß, Mad Max
@em-pie hat schon alles wichtige beisammen:
1) Welches DBMS in welcher Version?
2) Deine Start- und Endzeitpunkte gehören in das DATETIME Format überführt, alles andere ist totaler Schwachsinn. Zur Not mit einer View, Funktion oder sonst irgendwie on the fly.
3) WHERE Start between @start and @end OR ende between @start or @end) siehe @em-pie . Alles andere baut darauf auf, alles was Datum und/oder Zeit ist wird auch mit entsprechenden Funktionen verglichen.
1) Welches DBMS in welcher Version?
2) Deine Start- und Endzeitpunkte gehören in das DATETIME Format überführt, alles andere ist totaler Schwachsinn. Zur Not mit einer View, Funktion oder sonst irgendwie on the fly.
3) WHERE Start between @start and @end OR ende between @start or @end) siehe @em-pie . Alles andere baut darauf auf, alles was Datum und/oder Zeit ist wird auch mit entsprechenden Funktionen verglichen.
Grundlegend habe ich die Frage so verstanden:
Bei jeder Abfrage wird ein Startzeitpunkt und ein Endzeitpunkt angegeben. Aus der Datenbank/Tabelle sollen nun alle Datensätze angezeigt werden, die eine Schnittmenge mit diesem Zeitraum haben oder komplett darin liegen?
Sollen im zweiten Schritt die Zeiten des Datensatzes in Sekunden ausgegeben werden, die zwischen Start und Ende liegen? Also zur Nachtschicht und Schichtzulagenberechnung herangezogen werden?
Datensätze die vor dem Start enden oder nach den Ende starten sollen nicht angezeigt werden.
Datensätze die hineinreich, zwischen Start und Ende liegen oder hinausreichen sollen angezeigt werden.
Dann würde ich es so lösen:
Wenn die Stunden innerhalbt bestimmter Zeiten (23-0, 0-1, 1-5) summiert werden sollen, habe ich ein Skript, welches dies löst.
Greetz
thejoker2305
Bei jeder Abfrage wird ein Startzeitpunkt und ein Endzeitpunkt angegeben. Aus der Datenbank/Tabelle sollen nun alle Datensätze angezeigt werden, die eine Schnittmenge mit diesem Zeitraum haben oder komplett darin liegen?
Sollen im zweiten Schritt die Zeiten des Datensatzes in Sekunden ausgegeben werden, die zwischen Start und Ende liegen? Also zur Nachtschicht und Schichtzulagenberechnung herangezogen werden?
Datensätze die vor dem Start enden oder nach den Ende starten sollen nicht angezeigt werden.
Datensätze die hineinreich, zwischen Start und Ende liegen oder hinausreichen sollen angezeigt werden.
Dann würde ich es so lösen:
SELECT * FROM eb
WHERE
arbeitsbeginn<ende and arbeitsende>startWenn die Stunden innerhalbt bestimmter Zeiten (23-0, 0-1, 1-5) summiert werden sollen, habe ich ein Skript, welches dies löst.
Greetz
thejoker2305
