# Find last record in a group within 20 days of current record

Hi,

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'

Example 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.

hi

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
GO

CREATE TABLE data
(
customer   varchar(100) NULL ,
dateok   date NULL ,
)
GO

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'
GO
``````
SQL
``````; WITH cte
AS (SELECT Row_number()
OVER(
partition BY customer
ORDER BY dateok) AS rn,
*
FROM   data)
SELECT a.customer,
a.dateok,
CASE
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

go
``````
Result

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');

Select *
, 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'
End
From @testData       td;
``````

thanks Jeff

Yes its a simpler solution

I am so used to thinking and doing in SQL Server 2008 version

Thanks for this solution Jeff!!