关于时间区间的WHERE CASE WHEN SQL及闭店时段过滤BrandCode的SQL需求

Hey there! Let's tackle your SQL needs with clear, practical examples and explanations, just like you asked.

1. Time Range Filtering with CASE WHEN in the WHERE Clause #

Sometimes you need dynamic time range rules based on specific conditions—using CASE WHEN directly in the WHERE clause lets you handle flexible, context-aware filtering. For example, let's say you want to apply different visit time ranges depending on whether the visit falls on a weekend or weekday.

Example SQL #

SELECT 
    CustomerID,
    VisitTime,
    BrandCode
FROM 
    CustomerVisits
WHERE 
    VisitTime BETWEEN 
        CASE 
            -- Weekend visits: filter 10 AM to 10 PM (adjust weekday values for your SQL dialect: e.g., DAYOFWEEK() returns 1 for Sunday in MySQL)
            WHEN DATEPART(weekday, VisitTime) IN (1,7) THEN DATEADD(day, DATEDIFF(day, 0, VisitTime), '10:00:00')
            -- Weekday visits: filter 8 AM to 9 PM
            ELSE DATEADD(day, DATEDIFF(day, 0, VisitTime), '08:00:00')
        END
        AND 
        CASE 
            WHEN DATEPART(weekday, VisitTime) IN (1,7) THEN DATEADD(day, DATEDIFF(day, 0, VisitTime), '22:00:00')
            ELSE DATEADD(day, DATEDIFF(day, 0, VisitTime), '21:00:00')
        END;

How it works: #

• The CASE WHEN checks the day of the week for each visit, then dynamically sets the start/end times for the BETWEEN condition.
• Adjust the weekday values and time ranges to match your actual business rules and SQL dialect.

2. Filter BrandCode Excluding KFC's Closed Hours #

First, let's define KFC's closed period—we'll use 2:00 AM to 6:00 AM (swap this for your actual closed hours). The core goal is to exclude KFC visits that happen during closed hours, while optionally keeping non-KFC visits (adjust the logic based on your needs).

Example 1: Keep valid visits (non-KFC OR KFC outside closed hours) #

This is the most common use case—keep all non-KFC visits, plus KFC visits that happen during operating hours:

SELECT 
    CustomerID,
    VisitTime,
    BrandCode
FROM 
    CustomerVisits
WHERE 
    -- Either it's not KFC, or it's KFC and the visit is outside closed hours
    BrandCode != 'KFC'
    OR 
    (
        BrandCode = 'KFC'
        AND 
        -- Extract the time component: use TIME(VisitTime) for MySQL, CONVERT(time, VisitTime) for SQL Server
        CONVERT(time, VisitTime) NOT BETWEEN '02:00:00' AND '06:00:00'
    );

Example 2: Only keep KFC visits outside closed hours #

If you want to filter out all non-KFC brands entirely, and only keep valid KFC visits:

SELECT 
    CustomerID,
    VisitTime,
    BrandCode
FROM 
    CustomerVisits
WHERE 
    BrandCode = 'KFC'
    AND CONVERT(time, VisitTime) NOT BETWEEN '02:00:00' AND '06:00:00';

Sample Test Data #

Let's create a sample CustomerVisits table to test the above queries. This uses SQL Server syntax—adjust the DATETIME type to DATETIME/TIMESTAMP for other dialects:

CREATE TABLE CustomerVisits (
    CustomerID INT,
    VisitTime DATETIME,
    BrandCode VARCHAR(20)
);

INSERT INTO CustomerVisits VALUES
(1, '2024-05-20 09:30:00', 'KFC'), -- Weekday, valid KFC visit
(2, '2024-05-21 03:15:00', 'KFC'), -- KFC during closed hours (will be filtered out)
(3, '2024-05-25 14:45:00', 'KFC'), -- Weekend, valid KFC visit
(4, '2024-05-20 07:45:00', 'McDonalds'), -- Weekday, non-KFC (excluded by time range query)
(5, '2024-05-22 23:00:00', 'KFC'), -- Valid KFC visit (outside closed hours)
(6, '2024-05-26 04:30:00', 'BurgerKing'), -- Non-KFC, closed hours (kept in Example 1)
(7, '2024-05-20 20:30:00', 'KFC'); -- Weekday, valid KFC visit

Test Results Quick Check: #

• The time range query will exclude row 4 (weekday visit before 8 AM) and include all other valid time-range matches.
• Example 1 of the KFC filter will exclude row 2 (KFC during closed hours) and keep all other rows.

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