SQL Return 1,0 in new variable based on case when

Question

I'm trying to create a new variable that populates with a 1 (true), 0 (false) in MySQL based on a series of dates and ladder levels (null-E).

See fiddle: http://sqlfiddle.com/#!9/9975e1

Where the record_dates and ladder_levels aren't necessarily in sequential order. I'd like to return the ladder_change fieldvia a case when (?) statement, that says something like:

• First, look only within matching IDs (i.e. just for ID 324)
  • Then, something like: case when record_date2 > record_date1 AND (ladder_level2 < ladder_level1 OR ladder_level2>ladder_level1) then 1, else 0

Any tips on how to achieve this?

Answer 1

• Since there is no Primary Key defined, I am assuming that data is entered in sequential order on creation_date and ladder_level. Remember that data is stored in unordered fashion in MySQL.
• Firstly, we have to use a sub-select query to get the data in required order (as highlighted in previous step). It is noteworthy that Order By is executed after Select clause; so we will need to first get the data sorted, and then use the result-set as a Derived Table.
• Now, we will take the help of User-defined variables (session-level persistent and accessible). In another derived table user_init_vars, we initialize them.
• In the Select clause, we compare the current row's value against the previous row value. After comparison, we set the variable value to current row's value. You can think of it as looping technique, which we use in other programming languages like PHP, C++, Java etc.
• Case .. When expressions are used for comparison, and determining the ladder_change value.

---

Query #1

SELECT 
  dt.ID, 
  CASE WHEN DATE(@rd) <> DATE(dt.record_date) AND 
            dt.ladder_level > @ll 
       THEN 1 
       ELSE 0 
  END AS ladder_change, 
  @rd := dt.record_date AS record_date, 
  @ll := dt.ladder_level AS ladder_level 
FROM (SELECT ID, record_date, ladder_level 
      FROM conv_example 
      ORDER BY ID, record_date, ladder_level) AS dt 
CROSS JOIN (SELECT @rd := '', 
                   @ll := '') AS user_init_vars;

| ID    | ladder_change | record_date         | ladder_level |
| ----- | ------------- | ------------------- | ------------ |
| 324   | 0             | 2016-09-15 00:00:00 | a            |
| 324   | 0             | 2016-09-15 00:00:00 | b            |
| 324   | 0             | 2017-04-07 00:00:00 | b            |
| 324   | 0             | 2017-04-07 00:00:00 | c1           |
| 324   | 0             | 2018-09-08 00:00:00 | c1           |
| 324   | 0             | 2018-09-08 00:00:00 | e            |
| 1234  | 0             | 2013-04-03 00:00:00 |              |
| 1234  | 0             | 2014-07-03 00:00:00 | a            |
| 1234  | 1             | 2015-04-01 00:00:00 | b            |
| 1234  | 1             | 2016-09-15 00:00:00 | d            |
| 1234  | 0             | 2017-02-04 00:00:00 | b            |
| 1234  | 0             | 2017-04-03 00:00:00 | b            |
| 1234  | 1             | 2017-04-07 00:00:00 | c1           |
| 1234  | 1             | 2018-09-08 00:00:00 | e            |
| 31431 | 0             | 2013-04-03 00:00:00 |              |
| 31431 | 0             | 2014-07-03 00:00:00 | a            |
| 31431 | 1             | 2017-04-07 00:00:00 | c1           |
| 31431 | 1             | 2018-09-08 00:00:00 | e            |

---

View on DB Fiddle