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

AND MONTH(tickets_1.ticketStartDate) = 
    case when @month <> 0 & @showDate<>0 then
        case when @diffFuture = 0 then
            MONTH(GETDATE()) 
        else
             MONTH(@start)
        end
    else
        @month
    end
 

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

AND MONTH(tickets_1.ticketStartDate)
=MONTH(GETDATE())*(1-@diffFuture)+MONTH(@start)*@diffFuture
 

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

AND MONTH(tickets_1.ticketStartDate) 
=MONTH(GETDATE())*(1-@diffFuture)+MONTH(@start)*@diffFuture
 
OR MONTH(tickets_1.ticketStartDate) 
>case when @Month=0 then 0 else 12 end
 

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

HAVING sum(Qty)>100 and Avg(Qty)>50
 

You are not using agregates as filters so the more usual approach is

WHERE month(Dte)=2 and year(Dte)=2009
 

[tiptap]

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