Delphi-PRAXiS - Datumsüberschneidungen

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.

SQL-Code:

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

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).