CASE Statement inside HAVING clause

Hey,

I have the following SQL Query:


SELECT	C.ID AS ClientID,
		B.ID AS BookingID,
		C.Title AS Title,
		C.Fore AS FirstName,
		C.Name AS LastName,
		C.Mail AS Email,
		dbo.fnTrimStringOnLineBreak(C.Address, 0) AS Address1,
		dbo.fnTrimStringOnLineBreak(C.Address, 1) AS Address2,
		dbo.fnTrimStringOnLineBreak(C.Address, 2) AS Address3,
		C.State AS County,
		R.Name AS Region,
		S.Code AS PropertyCode,
		S.Name AS PropertyName,
		B.TotCost AS ValueOfBooking,
		CONVERT(Char(11), B.Created, 13) AS DateOfBooking,
		CONVERT(Char(11), B.Depart, 13) AS HolidayFrom,
		CONVERT(Char(11), DATEADD(DAY,Q.Nights, B.Depart), 13) AS HolidayTo,
		Q.Nights AS Duration,
		CASE
			WHEN EXISTS(
						  SELECT * FROM Pax Px
						  WHERE Px.BookingID = B.ID AND Px.Alt = 1
					    )
			THEN 'Yes' ELSE 'No'
		END AS Children,
		CASE
			WHEN EXISTS(
						  SELECT * FROM Pax Px
						  WHERE Px.BookingID = B.ID AND Px.Alt = 2
					    )
		THEN 'Yes' ELSE 'No'
		END AS Infants,
		Px3.Age AS LeadGuestAge,
		Px3.Sex AS LeadGuestGender,
		DiscountsUsed,
		CASE
			WHEN EXISTS
			(
				SELECT I.Notes
				FROM Invoice I
				WHERE I.SourceID = B.ID AND I.Notes = 'Pet'
			)
			THEN 'Yes'
			ELSE 'No'
		END AS Pets,
		L.Name AS BookingStatus,
		(
			SELECT TOP 1 Code
			FROM Enquiry E
			WHERE E.LinkID = C.ID
			ORDER BY DATE DESC
		) AS SourceCode
FROM Client C
INNER JOIN Booking B ON B.ClientID = C.ID
LEFT OUTER JOIN Quote Q ON Q.ID = B.QuoteID
LEFT OUTER JOIN Supplier S ON S.Code = Q.Code
LEFT OUTER JOIN Lookup L ON L.ID = B.SID
LEFT OUTER JOIN Region R ON R.ID = S.RegionID
LEFT OUTER JOIN Pax Px ON Px.BookingID = B.ID AND Px.Alt = 1 -- Children
LEFT OUTER JOIN Pax Px2 ON Px2.BookingID = B.ID AND Px.Alt = 2 -- Infants
LEFT OUTER JOIN Pax Px3 ON Px3.ID = B.LeadPaxID -- Sex
LEFT OUTER JOIN Invoice I ON I.SourceID = B.ID AND I.Notes = 'Pet' -- Pets
CROSS APPLY(
			   SELECT I.Notes + ', '
			   FROM Invoice I
			   WHERE I.SourceID = B.ID AND I.Notes LIKE '%Discount%'
			   FOR XML PATH('')
			) D (DiscountsUsed)
WHERE (B.SID = @StatusID OR @StatusID IS NULL)
AND (@BookingDateFrom IS NULL OR B.Created >= @BookingDateFrom)
AND (@BookingDateTo IS NULL OR B.Created <= @BookingDateTo)
AND (@HolidayStartDateFrom IS NULL OR B.Depart >= @HolidayStartDateFrom)
AND (@HolidayStartDateTo IS NULL OR B.Depart <= @HolidayStartDateTo)
AND (S.RegionID = @RegionID OR @RegionID IS NULL)
AND (S.ID = @PropertyID OR @PropertyID IS NULL)
AND (@HolidayValueRangeFrom IS NULL OR B.TotCost >= @HolidayValueRangeFrom)
AND (@HolidayValueRangeTo IS NULL OR B.TotCost <= @HolidayValueRangeTo)
AND (Q.Nights = @HolidayDuration OR @HolidayDuration IS NULL)
AND (Px3.Age >= @LeadGuestAgeFrom OR @LeadGuestAgeFrom IS NULL)
AND (Px3.Age <= @LeadGuestAgeTo OR @LeadGuestAgeTo IS NULL)
AND (Px3.Sex = @LeadGuestSex OR @LeadGuestSex IS NULL)
GROUP BY	C.ID,
			B.ID,
			C.Title,
			C.Fore,
			C.Name,
			C.Mail,
			C.Address,
			C.State,
			R.Name,
			S.Code,
			S.Name,
			B.TotCost,
			B.Created,
			B.Depart,
			Q.Nights,
			Px3.Age,
			Px3.Sex,
			D.DiscountsUsed,
			L.Name
HAVING  (
			(Count(Px.ID) >= @Children OR @Children IS NULL)
		AND
			(Count(Px2.ID) >= @Infants OR @Infants IS NULL)
		AND
			(Count(I.ID) >= @Pets OR @Pets IS NULL)
		)

I want to change the HAVING like so:

IF @Children = 0 THEN it should be:

(Count(Px.ID) = @Children OR @Children IS NULL)

IF @Children = 1 THEN it should be:

(Count(Px.ID) >= @Children OR @Children IS NULL)

I tried adding a CASE statement but that didn’t work. How can i do this?

Thanks,

You can write the statement like:
(@Children = 0 AND
(Count(Px.ID) = @Children OR @Children IS NULL) ) OR (@Children = 1 AND (Count(Px.ID) >= @Children OR @Children IS NULL) )

Your suggestion didn’t work, i tried this:


HAVING		(@Children = 0 AND
(Count(Px.ID) = @Children OR @Children IS NULL) ) OR (@Children = 1 AND (Count(Px.ID) >= @Children OR @Children IS NULL) )
AND
(@Infants = 0 AND
(Count(Px.ID) = @Infants OR @Infants IS NULL) ) OR (@Infants = 1 AND (Count(Px.ID) >= @Infants OR @Infants IS NULL) )
AND
(@Pets = 0 AND
(Count(Px.ID) = @Pets OR @Pets IS NULL) ) OR (@Pets = 1 AND (Count(Px.ID) >= @Pets OR @Pets IS NULL) )

It brought back nothing so i edited it abit and changed it to this:


HAVING	(
			(@Children = 0 AND Count(Px.ID) = 0 OR @Children IS NULL) OR (@Children = 1 AND Count(Px.ID) >= 1 OR @Children IS NULL)
		)
		AND
		(
			(@Infants = 0 AND Count(Px2.ID) = 0 OR @Infants IS NULL) OR (@Infants = 1 AND Count(Px2.ID) >= 1 OR @Infants IS NULL)
		)
		AND
		(
			(@Pets = 0 AND Count(I.ID) = 0 OR @Pets IS NULL) OR (@Pets = 1 AND Count(I.ID) >= 1 OR @Pets IS NULL)
		)

That nearly worked but when it goes through all 3 it doesnt bring anything back. For @Children and @Infants it works but when i used them both together it doesnt work. Also @pets doesnt work at all. It’s like it doesn’t even loop through each one.

Any ideas?