mysql: Update Zeile mit "Summe Zeit pro Tag und Mitarbeiter"

[swinkel]

Hallo Foren-Mitglieder,

meine Tabelle:
CREATE TABLE `buchungen` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`persID` varchar(255) DEFAULT NULL,
`start` datetime DEFAULT NULL,
`stop` datetime DEFAULT NULL,
`state` tinyint(4) DEFAULT NULL,
`zeit` double DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=181 DEFAULT CHARSET=latin1


Beispiel Daten vereinfacht:

id | persID | start | stop | state | zeit
1 | 123 | 2018-07-24 10:00:00 | 2018-07-24 11:00:00 | 0 | 1
2 | 123 | 2018-07-24 11:00:00 | 2018-07-24 12:00:00 | 0 | 1
3 | 123 | 2018-07-24 11:00:00 | NULL | 2 | NULL
4 | 124 ...............

Ich möchte pro persID checken, welches Datum start in dem Eintrag mit state 2 steht. Dann für den Tag alle Buchungen dieser einen Person summieren und bei dem Eintrag mit state 2 einfügen. Bei dem Bespiel müsste dann bei Index 3 bei zeit eine 2 eingefügt werden. (Nicht wundern, das ist jetzt vereinfacht.)

Mein Befehl dazu hat schon mal sporadisch funktioniert und dann wieder nicht.

UPDATE buchungen a   



INNER JOIN ( SELECT @var: = persID, persID, SUM(zeit) 'sum' FROM buchungen where DATE_FORMAT(start, "%Y%M%D") = (select DATE_FORMAT(start,  "%Y%M%D") from buchungen where state = 2 and persID = @var ) GROUP BY persID )  c   



ON a.persID = c.persID   



SET zeit = sum where state = 2 and stop is null;

Vielleicht hat jemand einen Tip, wie man das besser lösen kann.

Viele Grüße
Sabine

 

[Yaslaw]

Unschöner Datenaufbau. Aber gut.

Aber warum arbeitest du mit Variablen? Viel zu kompliziert

update 
  buchungen u,
  (
    select persid, sum(zeit) as total 
    from buchungen 
    where state != 2 
    group by persid
  ) b
set u.zeit = b.total
where u.state = 2
and u.persid = b.persid;

 

[swinkel]

Hallo Yaslaw, vielen Dank für die Antwort. Der Datenaufbau ist nicht genauso, z.B ist u.zeit in echt (8 - b.total). Das mit den Variablen war eine Verzweiflungstat. Habe es heute auch versucht anders zu lösen. Was bei mir und auch bei Dir oben noch fehlt, ist (sum(zeit) von start where state=2 und stop=NULL )

Ungefähr so:

update buchungen u
inner Join
(
Select persID, start from buchungen WHERE state = 2) a     

inner Join
  (
    Select persID, start, Sum(zeit) as total
    from buchungen
    where state = 0
    Group by persID
  ) b
  On b.persID = a.persID
  And b.start= a.start
 
 
set u.zeit = b.total
where u.state = 2
and u.persID = b.persID;

das funktioniert aber noch nicht. Falls Dir auf Anhieb was einfällt, wäre ich dankbar. Ansonsten versuche ich weiter.

Viele Grüße
Sabine

 

[swinkel]

Folgende Lösung scheint zu funktionieren:

Update buchungen u
Inner JOIN

(SELECT a.persID, b.state, b.start, SUM(a.zeit) 'sum' from buchungen as a Inner Join buchungen as b

ON a.persID = b.persID
AND DATE(a.start) = DATE(b.start)
Where b.state = 2

GROUP BY a.persID ) p

ON u.PersID = p.PersID

set u.zeit = (8 - sum) where u.state = 2;

 

[Zvoni]

Ih pfui, Feld-Alias gleich Funktions-Name..... *brrr**schauder*

 

[swinkel]

Ih pfui, Feld-Alias gleich Funktions-Name..... *brrr**schauder*

vielen Dank für den Hinweis, werde ich ändern!