Solved

Challenges developing timesheet status report query.

Posted on 2008-10-20
27
429 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 13
27 Comments
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22759633
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
ID: 22760049
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
ID: 22760221
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Comment

by:rdracer58
ID: 22760265
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
ID: 22760463
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
ID: 22760549
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
ID: 22760550
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
ID: 22760608
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
ID: 22760758
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
ID: 22760803
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
ID: 22760883
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
ID: 22760908
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
ID: 22760927
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
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22760966
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
ID: 22761046
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
ID: 22761116
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
ID: 22761826
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
ID: 22762008
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
ID: 22762105
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
ID: 22762127
Before we joined in for the approver, did we have our "Not Found" rows?
0
 

Author Comment

by:rdracer58
ID: 22762142
Doesn't appear to be so.
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22762216
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
ID: 22762282
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
ID: 31507877
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
ID: 22767190
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
ID: 22767339
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
ID: 22767390
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

688 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