?
Solved

WHERE CLAUSE problem

Posted on 2012-03-28
5
Medium Priority
?
185 Views
Last Modified: 2012-03-28
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!
0
Comment
Question by:sharon2011
5 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37778073
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

0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 37778083
Is this what you want to achieve?


select name, date1, date2, date3
from table_name
where name=@Param OR
 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
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37778084
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

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37778456
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.
0
 

Author Comment

by:sharon2011
ID: 37778489
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
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question