Solved

Inaccurate Query Results

Posted on 2008-10-21
22
294 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 51

Expert Comment

by:Mark Wills
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Or, do it in the report itself of course...
0
 

Author Comment

by:rdracer58
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to increase the row limit in Jasper Server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now