Solved

WHERE CLAUSE problem

Posted on 2012-03-28
5
170 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 39

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:ScottPletcher
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
combining condition to rerun if failure within BEGIN..END 27 87
Determine log file requirements 7 35
xpath sql query 2008 8 43
TSQL previous 5 25
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now