Conditional Where when using Group By

Question

I have a working procedure to show the report for given range of period. The below proc finds all the bookings for the given period of time and shows for each month group

CREATE PROCEDURE [dbo].[GetSummaryOverviewReport] (
    @StartDate SMALLDATETIME = NULL
    ,@EndDate SMALLDATETIME = NULL
    ,@IncludeVAT BIT = 0
    ,@AddedAfter SMALLDATETIME
    ,@HasObservation BIT = 0
    ,@IncludeCancelledBooking BIT = 0
    )
AS
BEGIN
    SET NOCOUNT ON

    BEGIN
        SELECT YEAR(StartTime) [Year]
            ,MONTH(StartTime) [Month]
            ,COUNT(BookingId) [BookingCount]
            ,SUM(CASE 
                    WHEN IsVAT = 1
                        AND @IncludeVAT = 1
                        THEN (Price / 100) * 80
                    ELSE Price
                    END) AS TotalPrice
            ,PaidCount = sum(CASE 
                    WHEN B.PaymentStatus = 'FullyPaid'
                        THEN 1
                    ELSE 0
                    END)
            ,PaidSum = sum(CASE 
                    WHEN B.PaymentStatus = 'FullyPaid'
                        THEN (
                                CASE 
                                    WHEN IsVAT = 1
                                        AND @IncludeVAT = 1
                                        THEN (Price / 100) * 80
                                    ELSE Price
                                    END
                                )
                    ELSE 0
                    END)
            ,C.CategoryId
            ,CategoryName
        FROM Category c
        LEFT JOIN Booking B ON C.CategoryId = B.CategoryId
        WHERE B.AddedDateTime > @AddedAfter
            AND ISNULL(B.IsCancelled, 0) = 0
            AND (
                (
                    B.StartTime BETWEEN @StartDate
                        AND @EndDate
                    )
                OR (
                    B.StartTime BETWEEN @StartDate
                        AND @EndDate
                    )
                )
            AND (
                @HasObservation = 0
                OR B.PatientID IN (
                    SELECT PatientID
                    FROM PatientXObservation O
                    WHERE ObservationId IN (
                            1
                            ,2
                            ,4
                            )
                    )
                )
        GROUP BY YEAR(StartTime)
            ,MONTH(StartTime)
            ,C.CategoryId
            ,CategoryName
        ORDER BY 1
            ,2
            ,CategoryName
    END
END

Now we want to add a filter @IncludeCancelledBooking parameter. So user can select whether to show cancelled bookings or not. As of now the procedure ignores the cancelled booking and shows only non cancelled booking..

I couldn't able to find out how to add this condition while grouping the records. I need to use AND ISNULL(B.IsCancelled, 0) = 0 based on @IncludeCancelledBooking

Answer 1

Is this the logic that you want?

(@IncludeCancelled <> 'Y' OR Booking ISNULL(B.IsCancelled, 0) = 0)