CODEWITHSUNDEEP
mysql
NewLearner
NewLearner

Reputation: 59

Populating column based on conditions on other 2 columns

I've a situation in which I need to

print "SAME" - where

either of 2 columns is empty or

both are empty or

repeating the same pattern

print "UP" - if

COL2 greater than COL1 (only at that point, if it continues the same pattern in following rows then it should only print "SAME" as mentioned above)

print "DOWN" - if

COL1 greater than COL2 (only at that point, if it continues the same pattern in following rows then it should only print "SAME" as mentioned above)

e.g.

DATE         **COL1  COL2    RESULT**
2019-01-01      0             SAME
2019-01-02      1     2       **UP**
2019-01-03      2     3       SAME
2019-01-04      3     4       SAME
2019-01-05      4     1       **DOWN**
2019-01-06      5     2       SAME
2019-01-07      6     3       SAME
2019-01-08      7     7       SAME
2019-01-09      8             SAME
2019-01-10      9     8       SAME
2019-01-11                    SAME
2019-01-12            4       SAME
2019-01-13      7     8       **UP**

Upvotes: 1

Views: 59

Answers (1)

Strawberry
Strawberry

Reputation: 33935

The beginning of this solution is a little contrived, but perhaps someone can think how to remove the contrivance. Also, because of the slightly hacky way I've done this, as written, this solution is only appropriate for versions prior to 8.0...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(DATE DATE PRIMARY KEY
,COL1 INT NULL
,COL2 INT NULL
);

INSERT INTO my_table VALUES
('2019-01-01',0,NULL),
('2019-01-02',1,2),
('2019-01-03',2,3),
('2019-01-04',3,4),
('2019-01-05',4,1),
('2019-01-06',5,2),
('2019-01-07',6,3),
('2019-01-08',7,7),
('2019-01-09',8,NULL),
('2019-01-10',9,8),
('2019-01-11',NULL,NULL),
('2019-01-12',NULL,4),
('2019-01-13',7,8);  

SELECT date, x result FROM
     ( SELECT date
     , CASE WHEN @p = result THEN @i:= 'same' ELSE @i:=result END x
     , @p:= result
  FROM 
     ( SELECT date
            , @prev := CASE WHEN col1 > col2 THEN 'down' WHEN col1 < col2 THEN 'up' ELSE @prev END result
         FROM my_table
            , (SELECT @prev:='same') vars -- this is the contrived part
        ORDER
           BY date
     ) n
     , (SELECT @p:=null,@i:=null) more_vars 
 ORDER
    BY date
    ) b
 ORDER BY date;
  +------------+--------+
  | date       | result |
  +------------+--------+
  | 2019-01-01 | same   |
  | 2019-01-02 | up     |
  | 2019-01-03 | same   |
  | 2019-01-04 | same   |
  | 2019-01-05 | down   |
  | 2019-01-06 | same   |
  | 2019-01-07 | same   |
  | 2019-01-08 | same   |
  | 2019-01-09 | same   |
  | 2019-01-10 | same   |
  | 2019-01-11 | same   |
  | 2019-01-12 | same   |
  | 2019-01-13 | up     |
  +------------+--------+

Upvotes: 1

Related Questions