Inaccurate Query Results
Posted on 2008-10-21
I am trying to develop an employee timesheet status report to determine the status of employee timesheets by month and week (timesheets are submitted on a weekly basis) and whether or not a time sheet has even been started using the query pasted in below.
The issue I am experiencing is that whenever a timesheet is listed as having a status of "Not Found," an approver name is not listed. Is there a way to resolve this so that the approver name is listed no matter what the status of the timesheet?
Thanks in advance for any help!
SELECT dbo.PJBILLABLEHOURS.TimePeriodDate, dbo.PJEMPLOY.emp_name, dbo.PJEMPLOY.emp_status, APPROVER.emp_name AS ApproverName,
CASE dbo.PJLABHDR.le_status WHEN 'A' THEN 'Approved, Needs to be Posted' WHEN 'C' THEN 'Needs Approval' WHEN 'I' THEN 'Incomplete' WHEN 'M'
THEN 'Missing' WHEN 'P' THEN 'Posted' WHEN 'R' THEN 'Needs to Be Submitted' WHEN 'X' THEN 'Posted with Corrections' ELSE 'Missing' END AS TimesheetStatus
FROM dbo.PJEMPLOY AS APPROVER RIGHT OUTER JOIN
dbo.PJLABHDR ON APPROVER.employee = dbo.PJLABHDR.Approver RIGHT OUTER JOIN
dbo.PJBILLABLEHOURS INNER JOIN
dbo.PJEMPLOY ON dbo.PJEMPLOY.emp_status = 'A' ON dbo.PJLABHDR.pe_date = dbo.PJBILLABLEHOURS.TimePeriodDate AND
dbo.PJLABHDR.employee = dbo.PJEMPLOY.employee
WHERE (YEAR(dbo.PJBILLABLEHOURS.TimePeriodDate) = YEAR(GETDATE())) AND (MONTH(dbo.PJBILLABLEHOURS.TimePeriodDate)
ORDER BY dbo.PJEMPLOY.emp_name, dbo.PJBILLABLEHOURS.TimePeriodDate