如何利用COUNT在日期区间返回1或0？存储过程异常排查

Let's break down the issue first: your current stored procedure only counts bookings that are fully contained within the date range you're checking (StartDate >= @DateCheck AND EndDate <= @DateCheck2). That's why it returns 0 when the check range overlaps with an existing booking but doesn't fully enclose it—you're validating the wrong condition.

What you actually need is to check if there's any overlap between the existing booking dates and the date range you're verifying. Here's how to fix this:

Step 1: Correct the Overlap Logic #

Two date ranges [A, B] and [C, D] overlap if:

• The start of one range is <= the end of the other, and
• The end of one range is >= the start of the other

Translated to your query, that means replacing your WHERE clause condition with:

StartDate <= @DateCheck2 AND EndDate >= @DateCheck

Step 2: Optimize the Query (Use EXISTS Instead of COUNT) #

Using COUNT() will scan all matching rows, but we only care if at least one overlapping booking exists. EXISTS is more efficient because it stops searching as soon as it finds the first match.

Revised Stored Procedure #

Here's the updated code that returns 1 if there's an overlap (charter is unavailable) and 0 if there's no overlap (charter is available):

CREATE PROCEDURE [dbo].[CheckCharterDate] 
    @DateCheck date, 
    @DateCheck2 date, 
    @charterID int
AS
BEGIN
    -- Return 1 if any overlapping booking exists, 0 otherwise
    SELECT CASE 
        WHEN EXISTS (
            SELECT 1 
            FROM Booking 
            WHERE CharterID = @charterID
              AND StartDate <= @DateCheck2 
              AND EndDate >= @DateCheck
        ) THEN 1
        ELSE 0
    END AS IsCharterUnavailable;
END

Why This Works #

Let's test with your problematic scenario:

• Existing booking: StartDate = '2024-05-01', EndDate = '2024-05-10'
• Check range: @DateCheck = '2024-05-05', @DateCheck2 = '2024-05-15'

The condition StartDate <= @DateCheck2 (2024-05-01 <= 2024-05-15) is true, and EndDate >= @DateCheck (2024-05-10 >= 2024-05-05) is also true. The EXISTS clause finds this booking, so the procedure returns 1—correctly indicating the charter is unavailable during that range.

Bonus: Handle Edge Cases #

This logic also accounts for edge cases like:

• The check range fully contains an existing booking (your original working scenario)
• An existing booking fully contains the check range
• The check range starts before an existing booking ends, and ends after the booking starts

内容的提问来源于stack exchange，提问作者MrDarkness96