Challenges developing timesheet status report query.
Posted on 2008-10-20
I am trying to develop an employee time sheet status report to determine the status of employee time sheets by month and week (time sheets are submitted on a weekly basis) and whether or not a time sheet has even been started using the query pasted in below.
The challenge is determining whether or not a time sheet for a particular period is even started. I am trying to connect the employee tables to get employee/time sheet approver names and the PJBILLABLEHOURS table, which contains a list of all available time periods, to the PJLABHDR table which contains time sheet information, in such a way so that if PJLABHDR does not contain information for a particular employee for a particular time period that it records a time sheet status of "missing."
Any insights would be appreciated!
dbo.PJLABHDR.employee, dbo.PJEMPLOY.emp_name, dbo.PJEMPLOY.emp_status, dbo.PJLABHDR.Approver,
PJEMPLOY_1.emp_name AS ApproverName, dbo.PJBILLABLEHOURS.TimePeriodDate,
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' END AS TimesheetStatus,
dbo.PJLABHDR.le_status, dbo.PJLABHDR.le_type, CASE DATEPART(mm, dbo.PJLABHDR.pe_date)
WHEN '01' THEN 'January' WHEN '02' THEN 'February' WHEN '03' THEN 'March' WHEN '04' THEN 'April' WHEN '05' THEN 'May' WHEN '06' THEN 'June'
WHEN '07' THEN 'July' WHEN '08' THEN 'August' WHEN '09' THEN 'September' WHEN '10' THEN 'October' WHEN '11' THEN 'November' WHEN '12' THEN
'December' ELSE 'Unknown' END AS PeriodMonth
FROM dbo.PJLABHDR INNER JOIN
dbo.PJEMPLOY AS PJEMPLOY_1 ON dbo.PJLABHDR.Approver = PJEMPLOY_1.employee RIGHT OUTER JOIN
dbo.PJBILLABLEHOURS ON dbo.PJLABHDR.pe_date = dbo.PJBILLABLEHOURS.TimePeriodDate RIGHT OUTER JOIN
dbo.PJEMPLOY ON dbo.PJLABHDR.employee = dbo.PJEMPLOY.employee
WHERE (dbo.PJLABHDR.le_status <> 'X') AND (dbo.PJLABHDR.le_status <> 'A') AND (dbo.PJLABHDR.le_status <> 'P') AND (dbo.PJEMPLOY.emp_status = 'A')
AND (YEAR(dbo.PJBILLABLEHOURS.TimePeriodDate) = YEAR(GETDATE())) AND (MONTH(dbo.PJBILLABLEHOURS.TimePeriodDate)
ORDER BY dbo.PJEMPLOY.emp_name, dbo.PJBILLABLEHOURS.TimePeriodDate