[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

WHERE CLAUSE problem

Posted on 2012-03-28
5
Medium Priority
?
183 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 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

649 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