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?
- 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
