(

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'