Question 20450 in Mysql Question posted in
The official MySQL documentation can be found here.

I want to exclude the records that were not part my value range specified in case when statement – SQL/MySQL

I am trying to set season ranges for specific dates coming from my DTBL_SCHOOL_DATES table. Below is my logic which works based on the year and its region to set the season per a given range.

    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 date is not in the range specified in logic, I would want them to be ignored. AKA, The value NOT IN RANGE Should be excluded. I tried using FTBL_TEST_SCORES.test_admin_period is not null and they won’t work due to no values from db being null.

NOT IN RANGE VALUE SHOULD BE excluded in results, how do I achieve that in where clause

I tried using Alias to restrict but they won’t work.
I am unsure if I can assign values to a particular field in the case statements like Case when ‘a’ then field =’B’ end

NOT IN RANGE VALUE SHOULD BE excluded in results, how do I achieve that in where clause

Tags:

2

Answers


  1. 0

    You can either repeat the entire CASE statement in the WHERE clause like this:

    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'
    Login or Signup to reply.
  2. 0

    Without being able to see the full query it is impossible to suggest the optimal solution. The simplest solution would be to add the criterion as a HAVING clause:

    HAVING `C4630` <> 'NOT IN RANGE'

    I am confused by your date formats. If this is supposed to be a MySQL query, they should be in yyyy-mm-dd format. Arguably, they should be in this format even on SQL Server, which supports ambiguous local date formats.

    Why all the RTRIMs and CASTs? The data should be stored in the correct format and sanitized upon entry, and if you cannot achieve that, periodically sanitize the data instead of doing it for every query.

    It would be sensible to move your seasons into their own table instead of defining them at query time. Then it is a simple join to seasons. I have used 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'
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search