Customer Inactivity calculation based on latest order date

Question

Dear Gurus,

here i am trying to calculate customer inactivity counts by month (if no order placed 100 continous days)

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
Thanks in advance

Answer 1

Hi @bizzi Manna
Try this query:
DECLARE @Startdate date = CAST('20210701' as datetime)
DECLARE @Enddate date = CAST('20221031' as datetime)

;WITH DateTable AS
(
SELECT @Startdate AS [date]
UNION ALL
SELECT DATEADD(MONTH, 1, [date])
FROM DateTable
WHERE DATEADD(MONTH, 1, [date]) <= @Enddate
),CTE AS
(
SELECT C.CID
,ISNULL(O.CostOrderDay_Date,C.CustomerJoinDate)AS Order_Date
,DATEADD(DAY,100,ISNULL(O.CostOrderDay_Date,C.CustomerJoinDate))AS [100_Days_Offset]
,LEAD(ISNULL(O.CostOrderDay_Date,C.CustomerJoinDate),1,DATEADD(MONTH,1,@Enddate))OVER(PARTITION BY C.CID ORDER BY O.CostOrderDay_Date) AS Next_OrderDate
FROM Customers C LEFT JOIN Customers_Orders O ON C.CID=O.CID
)
SELECT LEFT(DATENAME(MONTH,[date]),3)+'-'+DATENAME(YEAR,[date]) AS MONTH_YEAR,COUNT(CID)AS InactiveCustomerCounts
FROM DateTable D LEFT JOIN CTE C ON DATEDIFF(DAY,Order_Date,Next_OrderDate)>100
AND (DATEDIFF(MONTH,D.date,C.[100_Days_Offset])=0)
OR (DATEDIFF(MONTH,D.date,C.[100_Days_Offset])<0 AND DATEDIFF(MONTH,D.date,C.Next_OrderDate)>0)
GROUP BY [date]

Output:

Best regards,
Li Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you Very much. LiHong. Appreciate you help it is very helpful.

if time permits, may I know what these two conditions do? please

AND (DATEDIFF(MONTH,D.date,C.[100_Days_Offset])=0)
OR (DATEDIFF(MONTH,D.date,C.[100_Days_Offset])<0 AND DATEDIFF(MONTH,D.date,C.Next_OrderDate)>0)

Thanks Again
Bmanna