SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    CASE Statement inside HAVING clause

    Hey,

    I have the following SQL Query:

    Code:
    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,
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy

  2. #2
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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) )

  3. #3
    SitePoint Wizard billy_111's Avatar
    Join Date
    Jul 2009
    Posts
    1,683
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your suggestion didn't work, i tried this:

    Code:
    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:

    Code:
    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?
    Software Engineer ASP, ASP.NET, VB, PHP

    Kind regards
    Billy


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •