I want to exclude records that do not fall within the value range specified in case when statement - SQL/MySQL

Question

I'm trying to set a seasonal range for specific dates from the DTBL_SCHOOL_DATES table. Below is my logic that sets the seasons within a given range based on the year and its region.

CASE 
        WHEN RTRIM(dtbl_school_dates.local_school_year) = '2021-2022' THEN 
            CASE 
                WHEN RTRIM(dtbl_schools_ext.region) = 'Bay Area' AND 
                    CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '08/07/2021' and '09/08/2021' THEN 'FALL'
                WHEN RTRIM(dtbl_schools_ext.region) = 'Bay Area' AND 
                    CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '11/27/2021' and '12/15/2021' THEN 'WINTER'
                WHEN RTRIM(dtbl_schools_ext.region) = 'Bay Area' AND 
                    CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '03/04/2022' and '03/22/2022' THEN 'SPRING'
                WHEN RTRIM(dtbl_schools_ext.region) = 'Central Valley' AND 
                    CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '07/31/2021' and '09/01/2021' THEN 'FALL'
                WHEN RTRIM(dtbl_schools_ext.region) = 'Central Valley' AND 
                    CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '11/27/2021' and '12/15/2021' THEN 'WINTER'
                WHEN RTRIM(dtbl_schools_ext.region) = 'Central Valley' AND 
                    CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '02/19/2022' and '03/08/2022' THEN 'SPRING'
                WHEN RTRIM(dtbl_schools_ext.region) = 'Los Angeles' AND 
                    CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '08/14/2021' and '09/15/2021' THEN 'FALL'
                WHEN RTRIM(dtbl_schools_ext.region) = 'Los Angeles' AND 
                    CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '11/27/2021' and '12/15/2021' THEN 'WINTER'
                WHEN RTRIM(dtbl_schools_ext.region) = 'Los Angeles' AND 
                    CAST(dtbl_school_dates.date_value AS DATE) BETWEEN '03/04/2022' and '03/22/2022' THEN 'SPRING' 
                ELSE 'NOT IN RANGE' 
            END 
        ELSE FTBL_TEST_SCORES.test_admin_period 
    END AS "C4630"

But whenever the dates are not within the range specified in the logic, I want them to be ignored. AKA "out of range" values ​​should be excluded. I tried using FTBL_TEST_SCORES.test_admin_period not null and since no value in the database is null they won't work.

Values ​​that are not in the range should be excluded from the results, how do I achieve this in the where clause

I tried using Alias ​​for limiting but it doesn't work. I'm not sure if it's possible to assign a value to a specific field in a case statement, like Case when 'a' then field ='B' end

Values ​​that are not in the range should be excluded from the results, how do I achieve this in the where clause

Answer 1

Without being able to see the complete query, it's impossible to come up with the best solution. The simplest solution is to add the criteria as a HAVING clause:

HAVING `C4630` <> 'NOT IN RANGE'

I'm confused about your date format. If this is supposed to be MySQL queries, they should be in yyyy-mm-dd format. Arguably, they should be in this format even on SQL Servers that support ambiguous native date formats.

Why are they all RTRIM and CAST? Data should be stored in the correct format and sanitized on input, if this is not possible, sanitize the data regularly rather than for every query.

It would be wise to move your seasons into their own table rather than defining them at query time. Then it's a simple connection to seasons. I'm using a subquery here, but hopefully you get the idea:

SELECT
    /* other columns */
    COALESCE(seasons.season, FTBL_TEST_SCORES.test_admin_period) AS `C4630`
FROM all_the_other_tables
LEFT JOIN (

    SELECT 'Bay Area' AS region, 'FALL' AS season, '2021-08-07' AS start, '2021-09-08' AS end UNION ALL
    SELECT 'Bay Area'          , 'WINTER'        , '2021-11-27'         , '2021-12-15'        UNION ALL
    SELECT 'Bay Area'          , 'SPRING'        , '2022-03-04'         , '2022-03-22'        UNION ALL
        
    SELECT 'Central Valley'    , 'FALL'          , '2021-07-31'         , '2021-09-01'        UNION ALL
    SELECT 'Central Valley'    , 'WINTER'        , '2021-11-27'         , '2021-12-15'        UNION ALL
    SELECT 'Central Valley'    , 'SPRING'        , '2022-02-19'         , '2022-03-08'        UNION ALL
        
    SELECT 'Los Angeles'       , 'FALL'          , '2021-08-14'         , '2021-09-15'        UNION ALL
    SELECT 'Los Angeles'       , 'WINTER'        , '2021-11-27'         , '2021-12-15'        UNION ALL
    SELECT 'Los Angeles'       , 'SPRING'        , '2022-03-04'         , '2022-03-22'

) AS seasons
    ON dtbl_school_dates.local_school_year = '2021-2022'
    AND dtbl_schools_ext.region = seasons.region
    AND dtbl_school_dates.date_value BETWEEN seasons.start AND seasons.end
WHERE (
    (dtbl_school_dates.local_school_year = '2021-2022' AND seasons.season IS NOT NULL) OR
    dtbl_school_dates.local_school_year <> '2021-2022'
);

Answer 2

You can repeat the entire CASE statement in the WHERE clause as follows:

select
    case
        when x then y 
        when a then b 
        when c then d 
        else 'NOT IN RANGE'
    end as foo
from
    table t 
where 
    case
        when x then y 
        when a then b 
        when c then d 
        else 'NOT IN RANGE'
    end <> 'NOT IN RANGE'

Or you can use a subquery (or CTE) like this:

select * 
from (
    select
        case
            when x then y 
            when a then b 
            when c then d 
            else 'NOT IN RANGE'
        end as foo
    from
        table t 
) 
where foo <> 'NOT IN RANGE'