SELECT Anr.Text AS Anrede,
stamm.mavname AS Vorname,
stamm.maname AS Name,
Date_Format(x.lsdate, '%d.%m.%Y') AS Datum,
x.sumTheorie AS Theorie,
CASE WHEN x.sumFahrs_Mofa > 0 THEN x.sumFahrs_Mofa ELSE '-' END AS Fahrs_Mofa,
CASE WHEN x.sumFahrs_AM > 0 THEN x.sumFahrs_AM ELSE '-' END AS Fahrs_AM,
CASE WHEN x.sumFahrs_A1 > 0 THEN x.sumFahrs_A1 ELSE '-' END AS Fahrs_A1,
CASE WHEN x.sumFahrs_A > 0 THEN x.sumFahrs_A ELSE '-' END AS Fahrs_A,
CASE WHEN x.sumFahrs_A2 > 0 THEN x.sumFahrs_A2 ELSE '-' END AS Fahrs_A2,
CASE WHEN x.sumFahrs_B > 0 THEN x.sumFahrs_B ELSE '-' END AS Fahrs_B,
CASE WHEN x.sumFahrs_BE > 0 THEN x.sumFahrs_BE ELSE '-' END AS Fahrs_BE,
CASE WHEN x.sumPraPru_AM > 0 THEN x.sumPraPru_AM ELSE '-' END AS Prax_AM,
CASE WHEN x.sumPraPru_A1 > 0 THEN x.sumPraPru_A1 ELSE '-' END AS Prax_A1,
CASE WHEN x.sumPraPru_A > 0 THEN x.sumPraPru_A ELSE '-' END AS Prax_A,
CASE WHEN x.sumPraPru_A2 > 0 THEN x.sumPraPru_A2 ELSE '-' END AS Prax_A2,
CASE WHEN x.sumPraPru_B > 0 THEN x.sumPraPru_B ELSE '-' END AS Prax_B,
CASE WHEN x.sumPraPru_BE > 0 THEN x.sumPraPru_BE ELSE '-' END AS Prax_BE,
CASE WHEN x.sumUrlaub > 0 THEN 'U' ELSE '-' END AS Urlaub,
CASE WHEN x.sumKrank > 0 THEN 'K' ELSE '-' END AS Krank,
CASE WHEN x.SumFeiertag > 0 THEN 'F' ELSE '-' END AS Feiertag
FROM (
SELECT leist.id_nrmitar,
leist.lsdate,
SUM(ltheo.dauer) AS sumTheorie,
SUM(CASE WHEN lfahrs.is_fsklnr = 27
THEN lfahrs.lsdauer
ELSE 0 END ) AS sumFahrs_Mofa,
SUM(CASE WHEN lfahrs.is_fsklnr = 50
THEN lfahrs.lsdauer
ELSE 0 END ) AS sumFahrs_AM,
SUM(CASE WHEN lfahrs.is_fsklnr = 13
THEN lfahrs.lsdauer
ELSE 0 END ) AS sumFahrs_A1,
SUM(CASE WHEN lfahrs.is_fsklnr = 51
THEN lfahrs.lsdauer
ELSE 0 END ) AS sumFahrs_A,
SUM(CASE WHEN lfahrs.is_fsklnr = 53
THEN lfahrs.lsdauer
ELSE 0 END ) AS sumFahrs_A2,
SUM(CASE WHEN lfahrs.is_fsklnr = 15
THEN lfahrs.lsdauer
ELSE 0 END ) AS sumFahrs_B,
SUM(CASE WHEN lfahrs.is_fsklnr = 52
THEN lfahrs.lsdauer
ELSE 0 END ) AS sumFahrs_BE,
SUM(CASE WHEN lprapru.id_fsklnr = 50
THEN lprapru.lsdauer
ELSE 0 END ) AS sumPraPru_AM,
SUM(CASE WHEN lprapru.id_fsklnr = 13
THEN lprapru.lsdauer
ELSE 0 END ) AS sumPraPru_A1,
SUM(CASE WHEN lprapru.id_fsklnr = 51
THEN lprapru.lsdauer
ELSE 0 END ) AS sumPraPru_A,
SUM(CASE WHEN lprapru.id_fsklnr = 53
THEN lprapru.lsdauer
ELSE 0 END ) AS sumPraPru_A2,
SUM(CASE WHEN lprapru.id_fsklnr = 15
THEN lprapru.lsdauer
ELSE 0 END ) AS sumPraPru_B,
SUM(CASE WHEN lprapru.id_fsklnr = 52
THEN lprapru.lsdauer
ELSE 0 END ) AS sumPraPru_BE,
SUM(CASE WHEN lsonst.id_lsart = 38
THEN lsonst.lsdauer
ELSE 0 END ) AS sumUrlaub,
SUM(CASE WHEN lsonst.id_lsart = 40
THEN lsonst.lsdauer
ELSE 0 END ) AS SumKrank,
SUM(CASE WHEN lsonst.id_lsart = 42
THEN lsonst.lsdauer
ELSE 0 END ) AS SumFeiertag
FROM davidw2000._mtbl_leist AS leist
LEFT JOIN davidw2000._mtbl_leistsonstiges AS lsonst ON leist.id_flleist = lsonst.id_flleist
LEFT JOIN davidw2000._mtbl_leistunterricht AS ltheo ON leist.id_flleist = ltheo.id_flleist
LEFT JOIN davidw2000._kde_leist AS lkde ON leist.id_flleist = lkde.id_flleist
LEFT JOIN davidw2000._kde_leistfahrstunden AS lfahrs ON lkde.id_kdleist = lfahrs.id_kdleist AND lfahrs.isUnterschrift = True
LEFT JOIN davidw2000._kde_leistpruefungpraxis AS lprapru ON lkde.id_kdleist = lprapru.id_kdleist AND lprapru.isUnterschrift = True
WHERE leist.id_nrmitar = 1
AND leist.lsdate BETWEEN '2017-06-01 00:00:00' AND '2017-06-30 23:59:59'
GROUP BY
leist.id_nrmitar,
leist.lsdate
ORDER BY leist.lsdate DESC) AS x
INNER JOIN davidw2000._mtbl_stammdaten AS Stamm ON x.id_nrmitar = Stamm.id_mitar
INNER JOIN davidw2000._sys_anrede AS Anr ON Stamm.id_anrnr = Anr.id_anrnr[sql]
Also so hätte ich richtige Ergebnissen wenn ich aber Tabelle "_mtbl_leist" nicht mit "_kde_leist" verbinde bekomme ich die Werte aus der Tabelle "_kde_leistfahrstunden" und "_kde_leistpruefungpraxis" nicht, wie unten sind die Theoriezeiten richtig
[ATTACH=full]65214[/ATTACH]
[code=SQL]
SELECT Anr.Text AS Anrede,
stamm.mavname AS Vorname,
stamm.maname AS Name,
Date_Format(x.lsdate, '%d.%m.%Y') AS Datum,
x.sumTheorie AS Theorie,
CASE WHEN x.sumUrlaub > 0 THEN 'U' ELSE '-' END AS Urlaub,
CASE WHEN x.sumKrank > 0 THEN 'K' ELSE '-' END AS Krank,
CASE WHEN x.SumFeiertag > 0 THEN 'F' ELSE '-' END AS Feiertag
FROM (
SELECT leist.id_nrmitar,
leist.lsdate,
SUM(ltheo.dauer) AS sumTheorie,
SUM(CASE WHEN lsonst.id_lsart = 38
THEN lsonst.lsdauer
ELSE 0 END ) AS sumUrlaub,
SUM(CASE WHEN lsonst.id_lsart = 40
THEN lsonst.lsdauer
ELSE 0 END ) AS SumKrank,
SUM(CASE WHEN lsonst.id_lsart = 42
THEN lsonst.lsdauer
ELSE 0 END ) AS SumFeiertag
FROM davidw2000._mtbl_leist AS leist
LEFT JOIN davidw2000._mtbl_leistsonstiges AS lsonst ON leist.id_flleist = lsonst.id_flleist
LEFT JOIN davidw2000._mtbl_leistunterricht AS ltheo ON leist.id_flleist = ltheo.id_flleist
WHERE leist.id_nrmitar = 1
AND leist.lsdate BETWEEN '2017-06-01 00:00:00' AND '2017-06-30 23:59:59'
GROUP BY
leist.id_nrmitar,
leist.lsdate
ORDER BY leist.lsdate DESC) AS x
INNER JOIN davidw2000._mtbl_stammdaten AS Stamm ON x.id_nrmitar = Stamm.id_mitar
INNER JOIN davidw2000._sys_anrede AS Anr ON Stamm.id_anrnr = Anr.id_anrnr
[SQL]