asked on

WHERE CLAUSE problem

How do I in where clause to select correct data in the following statement:
--------------------------------------------------------------------------------------
select name, date1, date2, date3
from table
where name=@Param and
case
when @Param ="a", date1 between '2/1/2012' and '2/29/2012' THEN 1
when @Param="b", date2 between '2/1/2012' and '2/29/2012' THEN 1
when @Param ="c", date3 between '2/1/2012' and '2/29/2012' THEN 1
else 0
end =1

I only get 2 row. I should have 125.
---------------------------------------------------------------------------
--------------------------------------------------------------------------------------
select name, date1, date2, date3
from table
where name=@Param and
( date1 between '2/1/2012' and '2/29/2012' or
date2 between '2/1/2012' and '2/29/2012' or
date3 between '2/1/2012' and '2/29/2012' )

Obviously, '2/1/2012' and '2/29/2012' won't limited all dates because of "or "

Appreciate for great help!

Anthony Perkins

Have you tried this:

WHERE   name = @Param
        AND CASE WHEN @Param = "a"
                      AND date1 BETWEEN '2/1/2012' AND '2/29/2012' THEN 1
                 WHEN @Param = "b"
                      AND date2 BETWEEN '2/1/2012' AND '2/29/2012' THEN 1
                 WHEN @Param = "c"
                      AND date3 BETWEEN '2/1/2012' AND '2/29/2012' THEN 1
                 ELSE 0
            END = 1

Open in new window

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

Or:

WHERE   name = @Param AND (
        @Param = 'a' AND date1 BETWEEN '20120201' AND '20120229' OR
        @Param = 'b' AND date2 BETWEEN '20120201' AND '20120229' OR
        @Param = 'c' AND date2 BETWEEN '20120201' AND '20120229')

Open in new window

Scott Pletcher

In case date1 and date2 are datetime, or converted to datetime, I suggest this style for checking dates instead:

WHERE name = @Param AND (
(@Param = 'a' AND date1 >= '20120201' AND date1 < '20120301') OR
(@Param = 'b' AND date2 >= '20120201' AND date2 < '20120301') OR
(@Param = 'c' AND date2 >= '20120201' AND date2 < '20120301') )

Technically I don't think you need the extra parens, but in my view it makes it clearer to the reader.

sharon2011

ASKER

Thank you all. I found the reason I only got 2 rows instead of 125. There is another condition that need included NULL value. So the where clause should be:

where name=@Param and (division in (@div) or division is null)
and
case
when @Param ='a' and date1 between '2/1/2012' and '2/29/2012' THEN 1
when @Param='b' and date2 between '2/1/2012' and '2/29/2012' THEN 1
when @Param ='c' and date3 between '2/1/2012' and '2/29/2012' THEN 1
else 0
end =1