sqlt-sqlssms-17

How to filter a specific record in SQL Server within in the date range without removing other records?

I am using SSMS 2017.

Below is a source table,

Source Table

from the above table I want to filter only 'i' records

Records that needs to be removed

in which the date difference is >=6 months by satisfying the following condition (date2 <= dateadd(MONTH, +6, date1 - datepart(MONTH, date1) -1)) and the result should be as following,

Result table

any help from any one will be greatly appreciated.

Solution

  • Is this what you want?

    select t.*
from t
where name <> 'i' or
      date2 <= dateadd(month, +6, date1 - datepart(month, date1) -1))

    I'm not quite sure what your condition is supposed to be doing, but this is what is in your question.

    Based on the logic in the text:

    select t.*
from t
where name <> 'i' or
      date2 <= dateadd(month, +6, date1)