Differenz aus 2 Datumswerten, und diese dann summieren

[heldchen]

Hi,

ich habe hier eine PZE ähnliche Software. Hier wird täglich ein wert für den Start und ein Wert für das Ende weggeschrieben.
Beispiel

Mitarbeiter	Zeitpunkt	Status
123	2015-01-05 08:00:00.000	1
123	2015-01-05 18:00:00.000	2
123	2015-01-06 08:00:00.000	1
123	2015-01-06 18:00:00.000	2

Derselbe Mitarbeiter hat also an 2 Tagen Jeweils Start (Status 1 und Ende Status 2) bemeldet.
Nun möchte ich über einen Monat oder eine KW oder wie auch immer (Datepart komme ich mit zurecht) Die Differenz zwischen Status 1 und 2 errechnen und für den gewählten Zeitraum summieren. Hier wären das also an 2 Tagen insgesamt 20 Stunden. Als Newbie in SQL stellt man sich manchmal echt an wie der erste Mensch.... Ich hoffe mal wieder auf eine nette Person die mir hilft

Danke schonmal im Voraus.

 

[akretschmer]

du könntest mit lag() auf die letzte Meldung (partition by mitarbeiter order by zeitpunkt) zugreifen um so die Differenz zu berechnen. Nennt sich Window-Funktion, mehr sollte im Handbuch zu finden sein ...

 

[MDDaniel]

So wie mein Vorgänger gesagt hat nur mit LEAD() statt lag():

with x as (
SELECT *, lead (zeitpunkt) over (order by mitarbeiter, zeitpunkt) as Gehen, DATEDIFF(MINUTE, zeitpunkt, lead (zeitpunkt) over (order by mitarbeiter, zeitpunkt)) as Anwesendheit
FROM #test
)
select * from x where stempel = 1

Vielleicht hast du was verlässlicheres wie den Zeitstempel, oder zusätzlich noch eine genaue Reihenfolge falls 2 Stempel zusammenfallen.

 

[akretschmer]

So wie mein Vorgänger gesagt hat nur mit LEAD() statt lag():

geht prinzipiell beides.

 

[heldchen]

Habe hier leider nichts anderes außer der genannten Felder und noch ein nicht gefülltes Feld mit einer Terminalnummer.

 

[heldchen]

Das haut soweit schonmal hin, auch die eingrenzung nach gewünschtem Zeitraum mit Datediff. Alelrdings habe ich in der Tabelle fehlende Werte, wobei mir dann ein -79 irgendwas wert rauskommt, also ein minuswert. Den könnte ich ja in der where Klausel abfangen indem ich einfach >0 mache und Rest ignoriere. Und die Summierung fehlt mir noch im angegebenen Intervall. Wie greife ich denn in meinen Klauseln nun auf die Spalte "Anwesenheit" welche ja nur eine Ergebnisspalte ist zu?

 

[akretschmer]

mal noch ganz allgemein dazu: Ziel ist ja offenbar, da z.B. kommen und gehen - Zeiten zu erfassen. Meiner Meinung nach wäre es dann besser, beim kommen einen neuen Datensatz anzufangen, wo die gehen-Zeit NULL ist, und beim gehen den Datensatz via UPDATE den gehen-Zeitpunkt einzutragen. Was man dann auch noch haben möchte: es dürfen sich keine Buchungen überlappen, und es darf nicht zu mehr als 1 Datensatz je Mitarbeiter geben, wo es nur eine kommen-Buchung gibt aber keine gehen-Buchung.

Mit PostgreSQL könnte man das elegant wie folgt lösen:

postgres=# create table heldchen(mitarbeiter int, kommt_geht tsrange, exclude using gist(mitarbeiter with =, kommt_geht with &&));
CREATE TABLE
postgres=# create unique index on heldchen (mitarbeiter) where (upper(kommt_geht) is null);
CREATE INDEX
postgres=# insert into heldchen values (123, '[2015-01-05 08:00:00, 2015-01-05 18:00:00)');
INSERT 0 1
postgres=# insert into heldchen values (123, '[2015-01-06 08:00:00, 2015-01-06 18:00:00)');
INSERT 0 1
postgres=# select mitarbeiter, sum(upper(kommt_geht) - lower(kommt_geht)) from heldchen where upper(kommt_geht) is not null group by mitarbeiter;
 mitarbeiter |   sum    
-------------+----------
         123 | 20:00:00
(1 row)

Das Where filtert dabei Datensätze aus, die noch offen sind.

Das exclude using ... verhindert sich überlappende Buchungen, der partielle Index verhindert mehr als 1 offene gehen-Buchung:

postgres=# insert into heldchen values (42, '[2015-01-07 08:00:00,)');
INSERT 0 1
postgres=# insert into heldchen values (42, '[2015-01-08 08:00:00,)');
ERROR:  conflicting key value violates exclusion constraint "heldchen_mitarbeiter_kommt_geht_excl"
DETAIL:  Key (mitarbeiter, kommt_geht)=(42, ["2015-01-08 08:00:00",)) conflicts with existing key (mitarbeiter, kommt_geht)=(42, ["2015-01-07 08:00:00",)).
postgres=#

Und hier noch der Versuch einer überlappenden Buchung:

postgres=# insert into heldchen values (999, '[2015-01-10 08:00:00, 2015-01-10 18:00:00)');
INSERT 0 1
postgres=# insert into heldchen values (999, '[2015-01-10 12:00:00, 2015-01-10 16:00:00)');
ERROR:  conflicting key value violates exclusion constraint "heldchen_mitarbeiter_kommt_geht_excl"
DETAIL:  Key (mitarbeiter, kommt_geht)=(999, ["2015-01-10 12:00:00","2015-01-10 16:00:00")) conflicts with existing key (mitarbeiter, kommt_geht)=(999, ["2015-01-10 08:00:00","2015-01-10 18:00:00")).
postgres=#

Damit sollten Deine Probleme gelöst sein...

 

[heldchen]

Dazu muss ich leider sagen, Die Datenbank ist gegeben und das füllende Programm dazu leider auch. Es geht hier rein um Auswertungen dahinter, welche das Programm (nicht mehr supportet) nicht selbst liefert.

 

[akretschmer]

Dazu muss ich leider sagen, Die Datenbank ist gegeben und das füllende Programm dazu leider auch.

ja, also siehe #2

 

[dabadepdu]

Es geht hier rein um Auswertungen

Wie greife ich denn in meinen Klauseln nun auf die Spalte "Anwesenheit" welche ja nur eine Ergebnisspalte ist zu?

with x as (
SELECT *, lead (zeitpunkt) over (order by mitarbeiter, zeitpunkt) as Gehen, DATEDIFF(MINUTE, zeitpunkt, lead (zeitpunkt) over (order by mitarbeiter, zeitpunkt)) as Anwesendheit
FROM #test
)
select * from x where stempel = 1

..
select x.anwesenheit from x where stempel =1

With x as (..
ist in der Lösung von @MDDaniel eine sogenannte CTE, kannst Du mal nachschlagen, was das ist und was das bringt.

Alelrdings habe ich in der Tabelle fehlende Werte

Wenn Du Dir anschaust, was lag oder lead machen, ist sofort klar, dass fehlende Werte da nicht gut zu passen.
Man müsste hier in der Abfrage entweder Datensätze künstlich ergänzen oder unvollständige Daten komplett rausfiltern, damit Lag Und Lead gut funktionieren.