CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
LindsayScott23
LindsayScott23

Reputation: 43

TSQL - How do I analyse the previous row to establish time lapsed between payments

I need to analyse customer payment history to establish if they could be deemed as "New", "Current", "Lapsed" or "Deep Lapsed" based on the time lapsed in months between purchases.

I've figured out an approach (below) that seems to work by self joining a cte that has a count of payments for each customer which then calculating the difference in months between each transaction.

But is there a more elegant way to achieve this (using SQL Server 2012)?

WITH CTE
     AS (
     SELECT [customerID]
          , [transaction_date]
          , [payment_count]
     FROM [Financial])

     SELECT [cur].[customerID]
          , CASE
                WHEN ABS(DATEDIFF([mm], [cur].[transaction_date], [prv].[transaction_date])) IS NULL
                THEN 'New'
                WHEN ABS(DATEDIFF([mm], [cur].[transaction_date], [prv].[transaction_date])) BETWEEN 0 AND 24
                THEN 'Current'
                WHEN ABS(DATEDIFF([mm], [cur].[transaction_date], [prv].[transaction_date])) BETWEEN 24 AND 36
                THEN 'Lapsed'
                WHEN ABS(DATEDIFF([mm], [cur].[transaction_date], [prv].[transaction_date])) > 36
                THEN 'DeepLapsed'
            END AS [Customer_Status_at_Time_Of_Transaction]
     FROM [CTE] AS [cur]
          LEFT JOIN [CTE] AS [prv] ON [cur].[customerID] = [prv].[customerID]
                                               AND [cur].[payment_count] - 1 = [prv].[payment_count]
     ORDER BY [customerID];

Upvotes: 0

Views: 38

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

As you are using SQL-Server 2012 there is the windowing function LAG() for your rescue:

Try something like this:

SELECT [customerID]
      , [transaction_date]
      , [payment_count]
      , LAG([transaction_date]) OVER(PARTITION BY customerID ORDER BY transaction_date DESC) AS Last_transaction_date
      --you can use this as parameter too
      , DATEDIFF([mm], LAG([transaction_date]) OVER(PARTITION BY customerID ORDER BY transaction_date DESC), [transaction_date]) AS DifferenceInMonths
 FROM [Financial]

Upvotes: 3

Related Questions