I have 2 tables

Table 1

Table 2

Table 1 is derived from table2 using max(transactionDate)

I want to select all rows in table 2 that are 24 months before date_last_transacted for that particular customer.

So, the result I want to get from table 2 is: Table 2

Can you please help with the code?

CodePudding user response：

You may try the following join query:

SELECT T.Customer, T.Transactionid, T.TransactionDate
FROM Table2 T
JOIN
(
  SELECT Customer, MAX(TransactionDate) Date_last_transacted
  FROM Table2
  GROUP BY Customer
) D
ON T.TransactionDate > DATEADD(Month, -24, D.Date_last_transacted) AND
   T.Customer = D.Customer
ORDER BY T.Transactionid

Also, you may use the MAX window function as the following:

SELECT Customer, Transactionid, TransactionDate
FROM
  (
    SELECT *,
       MAX(TransactionDate) OVER (PARTITION BY Customer) Date_last_transacted
    FROM Table2
  ) T
WHERE TransactionDate > DATEADD(Month, -24, Date_last_transacted)
ORDER BY Transactionid

See a demo.

CodePudding user response：

There is a way to filter rows using the subquery and the DATEADD function.

SELECT * FROM Table2 T2
  WHERE
    DATEADD(month, 24, T2.transactionDate) >=
    ( SELECT MAX(T1.transactionDate) FROM Table2 T1
      WHERE T1.Customer=T2.Customer GROUP BY T1.Customer );