asked on

Challenges developing timesheet status report query.

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!

SELECT DISTINCT
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)
= @TestMonthParameter)
ORDER BY dbo.PJEMPLOY.emp_name, dbo.PJBILLABLEHOURS.TimePeriodDate

Steve Hogg

Looks like one timesheet per Calendar Month? If so, create a subquery with al 12 calendar months. Use the DATENAME function to return the Month Name so you can skip the CASE statement based on Month Number.
Your expectation is to create a report with each timesheet period, and all the information associated with each employee -if it exists - per timesheet? You could create a view with 12 rows, one for each month. LEFT JOIN this to your employee timesheets table so you will return one MONTH_TIMESHEET row per employee whether it exists or not.
SELECT DATENAME(month, <<date>>)

rdracer58

ASKER

There is one timesheet per week, starting on Sunday (or the first of the month, if it is not a Sunday) and ending on Saturday (or the last day of the month, if it is not a Saturday). The expectation is to create a report by selected month (hence the CASE statement) that will detail timesheet status information by employee for each timesheet in the given month.

The challenge is the PJLABHDR table gives timesheet statuses per the CASE dbo.PJLABHDR.le_status statement, but we also need to know if there is no timesheet information for a given week/timesheet for a particular employee. Hence the need to determine if there is no information for a particular week/pay period in the PJLABHDR table for a given employee/approver from the PJEMPLOY and PJEMPLOY_1 tables for a given week in the PJBILLABLEHOURS table (meaning there is no information for a particular employee and timesheet in the PJLABHDR table). If a timesheet does not exist for a particular employee/week, we need to flag it as "missing."

Any insights?

Steve Hogg

I would create a table which holds all Timesheets; ie TIMESHEET_DEF. This table would server as a calendar relationshiip between the PJBILLABLEHOURS.TimePeriodDate and a Timesheet for each employee. My assumption is each TimePeriodDate would match to a DateRangeFrom and DateRangeTo on the TIMESHEET_DEF table.

CREATE TABLE [dbo].[TIMESHEET_DEF](
[TIMESHEETID] [int] NOT NULL,
[TSMONTH] [int] NOT NULL,
[CAL_YEAR] [int] NOT NULL,
[TSWEEK] [int] NOT NULL,
[BEGIN_DT] [datetime] NOT NULL,
[END_DT] [datetime] NOT NULL
) ON [PRIMARY]
Your join would grab all Timesheets from a given period and LEFT JOIN to the PJBILLABLEHOURS table on TimePeriodDate between BEGIN_DT and END_DT. If the TimePeriodDate can be found multiple times in a Timesheet range then I would write a subquery to Sum by TSWEEK.
Does that help? Are we on the right track?

rdracer58

ASKER

I am not sure I fully understand. The PJBILLABLEHOURS table already holds the timesheet start dates and I am trying to compare whether there is a timesheet with the same pe_date in the PJLABHDR table for a specified employee.

Steve Hogg

Sorry, I see now.
dbo.PJBILLABLEHOURS.TimePeriodDate (One Row per TimeSheet Period)
dbo.PJLABHDR (Relationship between Employee and Timesheet)
When you RIGHT OUTER JOIN dbo.PJBILLABLEHOURS ON dbo.PJLABHDR.pe_date = dbo.PJBILLABLEHOURS.TimePeriodDate this will return all rows from the PJLABHDR table and only those rows from the PJBILLABLEHOURS table with matches. I believe you mean to do a LEFT OUTER JOIN here to pull all rows from PJBILLABLEHOURS, this will give you all of available timesheets and match all matches in PJLABHDR.
If you are performing a CASE on PJLABHDR.le_status, it would be NULL where no matching record was found. Is 'M' a real status or one you want to assign? If it is real, that means a match is found but ???

Steve Hogg

Crud, what an idiot. I just setup the entire SQL and ran it. The RIGHT JOIN is fine. Not understanding the data really is crippling for an old guy like me. Sry. So are you getting blank data in the PJLABHDR columns on missing timesheet rows?

rdracer58

ASKER

I checked my query and according to the Query Designer in SQL Server Reporting Services, the right outer join does indicate a selection of all rows from the PJBILLABLEHOURS table. Any insights into why this won't be bringing back NULL values for PJLABHDR where there is not a match to the date in PJBILLABLE HOURS?

Yes, "M" in some instances is a real status for "missing," but the timekeeping system does not always record this as the case. For example, there are employees who have timesheets ending this last Saturday who have not even started a timesheet for the period (hence, there should be a "NULL" in the PJLABHDR table for this particular timesheet for them), however, the query does not display this as such.

rdracer58

ASKER

No, I'm currently not getting blank data on the PJLABHDR columns. For clarification, I have pasted in the currently query I am trying to run. I did away with the month CASE statement as it is unnecessary in the report (I use a separate dataset and parameter to allow the report user to select a month).

SELECT DISTINCT
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
FROM dbo.PJLABHDR RIGHT OUTER JOIN
dbo.PJEMPLOY AS PJEMPLOY_1 ON dbo.PJLABHDR.Approver = PJEMPLOY_1.employee RIGHT OUTER JOIN
dbo.PJEMPLOY ON dbo.PJLABHDR.employee = dbo.PJEMPLOY.employee RIGHT OUTER JOIN
dbo.PJBILLABLEHOURS ON dbo.PJLABHDR.pe_date = dbo.PJBILLABLEHOURS.TimePeriodDate
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)
= @TestMonthParameter)
ORDER BY dbo.PJEMPLOY.emp_name, dbo.PJBILLABLEHOURS.TimePeriodDate

rdracer58

ASKER

Something that appears to be an issue is that it seems like there needs to be a table, or a series of tables, with the employee ID, name, and time period date (almost a need for a separate table for each employee with said information) so it can be compared to the PJLABHDR table.

The PJEMPLOY, PJEMPLOY_1, and PJBILLABLEHOURS do not have any way to join to one another, but rather are connected to one another via the PJLABHDR table. Could this be the cause of why I am not receiving any null rows from PJLABHDR?

Steve Hogg

I think this simplified version will select the right data. It assumes one row per employee in the PJEMPLOY table and one row per timesheet period in the PJBILLABLEHOURS table.

-- Simplified
SELECT
dbo.PJBILLABLEHOURS.TimePeriodDate,
dbo.PJEMPLOY.emp_name, 
dbo.PJEMPLOY.emp_status, 
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 'Not Found' 
END AS TimesheetStatus
FROM dbo.PJBILLABLEHOURS
INNER JOIN dbo.PJEMPLOY
	ON 1 = 1
RIGHT JOIN dbo.PJLABHDR
	ON dbo.PJLABHDR.pe_date = dbo.PJBILLABLEHOURS.TimePeriodDate

Open in new window

Steve Hogg

I modified the JOIN and WHERE clause. Not sure about the Approver column?

-- Simplified
SELECT
dbo.PJBILLABLEHOURS.TimePeriodDate,
dbo.PJEMPLOY.emp_name, 
dbo.PJEMPLOY.emp_status, 
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 'Not Found' 
END AS TimesheetStatus
FROM dbo.PJBILLABLEHOURS
INNER JOIN dbo.PJEMPLOY
	ON dbo.PJEMPLOY.emp_status = 'A'
RIGHT JOIN dbo.PJLABHDR
	ON dbo.PJLABHDR.pe_date = dbo.PJBILLABLEHOURS.TimePeriodDate
	AND (dbo.PJLABHDR.le_status <> 'X') 
	AND (dbo.PJLABHDR.le_status <> 'A') 
	AND (dbo.PJLABHDR.le_status <> 'P') 
WHERE (YEAR(dbo.PJBILLABLEHOURS.TimePeriodDate) = YEAR(GETDATE())) 
  AND (MONTH(dbo.PJBILLABLEHOURS.TimePeriodDate) = @TestMonthParameter)
ORDER BY dbo.PJEMPLOY.emp_name, dbo.PJBILLABLEHOURS.TimePeriodDate

Open in new window

rdracer58

ASKER

The approver column in the PJLABHDR table is essentially the same thing as the employee column (both are employee id numbers). We need some way to get the approver name similar to the employee name, if that makes sense.

rdracer58

ASKER

As a side note, I will be in meetings from 3 to 5 PM EST. I will check back here and experiment with your queries following the conclusion of my meetings.

Steve Hogg

I understand the approver. It is just another record in the PJEMPLOY table. I think this brings it in.

-- Simplified
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 'Not Found' 
END AS TimesheetStatus
FROM dbo.PJBILLABLEHOURS
INNER JOIN dbo.PJEMPLOY
	ON dbo.PJEMPLOY.emp_status = 'A'
RIGHT JOIN dbo.PJLABHDR
	ON dbo.PJLABHDR.pe_date = dbo.PJBILLABLEHOURS.TimePeriodDate
	AND (dbo.PJLABHDR.le_status <> 'X') 
	AND (dbo.PJLABHDR.le_status <> 'A') 
	AND (dbo.PJLABHDR.le_status <> 'P') 
INNER JOIN dbo.PJEMPLOY AS APPROVER
	ON dbo.PJLABHDR.Approver = APPROVER.employee 
WHERE (YEAR(dbo.PJBILLABLEHOURS.TimePeriodDate) = YEAR(GETDATE())) 
  AND (MONTH(dbo.PJBILLABLEHOURS.TimePeriodDate) = @TestMonthParameter)
ORDER BY dbo.PJEMPLOY.emp_name, dbo.PJBILLABLEHOURS.TimePeriodDate

Open in new window

rdracer58

ASKER

I quickly tried the last query--something appears to be wrong as it will returns numerous duplicates of the same timesheet for the same employee, with different approvers.

Steve Hogg

I changed the join to APPROVER to a LEFT JOIN. This should stop the duplicate rows. We could also change the join between PJLAHDR and APPROVER to a subquery but I think the LEFT will work.

-- Simplified
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 'Not Found' 
END AS TimesheetStatus
FROM dbo.PJBILLABLEHOURS
INNER JOIN dbo.PJEMPLOY
	ON dbo.PJEMPLOY.emp_status = 'A'
RIGHT JOIN dbo.PJLABHDR
	ON dbo.PJLABHDR.pe_date = dbo.PJBILLABLEHOURS.TimePeriodDate
	AND (dbo.PJLABHDR.le_status <> 'X') 
	AND (dbo.PJLABHDR.le_status <> 'A') 
	AND (dbo.PJLABHDR.le_status <> 'P') 
LEFT JOIN dbo.PJEMPLOY AS APPROVER
	ON dbo.PJLABHDR.Approver = APPROVER.employee 
WHERE (YEAR(dbo.PJBILLABLEHOURS.TimePeriodDate) = YEAR(GETDATE())) 
  AND (MONTH(dbo.PJBILLABLEHOURS.TimePeriodDate) = @TestMonthParameter)
ORDER BY dbo.PJEMPLOY.emp_name, dbo.PJBILLABLEHOURS.TimePeriodDate

Open in new window

rdracer58

ASKER

I'm still getting the duplicate rows with the new query. What would be the best way to approach the subquery?

Steve Hogg

I was mising a join between PJEMPLOY and PJLABHDR.

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 'Not Found' 
END AS TimesheetStatus
FROM dbo.PJBILLABLEHOURS
INNER JOIN dbo.PJEMPLOY
	ON dbo.PJEMPLOY.emp_status = 'A'
RIGHT JOIN dbo.PJLABHDR
	ON dbo.PJLABHDR.pe_date = dbo.PJBILLABLEHOURS.TimePeriodDate
	AND dbo.PJLABHDR.employee = dbo.PJEMPLOY.employee
	AND (dbo.PJLABHDR.le_status <> 'X') 
	AND (dbo.PJLABHDR.le_status <> 'A') 
	AND (dbo.PJLABHDR.le_status <> 'P') 
LEFT JOIN dbo.PJEMPLOY AS APPROVER
	ON dbo.PJLABHDR.Approver = APPROVER.employee 
WHERE (YEAR(dbo.PJBILLABLEHOURS.TimePeriodDate) = YEAR(GETDATE())) 
  AND (MONTH(dbo.PJBILLABLEHOURS.TimePeriodDate) = @TestMonthParameter)
ORDER BY dbo.PJEMPLOY.emp_name, dbo.PJBILLABLEHOURS.TimePeriodDate

Open in new window

rdracer58

ASKER

This seems to help fix the duplicate row issue; however, I do not get any statuses recorded as "Not Found." For example, there are a number of employees I know who have not yet started timesheets for the week ending this last Saturday where no data appears for their 10/18 timesheet, which kind of brings us back to the original problem.

Steve Hogg

Before we joined in for the approver, did we have our "Not Found" rows?

rdracer58

ASKER

Doesn't appear to be so.

Steve Hogg

OK, let's break it down:
This query should return 1 row for each TimePeriod for each Employee

SELECT
dbo.PJBILLABLEHOURS.TimePeriodDate,
dbo.PJEMPLOY.emp_name,
dbo.PJEMPLOY.emp_status,
FROM dbo.PJBILLABLEHOURS
INNER JOIN dbo.PJEMPLOY
ON dbo.PJEMPLOY.emp_status = 'A'

This query, adding the PJLABHDR should return all of the above rows with either a match or a "Not Found" in the TimesheetStatus field for each. Should be the same number of rows returned.

SELECT
dbo.PJBILLABLEHOURS.TimePeriodDate,
dbo.PJEMPLOY.emp_name,
dbo.PJEMPLOY.emp_status,
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 'Not Found'
END AS TimesheetStatus
FROM dbo.PJBILLABLEHOURS
INNER JOIN dbo.PJEMPLOY
ON dbo.PJEMPLOY.emp_status = 'A'
RIGHT JOIN dbo.PJLABHDR
ON dbo.PJLABHDR.pe_date = dbo.PJBILLABLEHOURS.TimePeriodDate
AND dbo.PJLABHDR.employee = dbo.PJEMPLOY.employee
AND (dbo.PJLABHDR.le_status <> 'X')
AND (dbo.PJLABHDR.le_status <> 'A')
AND (dbo.PJLABHDR.le_status <> 'P')

How are we to this point?

ASKER CERTIFIED SOLUTION

Steve Hogg

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rdracer58

ASKER

Excellent! That did the trick. Thank you VERY VERY much for your help! I am very appreciative that you decided to stick with it until a solution had been reached.

rdracer58

ASKER

Follow-up question: whenever I a timesheet status of "Not Found" is listed, an approver name is not listed. Is there a way to resolve this so that an approver name is listed no matter what the status of the timesheet?

Steve Hogg

Isn't the Approver found on the timesheet record in PJLABHDR? Because the Not Found is referring to no record found in PJLABHDR so we can't match to an Approver. Is there another place we can get the Approver name? If not, you could build another subquery that joins to PJEMPLOY and selects the Approver from other timesheets?

rdracer58

ASKER

Good point. The approver is found in the PJLABHDR record, and the Not Found is referring to records not listed in the PJLABHDR table; however, (1) it would be good if all records (whether "not found" or some other status) could be listed under the same employee name and (2) (the greater of the problems) is that I found that time periods that an employee has submitted a timesheet are also being listed as "not found" (or in other words, it appears we are getting duplicate entries...one for a timesheets that have been submitted and another saying "not found").

I have opened a new question here: https://www.experts-exchange.com/questions/23833245/Inaccurate-Query-Results.html, if you would be interested in continuing the discussion there. That way I can credit you with more points if you are still interested in helping.