sqlcasedatediff

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


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

Solution

  • 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