✔ SQL Werte aufsplitten

[baeri]

Hallo Leute,

ist es möglich Zeitwerte in "Viertelstunden" aufzusplitten?

meine Tabelle sieht etwa so aus:
WERT|VON|BIS
X|DATUM 9:00|DATUM 9:45
...

als Ausgabe hätte ich gerne:
X|DATUM 9:00|1
X|DATUM 9:15|1
X|DATUM 9:30|1

cool wäre, wenn
X|DATUM 9:15|DATUM 9:35
X|DATUM 9:15|1
X|DATUM 9:30|0.33
> nicht komplett erfüllte Viertelstunden als Prozentsatz zurückgegeben werden...

Welches SQL hab ich noch nicht ganz entscheiden... zur Auswahl stehen (bevorzugt) Access oder Oracle

Vielen Dank

 

[Yaslaw]

Wie soll genau gerechnet werden. Ist der Viertelstundentack der Anfang oder das Ende derPerioede?

9:20 - 9:50

--Die Zeit ist derPeriodenanfang
9:15 | 0.66
9:30 | 1.00
9:45 | 0.33

-- oder
--Die Zeit ist das Periodenende
9:30 | 0.66
9:45 | 1.00
10:00 | 0.33

 

[Yaslaw]

Du kannst es in Access oder Oracle machen.

Hier einen möglichen Aufbau in Access.
Ich verwende die Funktionen least() und greatest(), welche in Oracle etwa so auch existieren

-- Meine Testdaten
SELECT *
FROM tbl_daten;

WERT | VON                 | BIS             
-----|---------------------|--------------------
X    | 01.01.2017 09:00:00 | 01.01.2017 09:45:00
Y    | 01.01.2017 11:10:00 | 01.01.2017 12:40:00
Z    | 01.01.2017 16:54:00 | 01.01.2017 17:47:00

-- Eine Hilfstabelle mit allen 15Minuten-Schritte
SELECT TOP 6 *
FROM tbl_perioden;

ID | ZEIT 
---|---------
1  | 00:00:00
2  | 00:15:00
3  | 00:30:00
4  | 00:45:00
5  | 01:00:00
6  | 01:15:00
...

SELECT
   p.zeit,
   -- Zeiten extrahieren
   TIMEVALUE(d.von) AS zeit_von,
   TIMEVALUE(d.bis) AS zeit_bis,
   -- Unteres Limit für Prozentrechnung
   GREATEST([zeit_von], p.zeit) AS von1,
   -- Oberes Limit für Prozentrechnung
   LEAST([zeit_bis], DATEADD('n',15, p.zeit)) AS bis1,
   -- Differenz für Prozentrechnung
   DATEDIFF('n', von1, bis1) AS diff,
   -- Negative Differnz korrigieren
   IIF(SGN([diff]) = -1, 15 + diff, diff) AS diff1,
   -- Prozente berechnen
   (diff1 / 15) AS prozent,
   d.*
FROM
   tbl_daten d,
   tbl_perioden p
WHERE
   p.zeit >= TIMEVALUE(d.von) AND p.zeit < TIMEVALUE(d.bis)

zeit     | zeit_von | zeit_bis | von1     | bis1     | diff | diff1 | prozent           | WERT | VON                 | BIS             
---------|----------|----------|----------|----------|------|-------|-------------------|------|---------------------|--------------------
09:00:00 | 09:00:00 | 09:45:00 | 09:00:00 | 09:15:00 | 15   | 15    | 1                 | X    | 01.01.2017 09:00:00 | 01.01.2017 09:45:00
09:15:00 | 09:00:00 | 09:45:00 | 09:15:00 | 09:30:00 | 15   | 15    | 1                 | X    | 01.01.2017 09:00:00 | 01.01.2017 09:45:00
09:30:00 | 09:00:00 | 09:45:00 | 09:30:00 | 09:45:00 | 15   | 15    | 1                 | X    | 01.01.2017 09:00:00 | 01.01.2017 09:45:00
11:15:00 | 11:10:00 | 12:40:00 | 11:15:00 | 11:30:00 | 15   | 15    | 1                 | Y    | 01.01.2017 11:10:00 | 01.01.2017 12:40:00
11:30:00 | 11:10:00 | 12:40:00 | 11:30:00 | 11:45:00 | 15   | 15    | 1                 | Y    | 01.01.2017 11:10:00 | 01.01.2017 12:40:00
11:45:00 | 11:10:00 | 12:40:00 | 11:45:00 | 12:00:00 | 15   | 15    | 1                 | Y    | 01.01.2017 11:10:00 | 01.01.2017 12:40:00
12:00:00 | 11:10:00 | 12:40:00 | 12:00:00 | 12:15:00 | 15   | 15    | 1                 | Y    | 01.01.2017 11:10:00 | 01.01.2017 12:40:00
12:15:00 | 11:10:00 | 12:40:00 | 12:15:00 | 12:30:00 | 15   | 15    | 1                 | Y    | 01.01.2017 11:10:00 | 01.01.2017 12:40:00
12:30:00 | 11:10:00 | 12:40:00 | 12:30:00 | 12:40:00 | 10   | 10    | 0.666666666666667 | Y    | 01.01.2017 11:10:00 | 01.01.2017 12:40:00
17:00:00 | 16:54:00 | 17:47:00 | 17:00:00 | 17:15:00 | 15   | 15    | 1                 | Z    | 01.01.2017 16:54:00 | 01.01.2017 17:47:00
17:15:00 | 16:54:00 | 17:47:00 | 17:15:00 | 17:30:00 | 15   | 15    | 1                 | Z    | 01.01.2017 16:54:00 | 01.01.2017 17:47:00
17:30:00 | 16:54:00 | 17:47:00 | 17:30:00 | 17:45:00 | 15   | 15    | 1                 | Z    | 01.01.2017 16:54:00 | 01.01.2017 17:47:00
17:45:00 | 16:54:00 | 17:47:00 | 17:45:00 | 17:47:00 | 2    | 2     | 0.133333333333333 | Z    | 01.01.2017 16:54:00 | 01.01.2017 17:47:00

Nachtrag: Hat noch ein Fehler.Ich schaue nach dem Mittagessen nochmals drauf

 

[baeri]

Krass...

das ist ja schon mal MEGA...
Ich komme leider auch erst in n paar Stunden dazu es zu testen.

Access gefällt mir ...
Funktioniert das auch wenn das Datum dabei ist?
Ich habe bereits eine "Hilfstabelle" angelegt, weil das auch mein Ansatz war (bei dem ich aber am scheitern war)
Ich brauch eh nur die Daten von 1.1.13 - 31.12.17
=> okay, ja das sind dann 100000 Datensätze, aber ist nun mal so <- oder wäre es effizienter Jahre, Monate und 31 Tage extra in Tabellen abzulegen und diese dann "zusammenzujoinen"?

 

[baeri]

Ja... der Zeitrum ist immer der "Startwert"
d.h. 9:15 => 9:15:00 - 9:29:59 <- aber das ist ja nur noch Feinkonzept... <- ich hätte dann vom "ende" immer eine Sekunde abgezogen...

 

[Yaslaw]

Ich habe es mir wieder einmal viel zu schwer gemacht. Dabei habe ich ja seit Jahren einen Lösungsansatz in Peto (SQL: Perioden vergleichen

Nicht vergessen, die Funktionen greatest() und least() gibt es in Access so nicht. Siehe die Links weiter oben.

SELECT    
   d.wert,
   p.zeit,
   -- Differenz / 15
   DATEDIFF('n', GREATEST(TIMEVALUE(d.von), p.von), LEAST(TIMEVALUE(d.bis), p.bis))/15 AS prozent,
   d.von,
   d.bis
FROM
   tbl_daten d,
   -- Die Zeittabelle um eine End-Zeit erweitern
   (SELECT zeit, zeit AS von, DATEADD('n', 15, zeit) AS bis FROM tbl_perioden) p
WHERE
   LEAST(TIMEVALUE(d.bis), p.bis) > GREATEST(TIMEVALUE(d.von), p.von)
ORDER BY
   p.von,
   d.von

wert | zeit     | prozent           | von                 | bis              
-----|----------|-------------------|---------------------|--------------------
X    | 09:00:00 | 1                 | 01.01.2017 09:00:00 | 01.01.2017 09:45:00
X    | 09:15:00 | 1                 | 01.01.2017 09:00:00 | 01.01.2017 09:45:00
X    | 09:30:00 | 1                 | 01.01.2017 09:00:00 | 01.01.2017 09:45:00
Y    | 11:00:00 | 0.333333333333333 | 01.01.2017 11:10:00 | 01.01.2017 12:40:00
Y    | 11:15:00 | 1                 | 01.01.2017 11:10:00 | 01.01.2017 12:40:00
Y    | 11:30:00 | 1                 | 01.01.2017 11:10:00 | 01.01.2017 12:40:00
Y    | 11:45:00 | 1                 | 01.01.2017 11:10:00 | 01.01.2017 12:40:00
Y    | 12:00:00 | 1                 | 01.01.2017 11:10:00 | 01.01.2017 12:40:00
Y    | 12:15:00 | 1                 | 01.01.2017 11:10:00 | 01.01.2017 12:40:00
Y    | 12:30:00 | 0.666666666666667 | 01.01.2017 11:10:00 | 01.01.2017 12:40:00
Z    | 16:45:00 | 0.4               | 01.01.2017 16:54:00 | 01.01.2017 17:47:00
Z    | 17:00:00 | 1                 | 01.01.2017 16:54:00 | 01.01.2017 17:47:00
Z    | 17:15:00 | 1                 | 01.01.2017 16:54:00 | 01.01.2017 17:47:00
Z    | 17:30:00 | 1                 | 01.01.2017 16:54:00 | 01.01.2017 17:47:00
Z    | 17:45:00 | 0.133333333333333 | 01.01.2017 16:54:00 | 01.01.2017 17:47:00

 

[Yaslaw]

Funktioniert das auch wenn das Datum dabei ist?
Ich habe bereits eine "Hilfstabelle" angelegt, weil das auch mein Ansatz war (bei dem ich aber am scheitern war)
Ich brauch eh nur die Daten von 1.1.13 - 31.12.17
=> okay, ja das sind dann 100000 Datensätze, aber ist nun mal so <- oder wäre es effizienter Jahre, Monate und 31 Tage extra in Tabellen abzulegen und diese dann "zusammenzujoinen"?

Nicht ganz. Dann musst du den Code schon anpassen.
Das macht aber nur Sinn, wenn du an unterschiedlichen Tagen unterschiedliche Zeiten prüfen musst. Wenn es für jeden Tag dieselben Zeiten sind, dann kannst du meinen Ansatz nehmen.

 

[baeri]

Vielen Dank,
okay, das werd ich jetzt mal so einpflegen...
Ja ich muss in der Tat Zeiträume über Tage prüfen... aber ich denke mit dem Ansatz kann ich das Script auch entsprechend anpassen...

nochmals 1000 Dank