gistfile1.txt

CREATE FUNCTION GetProgressionReviewHours(@AsOfDate DateTime) 
 RETURNS @Hours TABLE (Employee_Id int,
  			EmployeeContract_Id int,
				ProgressionReviewPeriod int,
				WorkedHours numeric(18,2),
				TotalHours numeric(18,2))
				
BEGIN

INSERT INTO @Hours
SELECT 	ti.Employee_Id,
		lsc.EmployeeContract_Id,	/* Last contract with a Progression review reset */
		lsc.ProgressionReviewPeriod,
		SUM(ti.WorkedHours) AS WorkedHours,
		SUM(ti.TotalHours) AS TotalHours
FROM 
(SELECT ts.Employee_Id,
		tii.ItemDate,
		CASE WHEN Activity_ID IN (1,2) THEN Hours_Reg+Hours_Ovt ELSE 0 END AS WorkedHours,
		CASE WHEN Activity_ID NOT IN (7,8) THEN Hours_Reg+Hours_Ovt ELSE 0 END AS TotalHours
	FROM tblPPTimeItem tii
		JOIN tblPPTimesheet ts ON tii.PPTimesheet_Id = ts.Id
		INNER HASH JOIN viewEmployeeContracts ecs ON ecs.Employee_Id = ts.Employee_Id AND tii.ItemDate BETWEEN ecs.STartDate AND Ecs.EndDate  /* 2008-11  Slowed to a crawl, added hash*/
		JOIN tblEmployeeContract ec ON ec.Id = ecs.EmployeeContract_Id
		JOIN tblEmployeeContractTerm ct ON ct.Id = ec.ContractTerm_Id
	WHERE tii.ItemDate <= @AsOfDate AND ct.Code = 'PERM'
	UNION ALL
	SELECT  em.Id AS Employee_Id,
		CAST('2005/09/30' AS DateTime) AS ItemDate,
		em.OpeningReviewHours AS WorkedHours,		
		em.OpeningReviewHours AS TotalHours
	FROM tblEmployee em 
	) ti
	-- Get the all of the most recent contracts after the last pay grade step change
	JOIN (SELECT Employee_Id, ec.Id AS EmployeeContract_Id, 
			COALESCE(ec.RetroactiveDate,ec.Contract_Date) AS StartDate,
			lad.ProgressionReviewPeriod
		FROM tblEmployeeContract ec
			JOIN tblJobClass jc ON jc.Id = ec.JobClass_Id
			JOIN tblPayGrade pg ON pg.Id = jc.PayGrade_Id
			JOIN tblLabourAgreement la ON la.Id = ec.LabourAgreement_Id 
			JOIN tblLAbourAgreementDetail lad ON lad.LabourAgreement_id = la.id 
								AND NOT EXISTS(SELECT * FROM tblLabourAgreementDetail lad2 
										WHERE lad2.LabourAgreement_Id = la.Id AND lad2.EffectiveDate > lad.EffectiveDate)
		WHERE NOT EXISTS(SELECT * FROM tblEmployeeContract ec2 
					WHERE ec2.Employee_Id = ec.Employee_Id
					  AND COALESCE(ec2.RetroactiveDate,ec2.Contract_Date) > COALESCE(ec.RetroactiveDate,ec.Contract_Date) 
					  AND ec2.ReviewReset_Id = 1 ) 
		AND ec.ReviewReset_Id = 1
		AND lad.ProgressionReviewFlag = 1
		AND pg.LevelCount > ec.PayGradeStep -- Only include employees who have not reached their maximums
		) lsc ON lsc.Employee_Id = ti.Employee_Id and ti.ItemDate >= lsc.StartDate
	-- Get the employee status for all dates and correlate with hours worked.  This allows us to filter out light duty
	JOIN viewEmployeeStatuses es ON es.Employee_Id = ti.Employee_Id AND (ti.ItemDate BETWEEN es.StartDate AND es.EndDate) AND es.EmployeeStatus_ID = 12 -- Regular Duty
GROUP BY ti.Employee_Id, lsc.EmployeeContract_Id, lsc.ProgressionReviewPeriod

RETURN
END