For simplicity, I'm going to use the concept of customers and orders.
- When a new customer makes an order, a new record is created in a orders table, there is a status column that needs to be updated to 'New Order'.
- When the same customer purchases a second order, another row is created and, as long as the order is made within 30 days of the last order, the status field should be updated to 'Repeat Customer'.
- if the last order the customer created was over 30 days, then the second row is updated as 'New Order'
customer Date Status
C1 2018-01-01 New Order
C1 2018-01-05 Repeat Customer (4 days since last order)
C1 2018-01-25 Repeat Customer (15 days since last order)
C1 2018-03-01 New Order (over 30 days since last order is seen as the first row for that customer)
Using SQL I have to update the Status column. To do that I need to evaluate if there is a previous record within 30 days date to update to "Repeat Customer" and if there isn't i need to update the Status as "New Order".
Anyone know the best way to achieve this?
Thanks for any help.