1

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?

aks85
  • 695
  • 3
  • 11
  • 24
  • Screenshots make things so difficult... Why not set up and share an SQL fiddle? – erik258 Nov 05 '18 at 03:25
  • 1
    Ah. Good point. Will do. – aks85 Nov 05 '18 at 03:28
  • @DanFarrell updated. sorry about that. it's late. – aks85 Nov 05 '18 at 03:32
  • What is the datatype for record_date ? It does not look like standard date type of MySQL – Madhur Bhaiya Nov 05 '18 at 03:35
  • @MadhurBhaiya I loaded it from a csv --> the fiddle. datatype may have not translated well. – aks85 Nov 05 '18 at 03:36
  • What is your MySQL server version ? – Madhur Bhaiya Nov 05 '18 at 03:43
  • @MadhurBhaiya 8.0.12 – aks85 Nov 05 '18 at 03:44
  • @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 ? – Madhur Bhaiya Nov 05 '18 at 04:13
  • @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. – aks85 Nov 05 '18 at 04:30

1 Answers1

3
  • 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

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57