Hi, all
I need to get hourly rate for the employee based on their job position(each
position has 3 differet rates in either year of 2004 or 2005), date worked ( in
2004 or in 2005), pay type. I have the report built with MS Access and the
query is as follows:
SELECT [LName] & ", " & [FName] AS Employee, tblTimesheet.PayType,
tblTimesheet.Hours, tblTimesheet.dateWorked, tblTimesheet.caseID,
tblPosition.position, IIf([dateWorked]>#1/26/2004#,[RE05],[RE04]) AS RE,
IIf([dateWorked]>#1/26/2004#,[OT05],[OT04]) AS OT,
IIf([dateWorked]>#1/26/2004#,[TR05],[TR04]) AS TR,
IIf([PayType]="RE",[RE],IIf([PayType]="OT",[OT],[TR])) AS HourRate,
[HourRate]*[Hours] AS SubTotal
FROM (tblEmpInfo INNER JOIN tblTimesheet ON tblEmpInfo.empID =
tblTimesheet.Employee) INNER JOIN tblPosition ON tblTimesheet.PositionID =
tblPosition.positionID
WHERE (((tblTimesheet.dateWorked) Between [forms].[frmRunRpt].[BeginningDate]
And [forms].[frmRunRpt].[EndingDate]));

Now I have created a simple query using the Coldfusion Report Query Builder
try to generate a report like the one I did with Access, but I don?t know how
to write cfquery (the IIF portion) which will get the hourly rate . Here is the
query:
SELECT tblEmpInfo.LName, tblEmpInfo.FName, tblPosition.jobPosition,
tblTimesheet.PayType, tblTimesheet.dateWorked, tblTimesheet.caseID,
tblTimesheet.Hours, tblEmpInfo.empID
FROM tblTimesheet, tblEmpInfo, tblPosition
WHERE tblTimesheet.Employee = tblEmpInfo.empID
AND tblTimesheet.PositionID = tblPosition.positionID
ORDER BY LName, FName, jobPosition, PayType

Any help will be greatly appreciated.