• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Looking for a cleaner SELECT statement

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.
0
AkAlan
Asked:
AkAlan
  • 5
  • 3
  • 3
  • +1
2 Solutions
 
HainKurtSr. System AnalystCommented:
please post a sample data for the involved tables and what you are looking for from this sample data...
0
 
HainKurtSr. System AnalystCommented:
also I dont see the table with alias tr

jaTR.TrainingRequirementID = tr.TrainingRequirementID

do you miss something?
0
 
HainKurtSr. System AnalystCommented:
maybe you are looking for this
select * from (			
SELECT e.EmployeeName, e.EmployeeJobAssignmentID, (case when jaTr.JobAssignmentID is null then 0 else 1 end) as isRrequired
  FROM myTable e legft join dbo.HR_JobAssignment_x_TrainingRequirement jaTR on 
       jaTR.JobAssignmentID = e.EmployeeJobAssignmentID AND jaTR.TrainingRequirementID = tr.TrainingRequirementID
       ) x where (isNull(@Required,0) =0) or (@Required = x.IsRequired)

Open in new window

0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
lcohanDatabase AnalystCommented:
Did you set the Query timepout to 0(unlimited) like per article below:
http://support.microsoft.com/kb/811030
0
 
lcohanDatabase AnalystCommented:
Please disregard my posting above as it's not related and meant for different article - I appologize.
0
 
wdosanjosCommented:
Please provide the complete code.  It will help me to analyze it and possibly provide a cleaner version.
0
 
AkAlanAuthor 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

0
 
wdosanjosCommented:
Please try the following:

ALTER PROCEDURE [dbo].[spSelectHR_EmployeeTrainingLastCompleted]
(@EmployeeNumber Char(6) = 0,
@Required bit = NULL)
AS 
SELECT * FROM
(
	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 (EmployeeNumber = @EmployeeNumber OR @EmployeeNumber = 0) 
) q
WHERE (Required = ISNULL(@Required, Required))
ORDER BY  Employee, Description

Open in new window

0
 
HainKurtSr. System AnalystCommented:
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...
0
 
AkAlanAuthor 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.


0
 
HainKurtSr. System AnalystCommented:
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
0
 
wdosanjosCommented:
Have you tried the script I posted?
0
 
AkAlanAuthor 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!!
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now