How to calculate customer Inactivity based on latest order date ? please

• asita

  SSCarpal Tunnel

  Points: 4144

  More actions

  October 24, 2022 at 10:15 pm

  #4103834

  CREATE TABLE Customers (

  CID int NOT NULL PRIMARY KEY,

  GroupName varchar(255) NOT NULL,

  FirstName varchar(255),

  Age int,

  CustomerStatus SMALLINT, ---1 Means Active, 0 Inactive

  CustomerJoinDate Date

  )

  CREATE TABLE Customers_Orders (

  CID int NOT NULL,

  CostOrderDay_Date Date,

  ORDER_INDICATOR SMALLINT, --- 1 means made order 0 means no order

  Account_CashDeposit SMALLINT, --- 1 means yes 0 means no

  OrderCost int ,

  Qty int

  )

  ---Customers

  INSERT INTO Customers Values(101, 'ABC123', 'King',40,1,'07/01/2021');

  INSERT INTO Customers Values(99, 'ABC123', 'KYAL',20,1,'07/11/2021');

  INSERT INTO Customers Values(100, 'ABC123', 'John',30,1,'01/16/2022');

  INSERT INTO Customers Values(102, 'XYZ456', 'SARA',55,1,'02/21/2022');

  INSERT INTO Customers Values(104, 'XYZ123', 'Jimi',45,0,'03/26/2022');

  --- Orders Data

  INSERT INTO Customers_Orders Values(99,'09/01/2021',1,1,125,30);

  INSERT INTO Customers_Orders Values(100,'02/17/2022',1,0,125,30);

  INSERT INTO Customers_Orders Values(100,'04/19/2022',1,1,125,30);

  INSERT INTO Customers_Orders Values(100,'05/21/2022',1,0,125,30);

  INSERT INTO Customers_Orders Values(100,'06/25/2022',0,1,0,0);

   

  INSERT INTO Customers_Orders Values(102,'03/19/2022',1,1,50,30);

  INSERT INTO Customers_Orders Values(101,'10/01/2021',1,1,50,30);

  INSERT INTO Customers_Orders Values(101,'05/19/2022',1,1,50,30);

  if a customer dont have any orders for continous 100 day period then the account consider as inactive (for the month of 100day addition to latest CustOrderDay). if no order at all placed by customer then consider adding 100 days to customer joindate

  for example :- customer #99 joined 2021 July 11 , customer has order on 9/1/2022, if no order 9/1/2022 +100 = Dec-09-2021 days then count this record as inactive customer

   

  expected output

  Month InactiveCustomerCounts

  Jul-2021 0

  AUG-2021 0

  SEP-2021 0

  OCT-2021 0

  Nov-2021 0

  Dec-2021 1 ---- cust #99 latest order date +100 days falls dec 2021.

  Jan-2022 2 ---- same #99 & #101

  Feb-2022 2 ---- same #99 & #101

  Mar-2022 2 ---- same #99 & #101

  Apr-2022 2 ---- same #99 & #101

  May-2022 1 --- only 99 as 101 has order.

  Jun-2022 2 ---- 99, 102

  Jul-2022 3 ---- 99, 102, 104 (account never had any order so customer joindate +100)

  Aug-2022 4 -- 99, 102, 104, 101

  Sep-2022 4 -- 99, 102, 104, 101

  Oct-2022 5 -- 99, 102, 104, 101, 100

  Please help me

   

  Thank you

  ASitti

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  October 25, 2022 at 3:05 am

  #4103970

  if a customer dont have any orders for continous 100 day period then the account consider as inactive (for the month of 100day addition to latest CustOrderDay). if no order at all placed by customer then consider adding 100 days to customer joindate

  You can use LAG() to determine the date of a customer's "previous" order, and then use DATEDIFF().

  you could create a query to return only the orders that were actually placed, and then use DATEDIFF and LAG with that to get the difference in days.

  use AdventureWorks2019;
  go
  
  SELECT r.CustomerID,
  r.OrderDate,
  r.prevDate,
  DaysBetweenOrders = DATEDIFF(day,r.prevDate,r.OrderDate) 
  FROM
  (SELECT s.CustomerID,
  s.OrderDate,
  prevDate = LAG(s.OrderDate,1) OVER (PARTITION BY s.CustomerID ORDER BY s.OrderDate)
  FROM AdventureWorks2019.Sales.SalesOrderHeader s) r;