Solved

WHERE CLAUSE problem

Posted on 2012-03-28
5
169 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

13 Experts available now in Live!

Get 1:1 Help Now