Hours per employee month

Hours per employee and month

A simple join between the period employee header MPProjektBuchungskopf and
the time sheet entries MPProjektBuchungserfassung:

/*
 * MARIProject Version 4.9
 * ProjectManagement.SQLScript4.9.HoursPerEmployeeMonth.sql
 */
 
SELECT 
	SUM(MARIProjectTimeKeepingLines.Quantity) AS HoursUsed, 
	MARIProjectTimeKeepingHeader.EmployeeNumber,
	MARIProjectTimeKeepingHeader.Period
FROM MARIProjectTimeKeepingHeader
INNER JOIN MARIProjectTimeKeepingLines 
	ON MARIProjectTimeKeepingHeader.TimeKeepingHeaderID=MARIProjectTimeKeepingLines.TimeKeepingHeaderID
GROUP BY 
	MARIProjectTimeKeepingHeader.EmployeeNumber, 
	MARIProjectTimeKeepingHeader.Period

result

Additional joins to the master data (employee and project):

/*
 * ProjectManagement Version 4.9
 * ProjectManagement.SQLScript4.9.HoursPerEmployeeMonthProjectEmployee.sql
 */
 
SELECT 
	SUM(MARIProjectTimeKeepingLines.Quantity) AS HoursUsed, 
	MARIProjectTimeKeepingHeader.EmployeeNumber,
	Min(MARIEmployeeMaster.Matchcode) AS EmployeeMatchcode,
	MARIProjectTimeKeepingHeader.Period,
	MARIProjectTimeKeepingLines.ProjectNumber,
	Min(MARIProjectMaster.Matchcode) AS ProjectMatchcode
FROM MARIProjectTimeKeepingHeader
INNER JOIN MARIProjectTimeKeepingLines
	ON MARIProjectTimeKeepingHeader.TimeKeepingHeaderID=MARIProjectTimeKeepingLines.TimeKeepingHeaderID
INNER JOIN MARIProjectMaster 
	ON MARIProjectTimeKeepingLines.ProjectNumber=MARIProjectMaster.ProjectNumber
INNER JOIN MARIEmployeeMaster 
	ON MARIEmployeeMaster.EmployeeNumber=MARIProjectTimeKeepingHeader.EmployeeNumber
GROUP BY 
	MARIProjectTimeKeepingHeader.EmployeeNumber, 
	MARIProjectTimeKeepingHeader.Period,
	MARIProjectTimeKeepingLines.ProjectNumber

result

MARIProject Database Structure