MySQL advanced calculation based on two columns

I’m trying to figure out if I can use MySQL workbench to give me some advanced calculations based on id, statement_one and latest_statement. My base table looks like this:

Basically I would like the test column to shown as below:

• for id=1, the test column show “1” from 2019Q3 to 2021Q2 and everything else shows “0”.
• for id=22, the test column show “1” from 2019Q2 to 2021Q3 and everything else shows “0”.

Anyone knows which function I can use to create a calculation based on the statement_one and last_statement range for each id? Thank you in advance for your help here!

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You can try a query like this:

First I get first_statement (MIN) and last_statement (MAX) for each id, then I JOIN cte subquery with the original table and use CASE statement to calculate test column.

MySql >= 8.0

WITH cte AS (SELECT id,  MIN(statement_one) AS fs, MAX(latest_statement) AS ls
             FROM t
             GROUP BY id)
             
SELECT *,
       CASE WHEN quarter >= cte.fs AND quarter <= cte.ls THEN 1 ELSE 0 END AS test
FROM t
INNER JOIN cte ON t.id = cte.id;

MySql < 8.0

SELECT *,
       CASE WHEN quarter >= sq.fs AND quarter <= sq.ls THEN 1 ELSE 0 END AS test
FROM t
INNER JOIN (SELECT id,  MIN(statement_one) AS fs, MAX(latest_statement) AS ls
             FROM t
             GROUP BY id) AS sq ON t.id = sq.id;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0