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

Transact-SQL
1

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.

2

hi

i tried to do this
i hope it helps
:slight_smile:
:slight_smile:

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

image

3

Spot on harishgg1. This will work for me. Thanks for the help.

4

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;
5

thanks Jeff

Yes its a simpler solution

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

:slight_smile:
:slight_smile:

6

Thanks for this solution Jeff!!