SQL Query CASE Clause when > 180 days between TransactionDate for same MemberNumber

Question

probably a simple request but a rookie like me can't seem to figure it out.

I just need a query that generates results when there is > 180 days between the TransactionDate for each MemberNumber. This is what I have so far but it's not working correctly:

SELECT [TransactionDate]
  ,[MemberNumber]
  ,[MemberName]
  ,[PrincipalAmount]
  ,[TransactionCategory]
  ,[TransactionChannel]
  ,[Service]
  ,[Product]
  ,
  
CASE 
WHEN DATEDIFF(day, TransactionDate, TransactionDate) > 180 
AND MemberNumber = MemberNumber THEN 'Dormant'
ELSE 'Active'
END AS Dormant_Flag

Answer 1

Using Lag() you can obtain the previous Transaction date for a MemberNumber

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

fiddle

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