CASE WHEN in SQL with CONCAT

Question

I need to create an extra column in a table with the field "WinterSeasonYYYY" of "SummerSeasonYYYY" being YYYY the year of that record. Meaning that if the date is 01-01-2021 it will be considered WinterSeason2020, between March and August would be SummerSeason of the current year (01-07-2021 would be SummerSeason2021), and after August would be winter season (01-09-2021 would be WinterSeason2021)

So far I have been trying to assess this via CASE-WHEN:

SELECT *, 
CASE
WHEN MONTH(date) < 3 THEN
        extracted_year = EXTRACT(YEAR FROM (date)-1)
        CONCAT('WinterSeason',extracted_year);
WHEN MONTH(DATA_VENDA) BETWEEN 3 AND 8
        SET extracted_year = year(date)
        CONCAT('SummerSeason',extracted_year);
ELSE 
        SET extracted_year = year(date)
        CONCAT('WinterSeason',extracted_year);
END CASE AS SEASON
FROM CAMPANHAS;

Any ideas of where is the catch and how can I solve it?

Answer 1

One approach is to subtract two months for the year calculation:

SELECT c.*,
       CONCAT(CASE WHEN MONTH(date) BETWEEN 3 AND 8
                   THEN 'SummerSeason' ELSE 'WinterSeason'
              END,
              YEAR(date - INTERVAL 2 MONTH)
             ) as season
FROM CAMPANHAS c;