Datumsüberschneidungen

[topf5500]

Hallo DP-Gemeinde,

ich stehe hier vor einem Problem (oder habe einen Black-out). Ich habe eine Tabelle mit folgenden Daten (siehe unten - oder Anhang),
ich möchte nun die Summe der Ausfallstunden (Ausfall) der Maschine M1 wissen, bei der ein Ausfall gekennzeichnet ist (Feld A).
Nur überschneiden sich teilweise die Ausfallzeiten, weil z.B. zwei Gewerke gleichzeitig gearbeiten haben (Schlosser+Elektriker),
wie bekomme ich die reale Ausfallzeit berechnet (am Besten in der Datenbank - evtl. SQL ?)
P.S. Datensätze können doppelt vorkommen


Maschine Ausfall A StörBeginn StörBgZt StörEnde StörEZt
M1 1,12 X 03.01.2011 12:53:23 03.01.2011 14:00:49
M1 0,60 X 05.01.2011 16:56:47 05.01.2011 17:33:00
M1 2,78 X 07.01.2011 19:58:10 07.01.2011 22:45:00
M1 2,74 X 07.01.2011 21:50:55 08.01.2011 00:35:25
M1 1,87 X 08.01.2011 00:37:50 08.01.2011 02:30:00
M1 2,82 X 08.01.2011 00:40:34 08.01.2011 03:30:00
M1 10,37 X 08.01.2011 03:37:47 08.01.2011 14:00:00
M1 3,50 X 08.01.2011 18:13:51 08.01.2011 23:30:00
M1 1,20 X 09.01.2011 08:46:00 09.01.2011 09:58:13
M1 0,59 X 09.01.2011 09:57:21 09.01.2011 10:32:46
M1 18,02 X 11.01.2011 13:09:47 12.01.2011 07:10:42
M1 0,63 X 18.01.2011 16:31:57 18.01.2011 17:10:00
M1 5,16 X 20.01.2011 09:20:58 20.01.2011 14:30:34
M1 0,70 X 22.01.2011 04:17:46 22.01.2011 05:00:00
M1 0,47 X 23.01.2011 13:12:02 23.01.2011 13:40:00
M1 10,23 X 24.01.2011 10:26:10 24.01.2011 20:40:00
M1 10,23 X 24.01.2011 10:26:10 24.01.2011 20:40:00
M1 0,00 27.01.2011 15:08:29 00:00:00
M1 0,61 X 31.01.2011 20:09:09 31.01.2011 20:45:57
M1 0,61 X 31.01.2011 20:09:09 31.01.2011 20:45:57


Gruß
Topf5500

[Aphton]

Einfache Lösung anhand folgender Erklärung:

Strukturelle Verwaltung der Ausfallsdaten sehen ~so aus:

Delphi-Quellcode:

TAusfallZeit = record
  Von: TDateTime;
  Bis: TDateTime;
end;

var
  AusfallZeiten: Array of TAusfallZeit;

Im AusfallZeiten Array hast du nun die oben gepostete (sortierten) Liste.

Lösungs-Algorithmus

Code:

Schleife 1
- gehe jedes (bis zum Vorletzten) Element e im Array durch
- schau, ob e.Bis > nächstes Element.Von
- Falls ja, dann "merge" sie. dh lösche beide Elemente, erstelle ein Neues, dessen "Von" = e.Von und "Bis" = nächstes Element.Bis

Schleife 2
- Summiere die Stunden von "Von" bis "Bis" auf

[jobo]

Also mit Standard SQL wird das schwierig. Ohne eine Stored Procedure mit einem Algorithmus ala Aphton geht's vermutlich nur, wenn man sowas wie Analytic Functions verwendet oder aufwendig Teilmengen/Subselects definiert.

Der Algo von Aphton funktioniert allerdings auch nicht, wenn die Folgemenge (bis) eine vollständige Teilmenge aus (von) ist, oder?

[jobo]

Ich hab es mit einfachem SQL probiert, so ähnlich könnte es auch mit ACCESS gehen:

SQL-Code:

create or replace view TMaAusfall as
select rownum as LfdNr, -- virtueller "Primärschlüssel"
       t.*
 from (
        select x.field02 as Maschine,
               field03 as Ausfall,      -- einige Werte bereits falsch, da Zeiten tw. schon geändert *2
               to_date(x.field05||field06, 'DD.MM.YYYYHH24:MI:SS') as AusfallBeginn,
               to_date(x.field07||field08, 'DD.MM.YYYYHH24:MI:SS') as AusfallEnde
          from ar_allpp x -- meine copy/paste allzwecktabelle *3
         where field01 = 'TimeShift d' and field04='X'     -- *3   
         order by 1,3  -- diese Sortierfolge ist unumgänglich
      ) t;

View created

*2: Die Daten sind leicht editiert, um eine vollständige Teilmenge dabei zu haben, aber vielleicht kommt das auch gar nicht vor
*3: ar_allpp ist (m)eine Allzwecktabelle aus einem Dutzend Textfeldern. Dort habe ich die Werte reinkopiert.

Der View TMaAusfall dient als Basis. Die where Bedingung filtert auf nur Ausfall='X' und ein eigenes Selektionkriterium.

select * from TMaAusfall; -- modifizierte Originaldaten

Code:

 LFDNR MASCHINE AUSFALL  AUSFALLBEGINN        AUSFALLENDE
------ -------- -------- -------------------- --------------------
     1 M1       1,12     03.01.2011 12:53:23  03.01.2011 14:00:49
     2 M1       0,60     05.01.2011 16:56:47  05.01.2011 17:33:00
     3 M1       2,78     07.01.2011 19:58:10  07.01.2011 22:45:00
     4 M1       2,74     07.01.2011 21:50:55  08.01.2011 00:35:25
     5 M1       1,87     08.01.2011 00:37:50  08.01.2011 02:30:00
     6 M1       2,82     08.01.2011 02:40:34  08.01.2011 06:30:00
     7 M1       10,37    08.01.2011 03:37:47  08.01.2011 05:00:00
     8 M1       3,50     08.01.2011 18:13:51  08.01.2011 23:30:00
     9 M1       1,20     09.01.2011 08:46:00  09.01.2011 09:58:13
    10 M1       0,59     09.01.2011 09:57:21  09.01.2011 10:32:46
    11 M1       18,02    11.01.2011 13:09:47  12.01.2011 07:10:42
    12 M1       0,63     18.01.2011 16:31:57  18.01.2011 17:10:00
    13 M1       5,16     20.01.2011 09:20:58  20.01.2011 14:30:34
    14 M1       0,70     22.01.2011 04:17:46  22.01.2011 05:00:00
    15 M1       0,47     23.01.2011 13:12:02  23.01.2011 13:40:00
    16 M1       10,23    24.01.2011 10:26:10  24.01.2011 20:40:00
    17 M1       10,23    24.01.2011 10:26:10  24.01.2011 20:40:00
    18 M1       0,61     31.01.2011 20:09:09  31.01.2011 20:45:57
    19 M1       0,61     31.01.2011 20:09:09  31.01.2011 20:45:57
 
19 rows selected

Nun kommt die Summierung, leider etwas unübersichtlich:

SQL-Code:

select maschine,
       to_char(Ausfallbeginn, 'YYYYMMDD') as Tag, -- weglassen oder anders clustern nach Bedarf
       sum(Ausfallende-Ausfallbeginn)*24          -- Ausfall in Stunden *1
  from (
        select * from TMaAusfall -- Hauptmenge
        where lfdnr not in -- Menge mit Überschneidungen rausnehmen
           (select lfdnr from TMaAusfall y -- menge mit folgender Überschneidung
             where exists (select 1 from TMaAusfall z
                            where z.AusfallBeginn< y.AusfallEnde and y.lfdnr=z.lfdnr-1)
             union
            select lfdnr from TMaAusfall y -- menge mit vorangehender Überschneidung
             where exists (select 1 from TMaAusfall z
                            where y.AusfallBeginn< z.AusfallEnde and y.lfdnr-1=z.lfdnr)
            )
        minus -- Inklusionen ausschließen
        select * from TMaAusfall y -- menge, die in anderer Menge eingeschlossen ist
         where exists (select 1 from TMaAusfall z
                             where z.AusfallBeginn>y.AusfallBeginn and z.AusfallEnde < y.AusfallEnde  )
        union -- Aufbereitete Überschneidung wieder anfügen
        select f.lfdnr, f.maschine,
               null as ausfall,            -- der Originalwert stimmt nicht mehr, könnte auch hier neu berechnet werden
               f.ausfallbeginn, v.AusfallEnde
          from (select * from TMaAusfall y -- Menge mit folgender Überschneidung
                 where exists (select 1 from TMaAusfall z
                                where z.AusfallBeginn< y.AusfallEnde and y.lfdnr=z.lfdnr-1)) f,
               (select * from TMaAusfall y -- Menge mit vorangehender Überschneidung
                 where exists (select 1 from TMaAusfall z
                                where y.AusfallBeginn< z.AusfallEnde and y.lfdnr-1=z.lfdnr)) v
         where f.lfdnr=v.lfdnr-1
       ) sumA
 group by maschine, to_char(Ausfallbeginn, 'YYYYMMDD')
 order by 1,2;

Das Statement müsste so oder so ähnlich auch unter Access laufen. Ich hab's aber nicht probiert. Auch mein eigenes Ergebnis habe ich nicht nachgerechnet.

Aber das Prinzip funktioniert

Ergebnis:

Code:

MASCHINE  TAG      SUM(AUSFALLENDE-AUSFALLBEGINN)
--------- -------- ------------------------------
M1        20110103               1,12388888888889
M1        20110105              0,603611111111111
M1        20110107               4,62083333333333
M1        20110108                         9,4625
M1        20110109               1,77944444444444
M1        20110111               18,0152777777778
M1        20110118              0,634166666666667
M1        20110120                           5,16
M1        20110122              0,703888888888889
M1        20110123              0,466111111111111
M1        20110124               10,2305555555555
M1        20110131              0,613333333333333
 
12 rows selected

*1 Verfahrensbedingt können hier für "Ausfall" auch Werte größer 24 h auftauchen. Den Originalwert habe ich nur Spaß mitgeschleift. Der ist eigentlich von Beginn an redundant (überflüssig, falsch).

[topf5500]

Danke jobo,
werde versuchen das auf Access zu portieren.

Gruß
Topf