Fehlerbericht/queries/status_tag.sql

SELECT sm.datum, sm.kunde, sm.aufgabe, sm.anzahl,
IFNULL(s7.anzahl, 'X') AS anzahl_7, 
IFNULL(s6.anzahl, 'X') AS anzahl_6,
IFNULL(s5.anzahl, 'X') AS anzahl_5,
IFNULL(s4.anzahl, 'X') AS anzahl_4,
IFNULL(s3.anzahl, 'X') AS anzahl_3,
IFNULL(s2.anzahl, 'X') AS anzahl_2,
IFNULL(s1.anzahl, 'X') AS anzahl_1

FROM `status_meldung` sm
LEFT JOIN status_meldung AS s7 ON s7.datum = date_add(sm.datum, INTERVAL -7 DAY) AND s7.kunde = sm.kunde AND s7.aufgabe = sm.aufgabe
LEFT JOIN status_meldung AS s6 ON s6.datum = date_add(sm.datum, INTERVAL -6 DAY) AND s6.kunde = sm.kunde AND s6.aufgabe = sm.aufgabe
LEFT JOIN status_meldung AS s5 ON s5.datum = date_add(sm.datum, INTERVAL -5 DAY) AND s5.kunde = sm.kunde AND s5.aufgabe = sm.aufgabe
LEFT JOIN status_meldung AS s4 ON s4.datum = date_add(sm.datum, INTERVAL -4 DAY) AND s4.kunde = sm.kunde AND s4.aufgabe = sm.aufgabe
LEFT JOIN status_meldung AS s3 ON s3.datum = date_add(sm.datum, INTERVAL -3 DAY) AND s3.kunde = sm.kunde AND s3.aufgabe = sm.aufgabe
LEFT JOIN status_meldung AS s2 ON s2.datum = date_add(sm.datum, INTERVAL -2 DAY) AND s2.kunde = sm.kunde AND s2.aufgabe = sm.aufgabe
LEFT JOIN status_meldung AS s1 ON s1.datum = date_add(sm.datum, INTERVAL -1 DAY) AND s1.kunde = sm.kunde AND s1.aufgabe = sm.aufgabe

WHERE sm.datum = '2017-06-06'




SELECT DISTINCT a.datum, a.wochentag, a.feiertag, k.kunde, ka.aufgabe, k.system, k.bundesland, ka.start_soll,
	case
	  when a.wochentag = 'Mo' and ka.ende_mo IS NOT NULL then ka.ende_mo
	  when a.wochentag = 'Di' and ka.ende_di IS NOT NULL then ka.ende_di
	  when a.wochentag = 'Mi' and ka.ende_mi IS NOT NULL then ka.ende_mi
	  when a.wochentag = 'Do' and ka.ende_do IS NOT NULL then ka.ende_do
	  when a.wochentag = 'Fr' and ka.ende_fr IS NOT NULL then ka.ende_fr
	  when a.wochentag = 'Sa' and ka.ende_sa IS NOT NULL then ka.ende_sa
	  when a.wochentag = 'So' and ka.ende_so IS NOT NULL then ka.ende_so
	  else ka.ende_soll
	end as 'ende_soll',
	ka.woche,  
	case
	 when ka.woche = 'Mo-Fr' then a.mofr
	 when ka.woche = 'Mo-Sa' then a.mosa
	 when ka.woche = 'Mo-So' then a.moso
	 when ka.woche = 'Di-So' then a.diso
	 else 1
	end
	* ka.aktiv * if(a.datum >= ka.erster_status, 1, 0)  as 'anstehend'
								
FROM kunden k
INNER JOIN arbeitstage a USING (bundesland)
INNER JOIN kunden_aufgabe ka USING (kunde)

WHERE a.datum <= date_add(now(), INTERVAL 1 DAY) 
AND a.datum >= date_add(now(), INTERVAL -20 DAY)
							
							
							
							
							LEFT JOIN status_meldung sm USING (datum, kunde)
							LEFT JOIN status_kommentar sk ON sm.kommentar_id = sk.id
							LEFT JOIN status_kommentar as sk2 ON a.datum = sk2.datum AND k.kunde = sk2.kunde

							WHERE {$datum} AND {$datum_bis} AND {$kunde} AND a.datum <= now() AND k.erster_status <= a.datum
							AND k.aktiv = 1
							ORDER BY a.datum, k.ende_soll
							
							
							end * if(sk.status <= 1, 0, 1) * if(sm.anzahl > 0 or (sm.anzahl is null and now() > date_add(concat(a.datum, ' ', k.ende_soll), INTERVAL 1 HOUR)), 1, 0) as fehler,
								if(sm.anzahl is null and now() > date_add(concat(a.datum, ' ', k.ende_soll), INTERVAL 1 HOUR), 1, 0) as fehlend,