SQL-Query: Lücken füllen

[Levis]

Hi,

ich sitze jetzt schon ewig dran und bekomms einfach nicht hin

Ich habe eine Tabelle zur Zeiterfassung:
times(uid, start_time, end_time, task_id)
uid ist der identifizierende Username.

Jetzt brauche ich eine Übersicht, wieviel Sekunden ein User pro Monat an einem Task gearbeitet hat. Dies erreiche ich folgendermaßen:

SELECT DATE_FORMAT(start_time, '%Y-%c') AS time, 
    times.uid, 
    times.task_id, 
    SUM(1.0 * time_to_sec(end_time) - time_to_sec(start_time)) AS duration 
FROM ts_times times 
WHERE times.proj_id=34 AND 
   times.start_time>='2008-03-01' AND times.end_time<'2008-06-01' 
GROUP BY time, uid, task_id
ORDER BY time, uid, task_id

Jetzt soll die Übersicht allerdings für einen späteren automatischen Export nach Excel erweitert werden. Und zwar sollen immer alle Tasks pro Monat jedem User zugewiesen werden, an denen er irgendwann mal in dem angegebenen Zeitraum gearbeitet hat (unten rot markiert). Außerdem soll jeder User in jedem Monat auftreten, falls er mind. einmal in irgendeinem Monat aufgetreten ist (blau markiert).

Beispielausgabe mit aktueller Abfrage:
time | uid | task_id | duration
2008-3 | user1 | 109 | 126900.0
2008-3 | user2 | 107 | 520380.0
2008-3 | user3 | 106 | 36000.0
2008-3 | user3 | 107 | 184800.0
2008-3 | user4 | 106 | 10800.0

2008-4 | user2 | 107 | 114300.0
2008-4 | user3 | 107 | 55500.0

2008-5 | user3 | 107 | 76800.0
2008-5 | user5 | 107 | 65700.0

Beispielausgabe mit gewünschter Abfrage:
time | uid | task_id | duration
2008-3 | user1 | 109 | 126900.0
2008-3 | user2 | 107 | 520380.0
2008-3 | user3 | 106 | 36000.0
2008-3 | user3 | 107 | 184800.0
2008-3 | user4 | 106 | 10800.0
2008-3 | user5 | 107 | 0.0

2008-4 | user1 | 109 | 0.0
2008-4 | user2 | 107 | 114300.0
2008-4 | user3 | 106 | 0.0
2008-4 | user3 | 107 | 55500.0
2008-4 | user4 | 106 | 0.0
2008-4 | user5 | 107 | 0.0

2008-5 | user1 | 109 | 0.0
2008-5 | user2 | 107 | 0.0
2008-5 | user3 | 106 | 0.0
2008-5 | user3 | 107 | 76800.0
2008-4 | user4 | 106 | 0.0
2008-5 | user5 | 107 | 65700.0

Irgendwer eine Idee? Wahrscheinlich braucht man eine zweite SELECT-Abfrage, die man ins FROM einbaut und dann mit JOIN verbindet.. Aber ich habe keine Lösung gefunden

Vielen Dank im Voraus für alle Tipps

[Levis]

Ich zweifel schon langsam, ob das überhaupt mit einem SQL-Query abzuradeln ist...
Ich hab quasi ein Muster für je eine Zeiteinheit (z.B. Monat). Wenn in einem Monat ein User gar für gar keinen oder nicht für einen bestimmten Task gearbeitet hat, soll als Duration 0 / NULL angegeben werden.

hm, ich komm einfach nicht drauf

[Cybergreek]

Ich hoffe, Dir bringt ein Ansatz erstmal was

Ich denke, Du kannst Dein Problem damit lösen, dass Du Deine Abfrage mit einer zweiten verknüpfst. Also (hier kommt der Ansatz):

PHP-Code:

SELECT ...deineAbfrage...
UNION
SELECT ...
FROM ...
WHERE NOT EXISTS (...eine Abwandlung der ersten Abfrage...)

ORDER BY time, uid, task_id 


Ich hoffe, die Idee ist ein bisschen rübergekommen?

[steffenk]

also ich denk doch das das mit einer Query geht

also ich würde so vorgehen

SELECT
uid, DATE_FORMAT(start_time,'%d.%m.%Y') as Datum,
month(start_time) as Monat
sum(UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time) as Sekunden,
task_id
FROM ts_times times
group by Monat, task_id,uid

[Levis]

Hi,

danke für Eure Antworten!

steffen, deine Abfrage bringt dasselbe Ergebnis wie meine...
cybergreek: hm... ich befürchte ich habs noch nicht so ganz morgen werd ich darüber mal nachdenken dankeschön!

[Cybergreek]

Zitat von Levis

cybergreek: hm... ich befürchte ich habs noch nicht so ganz ...

Sag das doch gleich, ich drück mich manchmal undeutlich aus

Also es geht in diese Richtung:

PHP-Code:

SELECT DATE_FORMAT(start_time, '%Y-%c') AS time, 
    times.uid, 
    times.task_id, 
    SUM(1.0 * time_to_sec(end_time) - time_to_sec(start_time)) AS duration 
FROM test times 
WHERE times.proj_id=34 AND 
   times.start_time>='2008-03-01' AND times.end_time<'2008-06-01' 
GROUP BY time, uid, task_id

UNION 

SELECT DISTINCT DATE_FORMAT(start_time, '%Y-%c') AS time, 
  user.uid,
  times.task_id,
  0
FROM test times,
 (SELECT DISTINCT DATE_FORMAT(start_time, '%Y-%c') FROM test) months,
 (SELECT DISTINCT uid FROM test) user
WHERE times.proj_id=34 
   AND times.start_time>='2008-03-01' AND times.end_time<'2008-06-01' 
   AND NOT EXISTS (select * from test times2 where user.uid = times2.uid and DATE_FORMAT(times.start_time, '%Y-%c') = DATE_FORMAT(times2.start_time, '%Y-%c'))
   AND times.task_id in (select task_id from test times3 where times3.start_time>='2008-03-01' AND times3.end_time<'2008-06-01' AND times3.uid = user.uid)
    
ORDER BY time, uid, task_id 


Da ist noch einiges an Optimierungsbedarf da, aber für meinen ersten schnellen Tests schien es korrekt zu laufen.

[steffenk]

um die Null-Zeiten reinzubekommen wirst Du wohl um einen Join nicht drumrumkommen, da user, die keinen Einterag haben auch nicht in der Tabelle auftauchen.

[Levis]

Cybergreeks Ansatz werde ich später versuchen umzubauen. Bis jetzt braucht er 45 Sekunden und das Ergebnis ist auch noch nicht korrekt..

JOINS.. Ich habe schon alles probiert.. LEFT, RIGHT, OUTER..

Danke aber Euch beiden

[steffenk]

mach einen kleinen Dump von den 2 Tabellen (zeittabelle und user) und ich bastle was, bin zu faul das alles manuell zu erstellen