SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Example 
(
    TransactionDate	Date,
    MemberNumber	VARCHAR(512),
    MemberName	VARCHAR(512),
    PrincipalAmount	FLOAT,
    TransactionCategory	VARCHAR(512),
    TransactionChannel	VARCHAR(512),
    Service	VARCHAR(512),
    Product	VARCHAR(512)
);

INSERT INTO Example (TransactionDate, MemberNumber, MemberName, PrincipalAmount, TransactionCategory, TransactionChannel, Service, Product) VALUES
	('2024-1-1', '1', 'Bob', '1.00', '1', '1', '1', '1'),
	('2024-1-3', '1', 'Bob', '1.00', '1', '1', '1', '1'),
	('2024-2-3', '1', 'Bob', '1.00', '1', '1', '1', '1'),
	('2024-6-3', '1', 'Bob', '1.00', '1', '1', '1', '1'),
	('2024-1-3', '2', 'Ann', '1.00', '1', '1', '1', '1'),
	('2024-9-4', '2', 'Ann', '1.00', '1', '1', '1', '1');

SELECT * FROM Example

SELECT [TransactionDate]
  ,[MemberNumber]
  ,[MemberName]
  ,[PrincipalAmount]
  ,[TransactionCategory]
  ,[TransactionChannel]
  ,[Service]
  ,[Product]
  , LAG(TransactionDate) OVER (Partition BY [MemberNumber] ORDER BY TransactionDate) as PreviousTransactionDate
  ,DATEDIFF(Day, LAG(TransactionDate) OVER (Partition BY [MemberNumber] ORDER BY TransactionDate) , TransactionDate) as diff
  
,CASE 
WHEN DATEDIFF(Day, LAG(TransactionDate) OVER (Partition BY [MemberNumber] ORDER BY TransactionDate) , TransactionDate) > 180 
THEN 'Dormant'
ELSE 'Active'
END AS Dormant_Flag
FROM Example

TransactionDate	MemberNumber	MemberName	PrincipalAmount	TransactionCategory	TransactionChannel	Service	Product
2024-01-01	1	Bob	1	1	1	1	1
2024-01-03	1	Bob	1	1	1	1	1
2024-02-03	1	Bob	1	1	1	1	1
2024-06-03	1	Bob	1	1	1	1	1
2024-01-03	2	Ann	1	1	1	1	1
2024-09-04	2	Ann	1	1	1	1	1

TransactionDate	MemberNumber	MemberName	PrincipalAmount	TransactionCategory	TransactionChannel	Service	Product	PreviousTransactionDate	diff	Dormant_Flag
2024-01-01	1	Bob	1	1	1	1	1	null	null	Active
2024-01-03	1	Bob	1	1	1	1	1	2024-01-01	2	Active
2024-02-03	1	Bob	1	1	1	1	1	2024-01-03	31	Active
2024-06-03	1	Bob	1	1	1	1	1	2024-02-03	121	Active
2024-01-03	2	Ann	1	1	1	1	1	null	null	Active
2024-09-04	2	Ann	1	1	1	1	1	2024-01-03	245	Dormant