Conditional Column in Mysql

February 11, 2019, at 9:00 PM

I have an orders table which looks like this:

Customer    Date       Value
C1          2019-01-12     2
C2          2019-01-12     3
C3          2019-01-12    45
C4          2019-01-12    12
C1          2019-01-14     1
C3          2019-01-14     6
C1          2019-01-25     5

In this table, I want to add a column "Rewards", which returns yes if a customer has a transaction in the last 7 days and "no" if he has no transactions in the last 7 days.

Here's the desired output:

Customer  Date        Value Rewards
C1        2019-01-12      2 No
C2        2019-01-12      3 No
C3        2019-01-12     45 No
C4        2019-01-12     12 No
C1        2019-01-14      1 Yes
C3        2019-01-14      6 Yes
C1        2019-01-25      5 No

I'm new to SQL and I'm not even sure how to approach this problem. Any help is appreciated.

For each transaction(i.e. each row), I want to add a check if the user has done any transactions in the last 7 days, Last 7 days at the time of the transaction (if the transaction is done of Jan14, I will check if there are any transactions from Jan7 to jan14.

Answer 1

Looks like a case for an EXISTS subquery.

select Customer, Date, Value,
  case when exists (
    select *
    from transactions t1
    where t1.Customer = t.Customer
      and t1.Date >= t.Date - interval 7 day
      and t1.Date <  t.Date
  ) then 'Yes' else 'No' end as Rewards
from transactions t;

Now you can't just convert it to an UPDATE statement - The engine would complain about something like "cannot SELECT and UPDATE from the same table". So you will need to wrap the select statement into a subquery and join it with the same table for update:

update transactions u
join (
  select Customer, Date, Value,
    case when exists (
      select *
      from transactions t1
      where t1.Customer = t.Customer
        and t1.Date >= t.Date - interval 7 day
        and t1.Date <  t.Date
    ) then 'Yes' else 'No' end as Rewards
  from transactions t
) s using (Customer, Date)
set u.Rewards = s.Rewards;

Demo: https://www.db-fiddle.com/f/eJcwgzwS7QjJcJ5MFWaz8L/0

Answer 2

Consider the following:

DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(transaction_id SERIAL PRIMARY KEY
,customer CHAR(2) NOT NULL
,date DATE NOT NULL
,value INT NOT NULL
);
INSERT INTO my_table (customer,date,value) VALUES
('C1','2019-01-12', 2),
('C2','2019-01-12', 3),
('C3','2019-01-12',45),
('C4','2019-01-12',12),
('C1','2019-01-14', 1),
('C3','2019-01-14', 6),
('C1','2019-01-25', 5);
SELECT a.transaction_id
     , a.customer
     , a.date
     , a.value
     , COALESCE(a.date BETWEEN b.date AND b.date + INTERVAL 7 DAY,0) reward
  FROM 
     ( SELECT x.*
            , MAX(y.transaction_id) prev 
         FROM my_table x 
         LEFT 
         JOIN my_table y 
           ON y.customer = x.customer 
          AND y.transaction_id < x.transaction_id 
        GROUP 
           BY x.transaction_id
     ) a
  LEFT
  JOIN my_table b
    ON b.transaction_id = a.prev;
+----------------+----------+------------+-------+--------+
| transaction_id | customer | date       | value | reward |
+----------------+----------+------------+-------+--------+
|              1 | C1       | 2019-01-12 |     2 |      0 |
|              2 | C2       | 2019-01-12 |     3 |      0 |
|              3 | C3       | 2019-01-12 |    45 |      0 |
|              4 | C4       | 2019-01-12 |    12 |      0 |
|              5 | C1       | 2019-01-14 |     1 |      1 |
|              6 | C3       | 2019-01-14 |     6 |      1 |
|              7 | C1       | 2019-01-25 |     5 |      0 |
+----------------+----------+------------+-------+--------+

Answer 3

I am not sure if this can be solved with plain SQL. But I would like to provide a solution for this with JAVA. Make a JDBC connection with your database and run this.

boolean flag = true;
Statement st = connectionObject.createStatement();
ResultSet result = st.executeQuery("select Date from orders");
while(result.next())
{
    String mysqlDate = result.getString(1);
    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); //default mysql date format
    Date date = new Date();
    String currentDate = dateFormat.format(date);
    Date date1 = dateFormat.parse(mysqlDate);
    Date date2 = dateFormat.parse(currentDate);
    long difference = date2.getTime() - date1.getTime();
    long diffDays = difference / (24 * 60 * 60 * 1000);
    if(Math.toIntExact(value) >= 7)
    {
        flag = false;
        break;
    }
}
if(flag)
{
    st.executeUpdate("ALTER TABLE orders ADD rewards varchar(4);"); 
}