Hours per empolyee and contract

Hours per contract

Only customer projects and internal projects with contracts have contracts and
therefore customers. In this case, additionally to the time keeping table
(MPProjektBuchungserfassung) the service time keeping table
(MPProjektLeistungserfassung) comes into account. This table can be linked to
the contract (MPVKVertraege)

/*
 * MARIProject Version 4.9
 * ProjectManagement.SQLScript4.9.HoursPerEmployeeAndContract.sql
 */

SELECT 
	SUM(MARIProjectTimeKeepingLines.Quantity) AS HoursUsed, 
	MARIProjectTimeKeepingHeader.EmployeeNumber,
	MARIProjectTimeKeepingHeader.Period,
	MARIContract.ContractNumber,
	MARIContract.Customer AS CustomerCode
FROM MARIProjectTimeKeepingHeader
INNER JOIN MARIProjectTimeKeepingLines 
	ON MARIProjectTimeKeepingHeader.TimeKeepingHeaderID = MARIProjectTimeKeepingLines.TimeKeepingHeaderID
INNER JOIN MARIProjectTimeKeepingLinesServices 
	ON MARIProjectTimeKeepingLinesServices.TimeSheetEntryID = MARIProjectTimeKeepingLines.DocumentID
	AND MARIProjectTimeKeepingLinesServices.PositionID=0
INNER JOIN MARIContract 
	ON MARIContract.ContractID = MARIProjectTimeKeepingLinesServices.ContractID
GROUP BY 
	MARIProjectTimeKeepingHeader.EmployeeNumber, 
	MARIProjectTimeKeepingHeader.Period,
	MARIContract.ContractNumber, 
	MARIContract.Customer

result

MARIProject Database Structure