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.
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
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 |
+----------------+----------+------------+-------+--------+
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);");
}
What does exactly the value of output_buffering ini variable mean in phpini? On our older server, it was set to 1
I want to change a variable in javascript with greasemonkey
nodemon was working perfectly in my mac previously, I updated it to the latest version and when I try to start it, I keep getting the below error
I am trying to build an application using Nodejs, Angular 6 and SQLIn my database I have more than 80