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.
i tried to do this
i hope it helps
i wrote logic to calculate STATUS for all records of the same customer
from the beginning
does this meet your requirement ????
drop create sample data ...
DROP TABLE data
CREATE TABLE data
customer varchar(100) NULL ,
dateok date NULL ,
INSERT INTO data SELECT 'C1','2018-01-01'
INSERT INTO data SELECT 'C1','2018-01-05'
INSERT INTO data SELECT 'C1','2018-01-25'
INSERT INTO data SELECT 'C1','2018-03-01'
INSERT INTO data SELECT 'C2','2018-01-01'
INSERT INTO data SELECT 'C2','2018-02-05'
INSERT INTO data SELECT 'C2','2018-02-25'
INSERT INTO data SELECT 'C2','2018-04-01'
; WITH cte
AS (SELECT Row_number()
partition BY customer
ORDER BY dateok) AS rn,
WHEN a.rn = 1
AND b.rn IS NULL THEN 'New Order'
WHEN Datediff(dd, b.dateok, a.dateok) <= 30 THEN 'Repeat Customer'
WHEN Datediff(dd, b.dateok, a.dateok) > 30 THEN 'New Order'
END AS statusok
FROM cte a
LEFT JOIN cte b
ON b.rn + 1 = a.rn
AND a.customer = b.customer
ORDER BY a.customer
Spot on harishgg1. This will work for me. Thanks for the help.
This may be a simpler option:
Declare @testData Table (CustomerID char(2), OrderDate date);
Insert Into @testData (CustomerID, OrderDate)
Values ('C1', '2018-01-01')
, ('C1', '2018-01-05')
, ('C1', '2018-01-25')
, ('C1', '2018-03-01')
, ('C2', '2018-01-01')
, ('C2', '2018-02-05')
, ('C2', '2018-02-25')
, ('C2', '2018-04-01');
, PreviousOrderDate = lag(td.OrderDate, 1) over(Partition By td.CustomerID Order By td.OrderDate)
, CustomerStatus = Case When datediff(day, lag(td.OrderDate, 1, '1900-01-01')
over(Partition By td.CustomerID Order By td.OrderDate), td.OrderDate) > 30
Then 'New Order'
Else 'Repeat Customer'
From @testData td;
Yes its a simpler solution
I am so used to thinking and doing in SQL Server 2008 version
Thanks for this solution Jeff!!