SQL : I have two tables on table is contain the following table

Question

table A is contain CIF_Key and current date column and many more columns

table B is contain CIF_key and pre_date column which is different from date variable in Table A and many more columns.

my Goal :is to see if there is same record from table B in table A and if it exists I need to drop them from table A based on the condition that if the difference between the current date column and pre_date column is less than 4 month else I want to keep them in table A. how can I do this goal ?

Answer 1

You can JOIN the tables using the common CIF_Key - here is some info: JOIN

Then use WHERE to compare dates or something like this:

SELECT * 
FROM table_a 
    JOIN table_b on table_a.CIF_Key = table_b.CIF_Key 
WHERE DATEDIFF(month, table_a.date, table_b.date) < 4;