Berechnung, wie lange ein Status während der Arbeitszeit aktiv war

[Dominik85]

Guten Morgen,

ich möchte die Dauer eines gesetzten Status berechnen, schaffe das aber leider nicht.
Ein Beispiel der Tabelle ist angehängt.
Ich muss also z.B. berechnen, wie lange der Status "20" gesetzt war. Dies ist für Beleg "A001" vom 05.01.2016 08:05 bis 08.01.2016 08:11 der Fall - soweit auch kein Problem.
Nun sollte ich aber nur die Arbeitszeit (zB 08-17 Uhr) an Arbeitstagen (also ohne SA/SO/Feiertag) für die Berechnung berücksichtigen - und dabei scheitere ich leider.

D.h. das erwünschte Ergebnis wäre "2166 Minuten".
MS SQL Server 2012 ist im Einsatz. Eine Tabelle mit der Kennzeichnung, ob es sich um einen Arbeitstag handelt oder nicht, steht mir zur Verfügung: (Datum / Arbeitstag (J/N))

Vielen Dank für eure Vorschläge!

 

[ukulele]

Das wirst du wohl "manuell" machen müssen mit CASE etc.

Ist die Anfangszeit nach Arbeitsbeginn die Differenz bis Arbeitsende +
Ist die Endzeit nach Arbeitsbeginn die Differenz ab Arbeitsanfang +
Anzahl der Werktage zwischendrin.

 

[Dominik85]

Danke, dann probier ich das so einmal!

 

[akretschmer]

Nicht ganz trivial. Unter PostgreSQL würde ich hier zu Range-Typen greifen, um es mal kurz zu skizieren. Du hast vereinfacht:

test=
test=*# select * from foo;
 beleg |  ts
-------+---------------------
 bla  | 2016-01-05 08:05:00
 bla  | 2016-01-08 08:11:00
(2 rows)

Das stelle ich mal in einem View wie folgt dar: (Daten und View-Definition)

test=*# select * from gearbeitet ;
 beleg |  ts  |  dauer
-------+---------------------+-----------------------------------------------
 bla  | 2016-01-05 08:05:00 | ["2016-01-05 08:05:00","2016-01-08 08:11:00")
 bla  | 2016-01-08 08:11:00 | ["2016-01-08 08:11:00",)
(2 rows)

test=*# \d++ gearbeitet
  View "public.gearbeitet"
 Column |  Type  | Modifiers | Storage  | Description
--------+-----------------------------+-----------+----------+-------------
 beleg  | text  |  | extended |
 ts  | timestamp without time zone |  | plain  |
 dauer  | tsrange  |  | extended |
View definition:
 SELECT foo.beleg,
  foo.ts,
  tsrange(foo.ts, lead(foo.ts) OVER (PARTITION BY foo.beleg ORDER BY foo.ts), '[)'::text) AS dauer
  FROM foo;

Dazu baue ich mir einen weiteren View, der die Soll-Arbeitszeiten enthält, wieder daten und Definition:

test=*# select * from arbeitszeiten ;
  datum  |  zeiten
------------+-----------------------------------------------
 2016-01-01 | ["2016-01-01 08:00:00","2016-01-01 17:00:00")
 2016-01-02 |
 2016-01-03 |
 2016-01-04 | ["2016-01-04 08:00:00","2016-01-04 17:00:00")
 2016-01-05 | ["2016-01-05 08:00:00","2016-01-05 17:00:00")
 2016-01-06 | ["2016-01-06 08:00:00","2016-01-06 17:00:00")
 2016-01-07 | ["2016-01-07 08:00:00","2016-01-07 17:00:00")
 2016-01-08 | ["2016-01-08 08:00:00","2016-01-08 17:00:00")
 2016-01-09 |
 2016-01-10 |
(10 rows)

test=*# \d+ arbeitszeiten
  View "public.arbeitszeiten"
 Column |  Type  | Modifiers | Storage  | Description
--------+---------+-----------+----------+-------------
 datum  | date  |  | plain  |
 zeiten | tsrange |  | extended |
View definition:
 SELECT tage.datum,
  CASE
  WHEN date_part('dow'::text, tage.datum) = ANY (ARRAY[1::double precision, 2::double precision, 3::double precision, 4::double precision, 5::double precision]) THEN tsrange((tage.datum::text|| ' 08:00:00'::text)::timestamp without time zone, (tage.datum::text || ' 17:00:00'::text)::timestamp without time zone, '[)'::text)
  ELSE NULL::tsrange
  END AS zeiten
  FROM ( SELECT ('2016-01-01'::date + s.s::double precision * '1 day'::interval)::date AS datum
  FROM generate_series(0, 9) s(s)) tage;

Daraus bekomme ich dann mal schon dieses:

test=*# select *, gearbeitet.dauer * arbeitszeiten.zeiten as real_gearbeitet from gearbeitet left join arbeitszeiten on gearbeitet.dauer && arbeitszeiten.zeiten where not upper_inf(dauer);
 beleg |  ts  |  dauer  |  datum  |  zeiten  |  real_gearbeitet
-------+---------------------+-----------------------------------------------+------------+-----------------------------------------------+-----------------------------------------------
 bla  | 2016-01-05 08:05:00 | ["2016-01-05 08:05:00","2016-01-08 08:11:00") | 2016-01-05 | ["2016-01-05 08:00:00","2016-01-05 17:00:00") | ["2016-01-05 08:05:00","2016-01-05 17:00:00")
 bla  | 2016-01-05 08:05:00 | ["2016-01-05 08:05:00","2016-01-08 08:11:00") | 2016-01-06 | ["2016-01-06 08:00:00","2016-01-06 17:00:00") | ["2016-01-06 08:00:00","2016-01-06 17:00:00")
 bla  | 2016-01-05 08:05:00 | ["2016-01-05 08:05:00","2016-01-08 08:11:00") | 2016-01-07 | ["2016-01-07 08:00:00","2016-01-07 17:00:00") | ["2016-01-07 08:00:00","2016-01-07 17:00:00")
 bla  | 2016-01-05 08:05:00 | ["2016-01-05 08:05:00","2016-01-08 08:11:00") | 2016-01-08 | ["2016-01-08 08:00:00","2016-01-08 17:00:00") | ["2016-01-08 08:00:00","2016-01-08 08:11:00")
(4 rows)

Daraus kannst z.B. für jeden Tag die Arbeitszeit sehen:

test=*# select datum, lower(real_gearbeitet), upper(real_gearbeitet) from (select *, gearbeitet.dauer * arbeitszeiten.zeiten as real_gearbeitet from gearbeitet left join arbeitszeiten on gearbeitet.dauer && arbeitszeiten.zeiten where not upper_inf(dauer)) bla;
  datum  |  lower  |  upper
------------+---------------------+---------------------
 2016-01-05 | 2016-01-05 08:05:00 | 2016-01-05 17:00:00
 2016-01-06 | 2016-01-06 08:00:00 | 2016-01-06 17:00:00
 2016-01-07 | 2016-01-07 08:00:00 | 2016-01-07 17:00:00
 2016-01-08 | 2016-01-08 08:00:00 | 2016-01-08 08:11:00
(4 rows)

Das aggregiert:

test=*# select sum(upper(real_gearbeitet) - lower(real_gearbeitet)) from (select *, gearbeitet.dauer * arbeitszeiten.zeiten as real_gearbeitet from gearbeitet left join arbeitszeiten on gearbeitet.dauer && arbeitszeiten.zeiten where not upper_inf(dauer)) bla;
  sum
----------
 27:06:00
(1 row)

Das nur mal so ganz grob. Range-Typen hat M$SQL nicht, Du mußt das also eh massiv an die Möglichkeiten Deiner DB anpassen.


PS.: Deine Angaben im Post und im Bild scheinen sich zu widersprechen.

 

[Dominik85]

Danke für die Erklärung akretschmer - jetzt muss ich das "nur noch" per MSSQL hinbringen - ich probiers nochmal
Bez. widersprechen hast du recht - sorry - der 11.01. lt. Screenshot wäre das richtige Datum (ich wollte im Bsp. auch ein Wochenende dabei haben).