18 lines
1.1 KiB
Plaintext
18 lines
1.1 KiB
Plaintext
|
-- 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;
|