Case statement in where clause

Question

I'm trying to create a SSRS report for pulling employee records.

Depending on the parameter value for @workgroup at run-time, I want the query to select records based on specific status. I tried the below case statement in where clause but it is not working. 

-------------------------------------------

FROM 
EMPLOYEE E 

LEFT OUTER JOIN DEPT D
ON E.DEPT = D.CODE

LEFT OUTER JOIN ORGUNIT O
ON O.CODE = E.ORGUNIT

LEFT OUTER JOIN JOB J
ON E.JOB = J.CODE

WHERE 

E.FLDWORKGRP = @workgroup AND

CASE WHEN @workgroup = 'Value' THEN E.STATUS = 'A' ELSE
E.STATUS IN ('A','N','LOA','P','D') END

ORDER BY E.LNAME

Let me know if there is any other way to do this. Thanks in advance for your help.

Answer 1

FROM 
	dbo.EMPLOYEE E 
	LEFT OUTER JOIN dbo.DEPT D ON E.DEPT = D.CODE
	LEFT OUTER JOIN dbo.ORGUNIT O ON O.CODE = E.ORGUNIT
	LEFT OUTER JOIN dbo.JOB J ON E.JOB = J.CODE
WHERE 
	E.FLDWORKGRP = @workgroup AND
	1 = (CASE 
			WHEN @workgroup = 'Value' THEN (CASE WHEN E.STATUS = 'A' THEN 1 ELSE 0 END) 
			ELSE (CASE WHEN E.STATUS IN ('A','N','LOA','P','D') THEN 1 ELSE 0 END) 
		END)
ORDER BY E.LNAME

Please let me know if above SQL statement works for you or not.

---

Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

Answer 2

No, it didn't worked.

Thanks for your quick reply.

Answer 3

Could you please put some sample data and describe what you are trying to achieve and desired output?

I did the below test and seems is working, that is why if you can provide us what you pass as parameter with some sample data would be great.

DECLARE @Employee Table (EmployeeID INT, Status CHAR(3), FLDWORKGRP VARCHAR(10))
INSERT INTO @Employee 
SELECT 1, 'A', 'Value' UNION ALL 
SELECT 2, 'B', 'Value' UNION ALL 
SELECT 3, 'P', '' UNION ALL 
SELECT 4, 'C', '' UNION ALL 
SELECT 5, 'D', '' 

DECLARE @workgroup VARCHAR(10) = '' 

SELECT * 
FROM 
	@Employee E
WHERE 
	1 = (CASE WHEN @workgroup = 'Value' THEN (CASE WHEN E.Status = 'A' THEN 1 ELSE 0 END) ELSE (CASE WHEN E.Status In ( 'A','N','LOA','P','D') THEN 1 ELSE 0 END) END)

/*
Output
EmployeeID	Status	FLDWORKGRP
1	A  	Value
3	P  	
5	D  	
*/
SET @workgroup = 'Value' 
SELECT * 
FROM 
	@Employee E
WHERE 
	1 = (CASE WHEN @workgroup = 'Value' THEN (CASE WHEN E.Status = 'A' THEN 1 ELSE 0 END) ELSE (CASE WHEN E.Status In ( 'A','N','LOA','P','D') THEN 1 ELSE 0 END) END)
/*
Output
EmployeeID	Status	FLDWORKGRP
1	A  	Value
*/

---

Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

Answer 4

@workgroup = 'Value1' Then list all employees that have active 'A' status, other wise list the following status 'A','N','LOA','P','D'

Output

Name  Status

EMP1  A

EMP2 A

EMP3 A

@workgroup = 'Value2'

Name  Status

EMP1 A

EMP6 N

EMP7 LOA

etc

Answer 5

FROM 
EMPLOYEE E 

LEFT OUTER JOIN DEPT D
ON E.DEPT = D.CODE

LEFT OUTER JOIN ORGUNIT O
ON O.CODE = E.ORGUNIT

LEFT OUTER JOIN JOB J
ON E.JOB = J.CODE

WHERE 

E.FLDWORKGRP = @workgroup 
AND E.STATUS = 'A' 
OR (@workgroup = 'Value' AND E.STATUS IN ('N','LOA','P','D') 
ORDER BY E.LNAME

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My MSDN Page
My Personal Blog
My Facebook Page

Answer 6

Workgroup has the following values say (Value1, Value2, Value3 etc) 

@workgroup = 'Value1' then status = 'A' 

for all other @workgroup ('Value2','Value3','.....) status IN ('A','N','LOA','P','D')

I hope this helps and thanks for your time.

Answer 7

WHERE 
1 = (CASE WHEN @workgroup = 'Value1' THEN (CASE WHEN E.Status = 'A' THEN 1 ELSE 0 END) ELSE (CASE WHEN E.Status In ( 'A','N','LOA','P','D') THEN 1 ELSE 0 END) END)

---

Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

Answer 8

E.WORKGRP = @workgroup 

Sample data

Name         Workgroup    Status

Name1        Value 1        A
Name2        Value 2        N
Name3        Value 3        D
Name4        Value 4        A

Answer 9

Did you try the latest script that I provide to you? You do NOT need to have "E.WORKGRP = @workgroup " in your WHERE clause. 

---

Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

Answer 10

Yes, the query should check E.WORKGRP = @workgroup

I need only those records that has a value for the workgroup. 

Answer 11

If you cannot precisely describe the logic you need to implement in words, you will likely find it difficult to implement this logic in code.  In addition, your readers will be confused about what you said vs. what you mean or intend.  So let's back up.

You have a report that has one parameter - @workgroup.  So, what values do you intend to supply in that parameter?  I notice that you have referred to the literals "Value" and "Value1" while posting pseudo-code that uses "Value 1".  Can you see the confusion you are causing? 

Next, let's talk about what impact you desire based on the value of the parameter.  As best I can tell, when the value of this parameter is <some uncertain but specific value> you want the report to select rows where STATUS = 'A'.  Must you also limit the rows based on matching the similarly named column (FLDWORKGRP) to the value of the parameter?  And when the value of the parameter is anything but this specific value, what do you want to select?  My guess - all rows where status is one of: 'A','N','LOA','P','D'.  Does the FLDWORKGRP column need to be considered as well?  

As an exercise, I suggest you write a query that is specific to every possible value you intend to supply as a parameter.    That will make the logic you need explicit and will allow others to understand what you want to do and to offer suggestions for combining the queries into a single one. 

One last comment.  CASE is not a control-of-flow construct in tsql.  It is an expression that returns a scalar value.  That is why you cannot "apply" additional logic in the manner you attempt.  And please have a look at the sticky posts at the top of the forum.  They provide suggestions regarding how to provide useful information in your posts.  A script that can be executed by everyone will greatly reduce confusion and speed up the process of finding a solution.

Answer 12

Yes,I understood what you are talking about and sorry for the confusion. 

Answer 13

I'm sorry for all the confusion, finally I was able to make it work.  In future, I will make sure to  describe the logic I want to implement in detail.  

WHERE 
E.FLDWORKGRP = @workgroup AND
1 = (CASE 
WHEN @workgroup = 'UWR' AND E.FLDSTATUS = 'A' THEN 1
ELSE CASE WHEN @workgroup = 'UWMCMS' AND E.FLDSTATUS IN ('A','N','LOA','P','D') THEN 1 ELSE 0 END END)

Thanks for all the helpful scripts and suggestions.