WHERE Clause AND/OR Issue (Replacing CASE)

[earljgray]

Trying to replace case statement in a Where clause with boolean expressions. Need help in getting this resolved.

>> SQL Server 2008 R2
>> Three Parameters - Start Date, End Date, Employee [The Full name of the employee is required.]
>> There is a MODIFIEDBY field which contains the full names of all employees in the department. The manager, however, only wants a subset of those employees to be reported out, or, optionally, only 1 selected employee reported out.

Before the requirement for specifying 1 employee, the filter construction to limit the records displayed to specific employees looked like this:

(Cond.MODIFIEDBY like '%Chilano%'
or Cond.MODIFIEDBY like '%Kennedy%'
or Cond.MODIFIEDBY like '%Czamara-Kessler%'
or Cond.MODIFIEDBY like '%Woods%'
or Cond.MODIFIEDBY like '%Ka%Urban%'
or Cond.MODIFIEDBY like '%Hoover%')
)
When the requirement for selecting a single employee came in, the @Employee parameter was created. The NEW logic was:
IF there's nothing in @Employee THEN go through the original filter ELSE IF there's a value in @Employee then select for that and ignore the multi-name filter.

If the user puts in 07/01/2015, 07/24/2015, Susan Kennedy, all Susan Kennedy records between the two dates should be selected.

If the user puts in 07/01/2015, 07/24/2015, '', all records between the two dates should be selected for employees whose last names fall within the 'like' filter.

I've tried several combinations of AND / OR expressions; tests for null @Employee, etc. but end up with:
>> Complete the @Employee parameter with a name, get everyone for the date range
>> Do not enter a value for @Employee, get everyone for the date range - both the filtered AND the unfiltered employees

>>>> Cannot get the single employee when @Employee contains a legal name or JUST the filtered Employees when passing no name to @Employee....

Suggestions and pointers welcome!!!

Crystal Reports Design/training/Consultation
earljgray@gmail.com

 

[DaveInIowa]

You don't necessarily need to get rid of the CASE statement.

IF (LEN(LTRIM(RTRIM(@Employee))) = 0) SET @Employee = NULL

SELECT ...
 WHERE 
       CASE
          WHEN @Employee IS NULL THEN
             CASE 
                WHEN Cond.MODIFIEDBY LIKE '%Chilano%' THEN 1
                WHEN Cond.MODIFIEDBY LIKE '%Kennedy%' THEN 1
                WHEN Cond.MODIFIEDBY LIKE '%Czamara-Kessler%' THEN 1
                WHEN Cond.MODIFIEDBY LIKE '%Woods%' THEN 1
                WHEN Cond.MODIFIEDBY LIKE '%Ka%Urban%' THEN 1
                WHEN Cond.MODIFIEDBY LIKE '%Hoover%' THEN 1
                ELSE 0
             END
          ELSE
             CASE
                WHEN Cond.MODIFIEDBY = @Employee THEN 1
                ELSE 0
             END
       END = 1

 WHERE (@Employee IS NULL
       AND (Cond.MODIFIEDBY LIKE '%Chilano%' OR
            Cond.MODIFIEDBY LIKE '%Kennedy%' OR
            Cond.MODIFIEDBY LIKE '%Czamara-Kessler%' OR
            Cond.MODIFIEDBY LIKE '%Woods%' OR
            Cond.MODIFIEDBY LIKE '%Ka%Urban%' OR
            Cond.MODIFIEDBY LIKE '%Hoover%'))
    OR (@Employee IS NOT NULL
       AND (Cond.MODIFIEDBY = @Employee))

 

[earljgray]

Thank you!

I hadn't had the chance to pop it into the procedure - but I will on Monday!!!

Crystal Reports Design/training/Consultation
earljgray@gmail.com