-- Bei Arche Tecklenburg 7060431533 (APP3)
-- Qualifikationen, wenn mehrere vorhanden werden die in eine Spalte geschrieben

SELECT 
    emp.PersonnelNumber AS Personalnummer, 
    CONCAT(p.`FirstName`, ' ', p.`LastName`) as Name,     
    COALESCE(ROUND(con.WeeklyTotalhours, 2), ROUND(con.MonthlyTotalHours * 12 / 52, 2)) as `regelmäßige wö. Arbeitszeit`,
    GROUP_CONCAT(vle.Value SEPARATOR ', ') as Qualifikation
FROM `person` p 
INNER JOIN `employee` emp ON p.`Oid` = emp.`PersonOid` 
LEFT JOIN contract con ON con.Oid = (SELECT c2.Oid FROM contract c2 WHERE c2.EmployeeOid = emp.Oid AND c2.IsActive = 1 
		AND c2.EntryDate <= CURDATE() AND (c2.ContractEndDate IS NULL OR c2.ContractEndDate >= CURDATE())
        ORDER BY c2.EntryDate DESC LIMIT 1)
LEFT JOIN valuelistentry2object v2o ON v2o.ObjectOid = emp.Oid AND v2o.ObjectTid = 2 AND v2o.IsActive = 1
LEFT JOIN valuelistentry vle ON vle.Oid = v2o.ValuelistEntryOid AND vle.Type = 5 AND vle.IsActive = 1
WHERE emp.IsActive = 1 
GROUP BY emp.Oid, emp.PersonnelNumber, p.FirstName, p.LastName, con.WeeklyTotalhours, con.MonthlyTotalHours
ORDER BY p.Lastname;