Solved

Challenges developing timesheet status report query.

Posted on 2008-10-20
27
391 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:rdracer58
  • 14
  • 13
27 Comments
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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>>)
0
 

Author Comment

by:rdracer58
Comment Utility
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?
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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?
0
 

Author Comment

by:rdracer58
Comment Utility
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.
0
 
LVL 17

Expert Comment

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

 
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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?
0
 

Author Comment

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

Author Comment

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

Author Comment

by:rdracer58
Comment Utility
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?
0
 
LVL 17

Expert Comment

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

0
 
LVL 17

Expert Comment

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

0
 

Author Comment

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

Author Comment

by:rdracer58
Comment Utility
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.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 17

Expert Comment

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

0
 

Author Comment

by:rdracer58
Comment Utility
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.
0
 
LVL 17

Expert Comment

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

0
 

Author Comment

by:rdracer58
Comment Utility
I'm still getting the duplicate rows with the new query. What would be the best way to approach the subquery?
0
 
LVL 17

Expert Comment

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

0
 

Author Comment

by:rdracer58
Comment Utility
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.
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
Before we joined in for the approver, did we have our "Not Found" rows?
0
 

Author Comment

by:rdracer58
Comment Utility
Doesn't appear to be so.
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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?
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 500 total points
Comment Utility
Just caught my mistake, change it to a LEFT JOIN.

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'

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') 

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

0
 

Author Closing Comment

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

Author Comment

by:rdracer58
Comment Utility
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?
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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?
0
 

Author Comment

by:rdracer58
Comment Utility
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: http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23833245.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.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

772 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

16 Experts available now in Live!

Get 1:1 Help Now