Zeitraum zwischen zwei Rows

[it-bergmann]

Hallo,

ich habe eine Abfrage die mir verschiedene Zeiten auswirft:
select key_mitarbeiter,key_buchungsart,date(buchungszeit) As datum,time(buchungszeit) as Zeit from buchung where key_mitarbeiter=1644 order by datum
Ergebnis:
1644 Ge 2008-02-01 13:05:33
1644 Ko 2008-02-01 07:07:56

Jetzt gibt es für key_buchungsart jeweils eine Zeile für "Ko"=Kommen und "Ge"=Gehen, sprich einmal die Zeit, wann der Mitarbeiter gekommen und einmal, wann er gegangen ist, in dem Beispiel also um 07:07:56 gekommen und um 13:05:33 gegangen.

Hat jemand eine Idee, wie man die gesammte Anwesenheit (also von 07:07:56 bis 13:05:33) berechnen kann, also im Sinne von:
1644 2008-02-01 5:28

Danke,

Andre

 

[kuddeldaddeldu]

Hi,

ungetestet:

SELECT 
     key_mitarbeiter, 
     DATE(buchungszeit) AS Datum, 
     TIMEDIFF(TIME(buchungszeit), 
        (SELECT TIME(buchungszeit) FROM buchung
           WHERE key_mitarbeiter = 1644 
              AND key_buchungsart = 'KO')) 
     AS Dauer
     FROM buchung
     WHERE key_mitarbeiter = 1644
        AND key_buchungsart = 'GE'

LG

 

[it-bergmann]

Hallo,

ergibt leider den Fehler: More than one result row not allowed.
Wenn ich z.B. nur den Select aus dem TIMEDIFF eingebe, erhalte ich die Rows für alle Tage. Ich habe mal versucht, das Feld "Datum" als Bedingung zu setzen, aber dann motzt die DB, dass "Datum" nicht bekannt sei.
Denke mal, weil es ja eigentlich mit in das erste Select fällt ...

Danke,

Andre

 

[Pixel1001]

Hallo,

hier eine Variante, die bei MYSQL funktioniert:

select b.key_mitarbeiter, b.buchungszeit as kommt, c.buchungszeit as geht ,
SEC_TO_TIME(TIME_TO_SEC(c.buchungszeit)-TIME_TO_SEC(b.buchungszeit)) as Zeit
from (buchung b,buchung c)
where b.lfdnr=(select max(lfdnr) from buchung where key_buchungsart='Ko' and key_mitarbeiter=1644)
and c.lfdnr=(select max(lfdnr) from buchung where key_buchungsart='Ge' and key_mitarbeiter=1644)
;

oder

SELECT           
           key_mitarbeiter,
           DATE(buchungszeit) AS Datum,
           TIMEDIFF(max(TIME(buchungszeit)),
              (SELECT max(TIME(buchungszeit)) FROM buchung
                 WHERE key_mitarbeiter = 1644
                    AND key_buchungsart = 'Ko'   group by key_buchungsart))
           AS Dauer
           FROM buchung
           WHERE key_mitarbeiter = 1644
              AND key_buchungsart = 'Ge'   group by key_buchungsart;

ich würde nur die Variablen noch in eine DB-Variable speichern ---->

Set @User=1644

...key_mitarbeiter=@User

Aber das wird bei anderen Datenbanken eventuell nicht funktionieren.

Hier meine Testdaten:

CREATE TABLE `buchung` (
`lfdnr` tinyint(3) unsigned NOT NULL auto_increment,
`key_buchungsart` char(2) NOT NULL default '',
`buchungszeit` datetime default NULL,
`key_mitarbeiter` int(10) unsigned default NULL,
PRIMARY KEY (`lfdnr`),
UNIQUE KEY `lfdnr` (`lfdnr`),
KEY `lfdnr_2` (`lfdnr`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `buchung` (`lfdnr`, `key_buchungsart`, `buchungszeit`, `key_mitarbeiter`) VALUES("1", "Ko", "2008-02-01 07:00:00", "1644");
INSERT INTO `buchung` (`lfdnr`, `key_buchungsart`, `buchungszeit`, `key_mitarbeiter`) VALUES("2", "Ge", "2008-02-01 18:00:00", "1644");
INSERT INTO `buchung` (`lfdnr`, `key_buchungsart`, `buchungszeit`, `key_mitarbeiter`) VALUES("3", "Ko", "2008-02-02 07:05:01", "1644");
INSERT INTO `buchung` (`lfdnr`, `key_buchungsart`, `buchungszeit`, `key_mitarbeiter`) VALUES("4", "Ge", "2008-02-02 15:01:00", "1644");
INSERT INTO `buchung` (`lfdnr`, `key_buchungsart`, `buchungszeit`, `key_mitarbeiter`) VALUES("5", "Ko", "2008-02-03 07:12:11", "1644");
INSERT INTO `buchung` (`lfdnr`, `key_buchungsart`, `buchungszeit`, `key_mitarbeiter`) VALUES("6", "Ge", "2008-02-03 16:07:40", "1644");
INSERT INTO `buchung` (`lfdnr`, `key_buchungsart`, `buchungszeit`, `key_mitarbeiter`) VALUES("7", "Ko", "2008-02-04 08:20:30", "1644");
INSERT INTO `buchung` (`lfdnr`, `key_buchungsart`, `buchungszeit`, `key_mitarbeiter`) VALUES("8", "Ge", "2008-02-04 19:03:00", "1644");

SQL ist genial

Welche Datenbank setzt du ein ?

Pixel1001

 

[kuddeldaddeldu]

Hi,

stimmt, innerhalb des Subselects ist Datum unbekannt.

Wie sieht's so aus:

SELECT 
     key_mitarbeiter, 
     DATE(buchungszeit) AS Datum, 
     TIMEDIFF(TIME(buchungszeit), 
        (SELECT TIME(buchungszeit) FROM buchung
           WHERE key_mitarbeiter = 1644 
              AND key_buchungsart = 'KO'
              AND DATE(buchungszeit) = '2008-02-01')) 
     AS Dauer
     FROM buchung
     WHERE key_mitarbeiter = 1644
        AND key_buchungsart = 'GE'
        AND Datum = '2008-02-01'

LG

 

[Pixel1001]

Hallo,

SEC_TO_TIME(TIME_TO_SEC(c.buchungszeit)-TIME_TO_SEC(b.buchungszeit)) AS Zeit

kann natürlich auch durch

TIMEDIFF(c.buchungszeit,b.buchungszeit) as Zeit ersetzt werden.


Pixel1001.

 

[Pixel1001]

Hallo,

das Rätsels Lösung ist Group By

entweder je Tag oder User oder wie im Beispiel auf die letzte Zeit mit Max()

Bei einer Liste über einen Zeitraum wird es etwas komplizierter.

...da müsste ich mal einwenig testen...


Liebe Grüße

Pixel1001

 

[it-bergmann]

@kuddeldaddeldu

Na, gibt zumindest eine andere Fehlermeldung: System error: KB Stack type illegal.
Diesmal ist direkt die erste Select Anweisung als fehlerhaft deklariert. Warum auch immer und vor allem - was immer das heissen mag. Aber ich denke der Ansatz bringt schon was.

@Pixel1001

Bei Deiner Variante 1 meckert die DB es würde ein Select fehlen. Na ja, ich denke mal, so flexibel ist die einfach nicht.
Variante 2 meldet den Fehler: Invalid keyword or missing delimiter.
Es wird aber kein Fehler hervorgehoben.

Das Manko wird sein, dass es sich hier um die SAPDB 7.4 handelt.
Wird wohl der berühmte Kamp gegen Windmühlen ...

Danke,

Andre

 

[Pixel1001]

Hallo,

also falls es möglich ist, würde ich die Auswertung in die Programmlogik einbauen.

Da hier nur mit einer Kreuztabelle Abhilfe geschafft werden kann.

Aber ich kenne mich leider mit SAPDB nicht aus.


Im Oracle-Buch (leider in der Firma) habe ich ein schönes Beispiel dazu...

Aber das hilft dir jetzt auch nicht weiter, und ob es dann funktioniert kann ich auch nicht sagen.

Also bei manchen Abfragen nehme ich CrystalReports, bastel mir die Ansicht zusammen und schaue dann nach den SQL-Code, den ich dann für mich noch anpasse. Eventuell hast du solch einen Generator?!

Aber weiterhin viel Spaß damit

Pixel1001

 

[kuddeldaddeldu]

Hi,

da kenne ich mich leider auch nicht aus. Aber wenn man die Fehlermeldung mal Google unter die Nase hält, sieht's so aus, als wäre das ein Bug, den die schon seit mehreren Releases versuchen zu fixen. Da gibt's dann so Tips, wie Indizes löschen, Optimierung abschalten , auf das nächste Release warten :suspekt: ...
Kannst Du die Datenbank updaten (ist ja nicht mehr die neueste Version)? Vielleicht würde das das Problem schon lösen. Ansonsten das Problem mal bei denen ins Forum setzen.

LG