The article presents some commonly used SQL queries when exportin yearly target data..
Please find all database tables here.
Breakdown of how the annual target is formed
Example of breakdown of annual target formation. Values in HH:MM or HH:MM:SS format:
SELECT u.FirstName || ' ' || u.LastName AS PersonName, minutesToHHMM(p.CalculatedTargetInMinutes) AS CalculatedTargetHHMM, minutesToHHMM(p.ConfiguredTargetInMinutes) AS ConfiguredTargetHHMM, p.WorktimePercentage AS WorkTimePercentage, minutesToHHMMSS(p.TargetReductionFromEventsInMinutes) AS TargetReductionFromEventsHHMMSS, minutesToHHMMSS(p.ContributedMinutesToTarget) AS ContributedToTargetHHMMSS, p.ContributionBeginDate || ' - ' || p.ContributionEndDate AS ContributionPeriod, p.YearTargetPeriodBeginDate || ' - ' || p.YearTargetPeriodEndDate AS YearTargetPeriod FROM PlanningYearTargetBreakdownData p JOIN UserData u ON (u.UserId = p.UserId);
Example of breakdown of annual target formation. Values in decimal hours:
SELECT u.FirstName || ' ' || u.LastName AS PersonName, ROUND(p.CalculatedTargetInMinutes / 60.0, 2) AS CalculatedTargetHours, ROUND(p.ConfiguredTargetInMinutes / 60.0, 2) AS ConfiguredTargetHours, p.WorktimePercentage AS WorkTimePercentage, ROUND(p.TargetReductionFromEventsInMinutes / 60.0, 2) AS TargetReductionFromEventsHours, ROUND(p.ContributedMinutesToTarget / 60.0, 2) AS ContributedToTargetHours, p.ContributionBeginDate || ' - ' || p.ContributionEndDate AS ContributionPeriod, p.YearTargetPeriodBeginDate || ' - ' || p.YearTargetPeriodEndDate AS YearTargetPeriod FROM PlanningYearTargetBreakdownData p JOIN UserData u ON (u.UserId = p.UserId);