Inaccurate Query Results

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)
                      = @TestMonthParameter)
ORDER BY dbo.PJEMPLOY.emp_name, dbo.PJBILLABLEHOURS.TimePeriodDate
rdracer58Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Blackninja2007Commented:
I think you need to be using LEFT Outer Join ( rather than RIGHT Outer Join) so it lists all Approvers even if there are none of the other entries. Plus i think your INNER Join on your billable Hours may also be preventing items with no timesheet being listed. You may need to change this as well

0
rdracer58Author Commented:
I corrected the query to a LEFT OUTER JOIN and have experimented with the INNER JOIN without much success.
0
jose_juanCommented:
Hi,

what table would have "empty rows" ? (do not match)

PJEMPLOY?

you have a INNER JOIN with PJEMPLOY!!!!

you probably need (see code).

On the other hand, simplify and explain your example (not use real tables and cols, simplify to a example).

Regards!

(use alias for tables!!!! like

SELECT t.name
FROM my_table AS t
WHERE t.field = 3
)


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.PJLABHDR
JOIN	dbo.PJBILLABLEHOURS
ON	dbo.PJLABHDR.pe_date = dbo.PJBILLABLEHOURS.TimePeriodDate
JOIN	dbo.PJEMPLOY
ON	dbo.PJEMPLOY.emp_status = 'A'
AND	dbo.PJLABHDR.employee = dbo.PJEMPLOY.employee
 
LEFT OUTER
JOIN	dbo.PJEMPLOY AS APPROVER
ON APPROVER.employee = dbo.PJLABHDR.Approver
 
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HoggZillaCommented:
Let's make sure our beginning select is in order. Does this select one TimeSheet per period per employee? If Yes, let's go from there, if No let's fix this first. Perhaps the key structure is not what I believe it is?
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'

Open in new window

0
rdracer58Author Commented:
Let me clarify:

-      I am pulling the time period date from the PJBILLABLEHOURS table (this is the end date of a timesheet, i.e. 10/5, 10/12, 10/19, etc. and timesheets are submitted on a weekly basis) and the employee/timesheet approver names from the PJEMPLOY table.
-      The PJLABHDR table contains status information for timesheets (the status codes are deciphered in the query), however, it does not appear to contain data for timesheets that have not yet been started (i.e. if an employee has not yet started a timesheet for the period ending 10/5, it wont show up in the table).
-      Data should be retrieved for all time periods for all employees for a selected month (I allow report users to select a month via parameter, as you can see in my attempts at the query thus far). Data needs to be retrieved for timesheets that have been started/submitted as well as those that have not yet been started.
-      The problem is thus far I have been unable to get a query that will accurately return data for when an employee who has not yet started a timesheet along with the employee name, timesheet approver name, etc. For example, if an employee has not yet started a timesheet for 10/19 (or in other words, if no data resides in the PJLABHDR table for that employee/time period), the query should return a row for 10/19 for the employee in question stating missing" or "not found" or some other status message along those lines, however, it should still have the employee and approver name.
0
HoggZillaCommented:
This query is based on the structure as i understand it. If it is not working correctly then I reccomend you post the CREAT TABLE statements for each of the 3 tables, along with the keys. Hope this works. I created a subquery to pull the Aprrover from previous timesheets.

SELECT
dbo.PJBILLABLEHOURS.TimePeriodDate,
dbo.PJEMPLOY.emp_name, 
dbo.PJEMPLOY.emp_status, 
APPROVER.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.PJBILLABLEHOURS
INNER JOIN dbo.PJEMPLOY
	ON dbo.PJEMPLOY.emp_status = 'A'
LEFT JOIN (SELECT DISTINCT h.employee, h.Approver, e.emp_name AS ApproverName, MAX(h.pe_date)
		   FROM dbo.PJLABHDR h
		   INNER JOIN dbo.PJEMPLOY e
				 ON h.Approver = e.employee
			WHERE h.employee IS NOT NULL
			  AND h.approver IS NOT NULL
			GROUP BY h.employee, h.Approver, e.emp_name) AS Approver
	ON dbo.PJEMPLOY.employee = Approver.employee
LEFT JOIN dbo.PJLABHDR
	ON dbo.PJBILLABLEHOURS.TimePeriodDate = dbo.PJLABHDR.pe_date
	AND dbo.PJEMPLOY.employee = dbo.PJLABHDR.employee
	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

0
Mark WillsTopic AdvisorCommented:
Find the whole concept of reporting a timesheet that hasn't started and then someone who approves the non-started (or Missing) timesheet an interesting concept, especially if the only link to employees and approvers is via the non-existant timesheet. Is there anything in PJBILLABLEHOURS to join them up ? Or should PJBILLABLEHOURS be cross joined with employees (who fill out timesheests) first  ?

I think hoggzilla needs an order by in the subquery to get the most recent approver prior to the missing timesheet (ie the approver subquery)... and might be worth including as an in-line query...
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 'Missing' 
END AS TimesheetStatus,
 
(SELECT top 1 e.emp_name AS ApproverName 
 FROM dbo.PJLABHDR h INNER JOIN dbo.PJEMPLOY e ON h.Approver = e.employee
 WHERE h.employee = dbo.pjemploy.employee and h.pe_date <= PJBILLABLEHOURS.TimePeriodDate order by h.pe_date desc) as Approver_Name
		
FROM dbo.PJBILLABLEHOURS CROSS JOIN dbo.PJEMPLOY
LEFT JOIN dbo.PJLABHDR ON dbo.PJBILLABLEHOURS.TimePeriodDate = dbo.PJLABHDR.pe_date
	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') 
WHERE (YEAR(dbo.PJBILLABLEHOURS.TimePeriodDate) = YEAR(GETDATE())) 
  AND (MONTH(dbo.PJBILLABLEHOURS.TimePeriodDate) = @TestMonthParameter)
  AND  dbo.PJEMPLOY.emp_status = 'A'
ORDER BY dbo.PJEMPLOY.emp_name, dbo.PJBILLABLEHOURS.TimePeriodDate

Open in new window

0
rdracer58Author Commented:
mark_wills last post seems to have gotten us a step closer to a solution, however, there is still an interesting error. I tested the query for October and examined my own timesheets since I know the current status. The timesheet for 10/19 was marked as "Needs Approval," 10/26 was marked as "Incomplete," and 10/31 was marked as "Missing," all of which are correct; however, 10/5 and 10/12 were marked as "Missing," when they should have been filtered from the query because they have an le_status of "A."

Would it be possible to either (1) ensure these are filtered from the query and not erroneously marked as "missing" or (2) do not try to filter timesheets with an le_status of "X," "A," or "P" and merely have them labeled with the correct status?
0
HoggZillaCommented:
The problem is that we are pulling a row for each timesheet period for each employee. To stop this you need to move the dbo.PJLABHDR.le_status  out of the JOIN and put it in the WHERE clause.
0
rdracer58Author Commented:
When I use the query as pasted in below, with the dbo.PJLABHDR.le_status filtering removed from the JOIN clause and put in the WHERE clause, no timesheets with "Missing" are returned in the results; however, when I removed the filtering completely, the results seem to be completely accurately with the exception of no filtering of any kind.
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 'Missing' 
END AS TimesheetStatus,
 
(SELECT top 1 e.emp_name AS ApproverName 
 FROM dbo.PJLABHDR h INNER JOIN dbo.PJEMPLOY e ON h.Approver = e.employee
 WHERE h.employee = dbo.pjemploy.employee and h.pe_date <= PJBILLABLEHOURS.TimePeriodDate order by h.pe_date desc) as Approver_Name
                
FROM dbo.PJBILLABLEHOURS CROSS JOIN dbo.PJEMPLOY
LEFT JOIN dbo.PJLABHDR ON dbo.PJBILLABLEHOURS.TimePeriodDate = dbo.PJLABHDR.pe_date
        AND  dbo.PJLABHDR.employee = dbo.PJEMPLOY.employee
WHERE (YEAR(dbo.PJBILLABLEHOURS.TimePeriodDate) = YEAR(GETDATE())) 
  AND (MONTH(dbo.PJBILLABLEHOURS.TimePeriodDate) = @TestMonthParameter)
  AND  dbo.PJEMPLOY.emp_status = 'A' AND (dbo.PJLABHDR.le_status <> 'X') 
        AND (dbo.PJLABHDR.le_status <> 'A') 
        AND (dbo.PJLABHDR.le_status <> 'P') 
ORDER BY dbo.PJEMPLOY.emp_name, dbo.PJBILLABLEHOURS.TimePeriodDate

Open in new window

0
HoggZillaCommented:
This will bring back a timesheet per period, per employee IF the timesheet is in the requested month and the employee status is 'A' and ONLY IF the timesheet is NOT in X,A,P
WHERE (YEAR(dbo.PJBILLABLEHOURS.TimePeriodDate) = YEAR(GETDATE())) 
  AND (MONTH(dbo.PJBILLABLEHOURS.TimePeriodDate) = @TestMonthParameter)
  AND  dbo.PJEMPLOY.emp_status = 'A' 
  AND  (dbo.PJLABHDR.le_status NOT IN ('X','A','P')) 

Open in new window

0
Mark WillsTopic AdvisorCommented:
AND (dbo.PJLABHDR.le_status <> 'X')
        AND (dbo.PJLABHDR.le_status <> 'A')
        AND (dbo.PJLABHDR.le_status <> 'P')    should still be on the join...

ie:

FROM dbo.PJBILLABLEHOURS CROSS JOIN dbo.PJEMPLOY

LEFT JOIN dbo.PJLABHDR ON dbo.PJBILLABLEHOURS.TimePeriodDate = dbo.PJLABHDR.pe_date
        AND  dbo.PJLABHDR.employee = dbo.PJEMPLOY.employee
        AND  dbo.PJLABHDR.le_status not in ( 'X','A','P')

WHERE (YEAR(dbo.PJBILLABLEHOURS.TimePeriodDate) = YEAR(GETDATE()))
  AND (MONTH(dbo.PJBILLABLEHOURS.TimePeriodDate) = @TestMonthParameter)
  AND  dbo.PJEMPLOY.emp_status = 'A'
ORDER BY dbo.PJEMPLOY.emp_name, dbo.PJBILLABLEHOURS.TimePeriodDate
0
Mark WillsTopic AdvisorCommented:
HoggZilla: great minds : dbo.PJLABHDR.le_status not in ( 'X','A','P')  just in different spots...  personally prefer the join to reduce the rowset match from the cross join, but should work in either spot.
0
rdracer58Author Commented:
Adding the "AND  dbo.PJLABHDR.le_status not in ( 'X','A','P')" brings us back to the problem of any timesheets with an le_status of X, A, or P being labeled as "missing."
0
rdracer58Author Commented:
Clarification: Adding the "AND  dbo.PJLABHDR.le_status not in ( 'X','A','P')" to the JOIN statement brings us back to the problem of any timesheets with an le_status of X, A, or P being labeled as "missing." Following HoggZilla's methodology of the modified WHERE statement brings back a result set that does not include any timesheets with a status of "missing." Consequently, either mode of filtering is not good.
0
HoggZillaCommented:
Dude, you must be hating life right now. Sorry I cannot seem to get the right answer.
I hate tossing darts, but try modifying the WHERE clause with:
AND ISNULL(dbo.PJLABHDR.le_status,'') not in ( 'X','A','P')"
0
rdracer58Author Commented:
Receive error "Invalid text or symbol.
Error in list of values in IN clause.
Unable to parse query text" when making aforementioned modification to the WHERE clause.
0
Mark WillsTopic AdvisorCommented:
re: 22768845:  Hang on, that doesn't make sense - something is "missing"

if excluded from the join, then yes, will be "missing"
if excluded from the result set (ie in the where clause) then matching times will not be reported on at all.

That much I do understand,

but it sounds like you want them in the result set so they can be reported on... So, what about ignoring that clase altogether ie remove :
 AND  dbo.PJLABHDR.le_status not in ( 'X','A','P')

At least that way the "A" spreadsheet will show as Approved etc...

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rdracer58Author Commented:
I think what I am going to do is remove the "AND  dbo.PJLABHDR.le_status not in ( 'X','A','P')" line and filter those timesheets in the report itself.

Thanks!
0
Mark WillsTopic AdvisorCommented:
If you then want to report on just "missing" timesheets then you can do :


select * from (

SELECT
dbo.PJBILLABLEHOURS.TimePeriodDate,
dbo.PJEMPLOY.emp_name,
dbo.PJEMPLOY.emp_status,
dbo.PJLABHDR.le_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 'Missing'
END AS TimesheetStatus,
 
(SELECT top 1 e.emp_name AS ApproverName
 FROM dbo.PJLABHDR h INNER JOIN dbo.PJEMPLOY e ON h.Approver = e.employee
 WHERE h.employee = dbo.pjemploy.employee and h.pe_date <= PJBILLABLEHOURS.TimePeriodDate order by h.pe_date desc) as Approver_Name
             
FROM dbo.PJBILLABLEHOURS CROSS JOIN dbo.PJEMPLOY

LEFT JOIN dbo.PJLABHDR ON dbo.PJBILLABLEHOURS.TimePeriodDate = dbo.PJLABHDR.pe_date
        AND  dbo.PJLABHDR.employee = dbo.PJEMPLOY.employee

WHERE (YEAR(dbo.PJBILLABLEHOURS.TimePeriodDate) = YEAR(GETDATE()))
  AND (MONTH(dbo.PJBILLABLEHOURS.TimePeriodDate) = @TestMonthParameter)
  AND  dbo.PJEMPLOY.emp_status = 'A'

) as missing

where TimesheetStatus = 'Missing'

ORDER BY emp_name, TimePeriodDate

0
Mark WillsTopic AdvisorCommented:
Or, do it in the report itself of course...
0
rdracer58Author Commented:
Ended up using the following query with the " AND (dbo.PJLABHDR.le_status NOT IN ('A', 'P', 'X') OR  dbo.PJLABHDR.le_status IS NULL)" statement in the WHERE clause:

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 'Missing' END AS TimesheetStatus,
                          (SELECT     TOP 1 e.emp_name AS ApproverName
                            FROM          dbo.PJLABHDR AS h INNER JOIN
                                                   dbo.PJEMPLOY AS e ON h.Approver = e.employee
                            WHERE      (h.employee = dbo.pjemploy.employee) AND (h.pe_date <= dbo.PJBILLABLEHOURS.TimePeriodDate)
                            ORDER BY h.pe_date DESC) AS Approver_Name
FROM         dbo.PJBILLABLEHOURS CROSS JOIN
                      dbo.PJEMPLOY LEFT OUTER JOIN
                      dbo.PJLABHDR ON dbo.PJBILLABLEHOURS.TimePeriodDate = dbo.PJLABHDR.pe_date AND
                      dbo.PJLABHDR.employee = dbo.PJEMPLOY.employee
WHERE     (YEAR(dbo.PJBILLABLEHOURS.TimePeriodDate) = YEAR(GETDATE())) AND (MONTH(dbo.PJBILLABLEHOURS.TimePeriodDate)
                      = @TestMonthParameter) AND (dbo.PJEMPLOY.emp_status = 'A') AND (dbo.PJLABHDR.le_status NOT IN ('A', 'P', 'X') OR
                      dbo.PJLABHDR.le_status IS NULL)
ORDER BY dbo.PJEMPLOY.emp_name, dbo.PJBILLABLEHOURS.TimePeriodDate
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.