Conditional Having Statement

[tiptap]

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

IF @month <> 0 & @diffFuture = 0 & @showDate <> 0
	
	HAVING (events.eventID = @eventID) 
	AND 	(events.enabled = 1)
	AND 	(MONTH(tickets_1.ticketStartDate) = MONTH(GETDATE())) 
	AND	(YEAR(tickets_1.ticketStartDate) = YEAR(GETDATE()))

IF @month <> 0 & @diffFuture <> 0 & @showDate <> 0

	HAVING (events.eventID = @eventID) 
	AND 	(events.enabled = 1)
	AND 	(DATEPART(MONTH,tickets_1.ticketStartDate) = MONTH(@start)) 
	AND 	(DATEPART(YEAR,tickets_1.ticketStartDate) = YEAR(@start))

ELSE							
	
	HAVING (events.eventID = @eventID) 
	AND 	(events.enabled = 1)		
	AND 	(DATEPART(MONTH,tickets_1.ticketStartDate) = @month) 
	AND	(DATEPART(YEAR,tickets_1.ticketStartDate) = @year)

But how would i turn that into a conditional HAVING.... I thought the below would work

        HAVING (events.eventID = @eventID) 
	AND 	(events.enabled = 1)	
        CASE
	         WHEN @month <> 0 & @diffFuture = 0 & @showDate <> 0 THEN
                  	    (MONTH(tickets_1.ticketStartDate) = MONTH(GETDATE())) 
	                    AND	(YEAR(tickets_1.ticketStartDate) = YEAR(GETDATE()))
                WHEN...
        END

Any ideas?

[Delerna]

Perhaps something like
[code=sql]
AND MONTH(tickets_1 .ticketStartDat e) =
case when @month <> 0 & @showDate<>0 then
case when @diffFuture = 0 then
MONTH(GETDATE() )
else
MONTH(@start)
end
else
@month
end
[/code]

ditto for the year

[Delerna]

alternatively
@month, @diffFuture and @showDate look as though they will be either 0 or 1

so perhaps something like this will work
[code=sql]
AND MONTH(tickets_1 .ticketStartDat e)
=MONTH(GETDATE( ))*(1-@diffFuture)+MO NTH(@start)*@di ffFuture
[/code]

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

[tiptap]

Great that makes sense.

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

Would i need to wrap that statement in an IF?

[Delerna]

in other words if @Month=0 you want to see every month?

You need somehow to say

MONTH(tickets_1 .ticketStartDat e)>@Month

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

MONTH(tickets_1 .ticketStartDat e)=@Month


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



By the way, which one makes sense?

[Delerna]

Im thinkin OR to handle that case

[code=sql]
AND MONTH(tickets_1 .ticketStartDat e)
=MONTH(GETDATE( ))*(1-@diffFuture)+MO NTH(@start)*@di ffFuture

OR MONTH(tickets_1 .ticketStartDat e)
>case when @Month=0 then 0 else 12 end
[/code]

There is no month > 12 so the OR will only take effect when @Month=0


Again, just ideas

[Delerna]

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

[code= sql]
HAVING sum(Qty)>100 and Avg(Qty)>50
[/code]

You are not using agregates as filters so the more usual approach is
[code= sql]
WHERE month(Dte)=2 and year(Dte)=2009
[/code]

[tiptap]

ill give those a go now and report back. cheers for the help so far!