Information ausblenden
Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm

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

Dieses Thema im Forum "Microsoft SQL Server" wurde erstellt von Dominik85, 12 Februar 2016.

  1. Dominik85

    Dominik85 Benutzer

    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!
     

    Anhänge:

  2. ukulele

    ukulele Datenbank-Guru

    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.
     
  3. Dominik85

    Dominik85 Benutzer

    Danke, dann probier ich das so einmal!
     
  4. akretschmer

    akretschmer Datenbank-Guru

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

    Code:
    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)

    Code:
    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:

    Code:
    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:

    Code:
    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:

    Code:
    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:

    Code:
    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.
     
  5. Dominik85

    Dominik85 Benutzer

    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).
     
    akretschmer gefällt das.
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden