Solved

Inaccurate Query Results

Posted on 2008-10-21
22
295 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:rdracer58
  • 9
  • 6
  • 5
  • +2
22 Comments
 
LVL 3

Expert Comment

by:Blackninja2007
ID: 22767360
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
 

Author Comment

by:rdracer58
ID: 22767408
I corrected the query to a LEFT OUTER JOIN and have experimented with the INNER JOIN without much success.
0
 
LVL 5

Expert Comment

by:jose_juan
ID: 22767425
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 17

Expert Comment

by:HoggZilla
ID: 22767491
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
 

Author Comment

by:rdracer58
ID: 22767523
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22767749
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22768343
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
 

Author Comment

by:rdracer58
ID: 22768569
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22768621
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
 

Author Comment

by:rdracer58
ID: 22768698
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22768769
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22768773
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22768809
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
 

Author Comment

by:rdracer58
ID: 22768813
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
 

Author Comment

by:rdracer58
ID: 22768845
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22768917
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
 

Author Comment

by:rdracer58
ID: 22768945
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 22769058
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
 

Author Comment

by:rdracer58
ID: 22769079
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22769107
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22769133
Or, do it in the report itself of course...
0
 

Author Comment

by:rdracer58
ID: 22769363
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question