I have table:

I have to insert into result value either 1 or 0 depending on date difference of previous row date. 1 indicates new client and 0 indicates old client.

For example:

For rownr 1 i need to look date of rownr 2 and make sure it has same ID and if datediff is for example 1month then insert 1 else insert 0. For row nr2 as there is no previous record with this ID it gets value 1 On this sample table rownr5 id3 gets value 1 as it has no previous records.

And so on. This table gets new rows every day and needs this 1 or 0 into every row.

Goal is to indicate if customer is new or old. If customer has no previous orders it gets value 1 as a new customer. If customer has previous orders but datediff is long then it gets value 1 and indicates a old customer that came back. If customer has previous orders and datediff is short then it gets value 0 and indicates a regular customer.

How should i approach this problem? Any direction to solving this problem is appreciated.

CodePudding user response：

WITH CTE(ROWNR,ID,SALESDATE,RESULT) AS
(
  SELECT 1,1,'20210105',NULL
     UNION ALL
  SELECT 2,1,'20210205',NULL
     UNION ALL
  SELECT 3,2,'20210203',NULL
     UNION ALL
  SELECT 4,2,'20210304',NULL
     UNION ALL
  SELECT 5,3,'20210504',NULL
     UNION ALL
  SELECT 6,4,'20210703',NULL
     UNION ALL
  SELECT 7,4,'20210905',NULL  
)

SELECT C.ROWNR,C.ID,C.SALESDATE,
LAG(C.SALESDATE)OVER(PARTITION BY C.ID ORDER BY C.SALESDATE)PREV_DATE,
 CASE
  WHEN DATEDIFF(DD,C.SALESDATE,LAG(C.SALESDATE)OVER(PARTITION BY C.ID ORDER BY C.SALESDATE))<30 THEN 'OLDD'
 ELSE 'NEWW'
END AS RESULT
FROM CTE AS C

You can use LAG-function to find the previous sale's date for ID

CodePudding user response：

Given this sample data:

CREATE TABLE dbo.TheTable(Rownr int, ID int, SalesDate date, result tinyint);

INSERT dbo.TheTable(Rownr, ID, SalesDate, result) VALUES
(1, 1, '20210105', NULL),
(2, 1, '20210205', NULL),
(3, 2, '20210203', NULL),
(4, 2, '20210304', NULL),
(5, 3, '20210504', NULL),
(6, 4, '20210703', NULL),
(7, 4, '20210905', NULL);

This query updates the result column with 1 if the customer hasn't been before or their last purchase was more than a month ago, and 0 if their previous purchase was last month. Note that if a new customer places two orders in the same month, only the first one will be marked as such.

;WITH cte AS 
(
  SELECT *, Prev =
    COALESCE(LAG(SalesDate, 1) 
    OVER (PARTITION BY ID ORDER BY SalesDate), SalesDate)
  FROM dbo.TheTable
  WHERE result IS NULL
)
UPDATE cte SET result = CASE
  WHEN DATEDIFF(MONTH, Prev, SalesDate) = 1
  THEN 0 ELSE 1 END;

• Example db<>fiddle