SQL Return 1,0 in new variable based on case when statement referring to multiple other variables

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?

Lysenko

Screenshots make things so difficult... Why not set up and share an SQL fiddle? – Melany

Ah. Good point. Will do. – Lysenko

@DanFarrell updated. sorry about that. it's late. – Lysenko

What is the datatype for record_date ? It does not look like standard date type of MySQL – Song

@MadhurBhaiya I loaded it from a csv --> the fiddle. datatype may have not translated well. – Lysenko

What is your MySQL server version ? – Song

@MadhurBhaiya 8.0.12 – Lysenko

@AlissaSwartz "Where the record_dates and ladder_levels aren't necessarily in sequential order" do you have any primary key in your table ? Always remember that data is in unordered fashion. So, is it fair to assume that record_dates will always be inserted in sequential manner ? – Song

@MadhurBhaiya, ah, good point. I made up this data set so the real primary key is missing. each record_date + ladder_level record has a unique identifier. record_date is hypothetically inserted in a sequential manner, but I wouldn't bet the house on it being done 100% of the time. – Lysenko

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

Song

WOW! Thank you so much. Can you explain what the following do: @rd @ll @dt – Lysenko

@AlissaSwartz give me some time. Will add explanation when on PC. These are basically user defined variables. – Song

No worries. Thank you so much. – Lysenko

@AlissaSwartz please check the updated answer for explanation. Let me know if something is unclear. – Song