Solved

WHERE CLAUSE problem

Posted on 2012-03-28
5
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 500 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 69

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

730 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