SQL – Filter records by date when record contains specific ID's

February 16, 2022

I am trying to filter the RECORDS by last modified date (LastModifiedDate) column but I want this filter to work only when any record have status id’s as 6 or 7. for other ID’s it should return those records without checking for date filter.

Can anyone help me with the proper condition in whereclause of below sql?

SELECT fd.[DocID], fd.[FolderName], fd.[AStatus], fd.[LastModifiedDate]
FROM FoldersDetail fd
INNER JOIN StatusMaster sm 
    ON sm.StatusID = fd.StatusID
INNER JOIN PriorityMaster pm 
    ON pm.PriorityID = fd.PriorityID
WHERE FD.StatusID IN (6,7) AND fd.LastModifiedDate > DATEADD(day, -30, GETDATE())

This SQL filtering records but not returning other rows whose ID’s are not 6 or 7.

>Solution :

Try this:

SELECT fd.[DocID], fd.[FolderName], fd.[AStatus], fd.[LastModifiedDate]
FROM FoldersDetail fd
INNER JOIN StatusMaster sm 
    ON sm.StatusID = fd.StatusID
INNER JOIN PriorityMaster pm 
    ON pm.PriorityID = fd.PriorityID
WHERE FD.StatusID NOT IN (6,7) OR fd.LastModifiedDate > DATEADD(day, -30, GETDATE())

Basically if StatusID isn’t 6 or 7, the OR is satisfied, if StatusID is 6 or 7, the right part of the OR is checked.