Developer Developer - 2 months ago 14
SQL Question

Conditional Where when using Group By

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

enter image description here

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

Is this the logic that you want?

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