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

Mysql – SQL- Selecting from data based on a condition

I am trying to write a SQL, with a scenario:
If I have 3 years in my database- 2022,2023,2024. If the current month is anywhere between March 2023 to Feb 2024, it should return the year 2023 only. If the current month is between March 2024 and Feb 2025 (in this case my database would update to 2023,2024,2025 automatically), it should return 2024 and so on.
Can someone help me in this on how to approach?
Sample Scenario

I was able to get the current month by this syntax:

SELECT MONTH(CURDATE());

Not able to think how can I use this to get the output

Tags:

2

Answers


  1. 0

    The details you have shared are very minimal. Still, the below query might work

    SELECT 
CASE
    WHEN (MONTH(CURDATE()) <> 1 or MONTH(CURDATE()) <> 2) THEN YEAR(CURDATE())
    WHEN (MONTH(CURDATE()) = 1 or MONTH(CURDATE()) = 2) THEN YEAR(CURDATE())-1
END AS result

    If not, please share sample input table contents and expected output.

    Login or Signup to reply.
  2. 0

    To achieve the desired result, you can use a combination of SQL functions and conditional logic. The steps involve getting the current month, determining the appropriate year range, and then selecting the correct year based on that range. Here’s a sample SQL query to help you with this scenario:

    -- Assuming you have a table named 'years' that stores the years (2022, 2023, 2024) in a column called 'year'

-- Step 1: Get the current month
SELECT MONTH(CURDATE()) AS current_month;

-- Step 2: Determine the year range based on the current month
SELECT
  CASE
    WHEN MONTH(CURDATE()) BETWEEN 3 AND 12 THEN YEAR(CURDATE())
    WHEN MONTH(CURDATE()) BETWEEN 1 AND 2 THEN YEAR(CURDATE()) - 1
  END AS year_range_start,
  CASE
    WHEN MONTH(CURDATE()) BETWEEN 3 AND 12 THEN YEAR(CURDATE()) + 1
    WHEN MONTH(CURDATE()) BETWEEN 1 AND 2 THEN YEAR(CURDATE())
  END AS year_range_end;

-- Step 3: Use the year range to select the appropriate year from the 'years' table
SELECT year
FROM years
WHERE year BETWEEN
  (SELECT
    CASE
      WHEN MONTH(CURDATE()) BETWEEN 3 AND 12 THEN YEAR(CURDATE())
      WHEN MONTH(CURDATE()) BETWEEN 1 AND 2 THEN YEAR(CURDATE()) - 1
    END
  ) AND
  (SELECT
    CASE
      WHEN MONTH(CURDATE()) BETWEEN 3 AND 12 THEN YEAR(CURDATE()) + 1
      WHEN MONTH(CURDATE()) BETWEEN 1 AND 2 THEN YEAR(CURDATE())
    END
  );

    Let me explain the steps:

    1. The first step retrieves the current month using MONTH(CURDATE()).

    2. The second step uses a CASE statement to determine the year range based on the current month. If the month is between March and December (months 3 to 12), it considers the current year as the starting year of the range and the next year as the ending year. If the month is between January and February (months 1 to 2), it considers the previous year as the starting year of the range and the current year as the ending year.

    3. The final step selects the appropriate year from the ‘years’ table using the determined year range. It filters the rows based on the ‘year’ column falling between the start and end of the year range calculated in step 2.

    Keep in mind that you’ll need to replace ‘years’ with the actual name of your table storing the years. Also, ensure that the table contains the years (2022, 2023, 2024) or any other years based on the current date.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search