We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Looking for a cleaner SELECT statement

AkAlan
AkAlan asked
on
Medium Priority
269 Views
Last Modified: 2012-05-11
I'm looking to see if there is a better/cleaner way to achieve the same results I am getting now. I need a column that returns a bit if there is a related record in another table. I am currently using a CASE statement but I have to reuse it in my Where statement twice. I'll give a shortened  example:

ALTER PROCEDURE [dbo].[spMySproc]
(@Required bit = NULL)
AS
SELECT e.EmployeeName,
             e.EmployeeJobAssignmentID ,
             CASE
	WHEN(SELECT jaTr.JobAssignmentID
	FROM    dbo.HR_JobAssignment_x_TrainingRequirement jaTR
	WHERE jaTR.JobAssignmentID = e.EmployeeJobAssignmentID AND jaTR.TrainingRequirementID = tr.TrainingRequirementID) IS NULL 
	THEN 0 
	ELSE 1 
	END
               AS Required
FROM myTable e
Where 

(CASE
	WHEN(SELECT jaTr.JobAssignmentID
	FROM    dbo.HR_JobAssignment_x_TrainingRequirement jaTR
	WHERE jaTR.JobAssignmentID = e.EmployeeJobAssignmentID AND jaTR.TrainingRequirementID = tr.TrainingRequirementID) IS NULL 
	THEN 0 
	ELSE 1 
	END ) = ISNULL(@Required,(CASE
				WHEN(SELECT jaTr.JobAssignmentID
				FROM    dbo.HR_JobAssignment_x_TrainingRequirement jaTR
				WHERE jaTR.JobAssignmentID = e.EmployeeJobAssignmentID AND jaTR.TrainingRequirementID = tr.TrainingRequirementID) IS NULL 
				THEN 0 
				ELSE 1 
				END ))

Open in new window


Seems like there should be a better way to do this. Thanks for any help.
Comment
Watch Question

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
please post a sample data for the involved tables and what you are looking for from this sample data...
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
also I dont see the table with alias tr

jaTR.TrainingRequirementID = tr.TrainingRequirementID

do you miss something?
Sr. System Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
Did you set the Query timepout to 0(unlimited) like per article below:
http://support.microsoft.com/kb/811030
lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
Please disregard my posting above as it's not related and meant for different article - I appologize.
Top Expert 2011

Commented:
Please provide the complete code.  It will help me to analyze it and possibly provide a cleaner version.

Author

Commented:
Ok, so here is the code, minus some columns that are not relavant. The idea is that if a Training Requirement is not assigned to the employees Job Assignment category (thereby creating a record in the jatr table) , then it is not a "Required" Training which is used for determining the employees dispatch status. My question is, is it possible to somehow assign the results of the CASE statement to a variable and then just reuse the value of the variable in the WHERE statement? Thanks for everyones help on this.

ALTER PROCEDURE [dbo].[spSelectHR_EmployeeTrainingLastCompleted]
(@EmployeeNumber Char(6) = 0,
@Required bit = NULL)
AS SELECT			e.LastName + ', ' + e.FirstName + ' ' + ISNULL(e.MI, '') AS Employee,
					e.EmployeeNumber, 
					tr.Description, 
					tr.Interval, 
					tr.Frequency, 
					
					CONVERT(VARCHAR, (SELECT MAX(DateCompleted)
					FROM    dbo.HR_TrainingHistory
					WHERE TrainingRequirementID = etr.TrainingRequirementID AND EmployeeNumber = etr.EmployeeNumber), 107) 
					AS LastDateCompleted,
						 
										
					CASE
						WHEN(SELECT jaTr.JobAssignmentID
						FROM    dbo.HR_JobAssignment_x_TrainingRequirement jaTR
						WHERE jaTR.JobAssignmentID = e.EmployeeJobAssignmentID AND jaTR.TrainingRequirementID = tr.TrainingRequirementID) IS NULL 
						THEN 0 
						ELSE 1 
					END AS Required, 
					etr.TrainingRequirementID, 
					o.FirstName + ', ' + o.LastName as TrainingOwner
					
                
FROM	dbo.HR_TrainingRequirements				tr 
	INNER JOIN
                dbo.HR_EmployeeTrainingRequirements		                etr	ON tr.TrainingRequirementID = etr.TrainingRequirementID 
                RIGHT OUTER JOIN
                dbo.HR_Employees					e	ON etr.EmployeeNumber = e.EmployeeNumber 
                LEFT OUTER JOIN
                dbo.HR_Employees					o	ON o.EmployeeNumber = tr.OwnerID 
                
WHERE   (e.EmployeeNumber = @EmployeeNumber OR @EmployeeNumber = 0) 
			AND (CASE
						WHEN(SELECT jaTr.JobAssignmentID
						FROM    dbo.HR_JobAssignment_x_TrainingRequirement jaTR
						WHERE jaTR.JobAssignmentID = e.EmployeeJobAssignmentID AND jaTR.TrainingRequirementID = tr.TrainingRequirementID) IS NULL 
						THEN 0 
						ELSE 1 
					END ) = ISNULL(@Required,(CASE
						WHEN(SELECT jaTr.JobAssignmentID
						FROM    dbo.HR_JobAssignment_x_TrainingRequirement jaTR
						WHERE jaTR.JobAssignmentID = e.EmployeeJobAssignmentID AND jaTR.TrainingRequirementID = tr.TrainingRequirementID) IS NULL 
						THEN 0 
						ELSE 1 
					END ))
                
ORDER BY  Employee, tr.Description

Open in new window

Top Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
did you try my query posted above?
please post a sample data for tables and post what you are after... your query definetely can be written in a more compact & efficient way... just help us to understand the logic instead of solving the query...

Author

Commented:
@HainKurtI have tried the query you mentioned, I actually tried that myself before posting but it seems that because Required is created from a SELECT statement, when I try and reference it in the WHERE statement I get a red underline that says Invalid column name 'Required'. That is the part I was hoping to be able to clean up. The only way I can get my sproc to work is by recreating the CASE statement again.
I will try to explain what I am doing here but it is a bit involved.  I have the following relavant tables:

HR_Employees  (EmployeeNumber  pk, EmployeeJobAssignmentID fk)                            basic employee info
HR_EmployeeJobAssignments (EmployeeJobAssignmentID pk)                                        Job Assignment assigned to employees, ex., Electrician, Plumber, etc..
HR_TrainingRequirements (TrainingRequirementID pk)                                                     All Training Requirements that can be assigned to employees, ex. CPR, First Aid, etc...
HR_EmployeeTrainingRequirements  (EmployeeTrainReqID  pk,EmployeeNumber fk, TrainingRequirementID fk  ) Training Requirements taht are assigned to Employees
HR_JobAssignment_x_TrainingRequirement (Tr_x_JaID  pk, JobAssignmentID fk, TrainingRequirementID fk)         Cross table associating Job assignments to Training Requirements)
HR_TrainingHistory  (TrainingHistoryID pk, EmployeeNumber fk, TrainingRequirementID fk)                             history of every time an employee completes a training.

All Training Requirements are assigned to the individual employee. We also identify Training Requirements that employees with specific Job Assignments must be current on in order to be able to be dispatched. That is the purpose of the  HR_JobAssignment_x_TrainingRequirement  table. So, if we assign CPR training to the Electrician JobAssignment, I run a sproc (by way of a scheduled job) that updates all Employees who are Electricians with the CPR Training Requirement. Then when I run the sproc in question here to see an employees training status, if the Training Requirement and the employees Job Assinment are a match in the HR_JobAssignment_x_TrainingRequirement table (This is where the CASE statement comes in play), it is a "Required" training and the employee must be current on that training in order to be dispatched. Otherwise it is just an additional Training Requirement that will not prevent dispatching.

I hope I have made this clear enough, Thanks for your help with this.


HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
But again, if you can create an excel file with the tables inside with some sample data
and resultset that you are looking for, that will help us to understand and solve the issue instead of trying to understand whole logic... Most of the time we solve the issues without seeing the whole picture :)

I am looking for this

Table 1
col1 col2 col3
row11 row12 row13
row21 row22 row23
row31 row32 row33

Table 2
col1 col2 col3
row11 row12 row13
row21 row22 row23

Table 3
col1 col2 col3
row11 row12 row13
row21 row22 row23
row31 row32 row33

and a result
Top Expert 2011

Commented:
Have you tried the script I posted?

Author

Commented:
I appologized to you guys for not seeing what you provided. I didn't thougroughly read your posts and missed the important part where you do the SELECT * FROM and then have the rest of the code in another SELECT statement. This is a new concept to me and I missed it. Both of your solutions worked and did what I asked. Thanks very much!!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.