By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

Conditional Having Statement

P: 5
Hey Guys,

I have a huge statement loads of if statements in... and its getting bigger.

On closer inspection there is only 3 difference in the select statement. so I thought I could cut the whole thing down to just 1 select statement if I have a conditional Having.

I've simplified the IF statement down a bit to give you an idea of what im trying to achieve

Expand|Select|Wrap|Line Numbers
  1. IF @month <> 0 & @diffFuture = 0 & @showDate <> 0
  2.  
  3.     HAVING (events.eventID = @eventID) 
  4.     AND     (events.enabled = 1)
  5.     AND     (MONTH(tickets_1.ticketStartDate) = MONTH(GETDATE())) 
  6.     AND    (YEAR(tickets_1.ticketStartDate) = YEAR(GETDATE()))
  7.  
  8. IF @month <> 0 & @diffFuture <> 0 & @showDate <> 0
  9.  
  10.     HAVING (events.eventID = @eventID) 
  11.     AND     (events.enabled = 1)
  12.     AND     (DATEPART(MONTH,tickets_1.ticketStartDate) = MONTH(@start)) 
  13.     AND     (DATEPART(YEAR,tickets_1.ticketStartDate) = YEAR(@start))
  14.  
  15. ELSE                            
  16.  
  17.     HAVING (events.eventID = @eventID) 
  18.     AND     (events.enabled = 1)        
  19.     AND     (DATEPART(MONTH,tickets_1.ticketStartDate) = @month) 
  20.     AND    (DATEPART(YEAR,tickets_1.ticketStartDate) = @year)
  21.  
But how would i turn that into a conditional HAVING.... I thought the below would work

Expand|Select|Wrap|Line Numbers
  1.         HAVING (events.eventID = @eventID) 
  2.     AND     (events.enabled = 1)    
  3.         CASE
  4.              WHEN @month <> 0 & @diffFuture = 0 & @showDate <> 0 THEN
  5.                           (MONTH(tickets_1.ticketStartDate) = MONTH(GETDATE())) 
  6.                         AND    (YEAR(tickets_1.ticketStartDate) = YEAR(GETDATE()))
  7.                 WHEN...
  8.         END
Any ideas?
Sep 2 '09 #1
Share this Question
Share on Google+
7 Replies


Delerna
Expert 100+
P: 1,134
Perhaps something like
Expand|Select|Wrap|Line Numbers
  1. AND MONTH(tickets_1.ticketStartDate) = 
  2.     case when @month <> 0 & @showDate<>0 then
  3.         case when @diffFuture = 0 then
  4.             MONTH(GETDATE()) 
  5.         else
  6.              MONTH(@start)
  7.         end
  8.     else
  9.         @month
  10.     end
  11.  
ditto for the year
Sep 3 '09 #2

Delerna
Expert 100+
P: 1,134
alternatively
@month, @diffFuture and @showDate look as though they will be either 0 or 1

so perhaps something like this will work
Expand|Select|Wrap|Line Numbers
  1. AND MONTH(tickets_1.ticketStartDate)
  2. =MONTH(GETDATE())*(1-@diffFuture)+MONTH(@start)*@diffFuture
  3.  
in other words, if f is either 1 or 0 then

(value1 * (1- f)) + (value2 * f ) = value1 when f=0 or value2 when f=1




It's just an idea, I will let you work out the rest of it if my assumption about the values of the variables is correct
Sep 3 '09 #3

P: 5
Great that makes sense.

Ok so what now if @month = 0. In that case I dont want MONTH(tickets_1.ticketsStartDate) in the Having statement.

Would i need to wrap that statement in an IF?
Sep 3 '09 #4

Delerna
Expert 100+
P: 1,134
in other words if @Month=0 you want to see every month?

You need somehow to say

MONTH(tickets_1.ticketStartDate)>@Month

when @Month=0 and when @Month<> 0 you need it to say

MONTH(tickets_1.ticketStartDate)=@Month


hmmmm ..................... thinking!



By the way, which one makes sense?
Sep 4 '09 #5

Delerna
Expert 100+
P: 1,134
Im thinkin OR to handle that case

Expand|Select|Wrap|Line Numbers
  1. AND MONTH(tickets_1.ticketStartDate) 
  2. =MONTH(GETDATE())*(1-@diffFuture)+MONTH(@start)*@diffFuture
  3.  
  4. OR MONTH(tickets_1.ticketStartDate) 
  5. >case when @Month=0 then 0 else 12 end
  6.  
There is no month > 12 so the OR will only take effect when @Month=0


Again, just ideas
Sep 4 '09 #6

Delerna
Expert 100+
P: 1,134
Also, not sure if you are aware but HAVING and WHERE work in the same way.

Generally you use HAVING when you need to filter by an AGGREGATE function

Expand|Select|Wrap|Line Numbers
  1. HAVING sum(Qty)>100 and Avg(Qty)>50
  2.  
You are not using agregates as filters so the more usual approach is
Expand|Select|Wrap|Line Numbers
  1. WHERE month(Dte)=2 and year(Dte)=2009
  2.  
Sep 4 '09 #7

P: 5
ill give those a go now and report back. cheers for the help so far!
Sep 4 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.