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,