CODEWITHSUNDEEP
sql-servert-sql
DenStudent
DenStudent

Reputation: 928

SQL Server - Only filter records where column equals value

Let's say I have following (fictional) dataset

 ID  | Name | Location | LastUpdated
 1   | Bill | Germany  | 2017-01-02
 2   | Bill | Germany  | 2017-01-03
 3   | Bill | Germany  | 2017-01-04
 4   | Bill | Germany  | 2017-01-05
 5   | Jack | U.K      | 2017-01-02
 6   | Jack | U.K      | 2017-01-03
 7   | Jack | U.K      | 2017-01-04
 8   | John | Japan    | 2017-02-22
 9   | John | Japan    | 2017-02-23
 10  | John | Japan    | 2017-02-24
 11  | John | Japan    | 2017-02-25

With my dataset I want to remove all records where lastupdated equals 2017-01-03

I also want to remove all of John's records where lastupdated equal or is greater then 2017-02-24

I tried following SQL statement:

SELECT *
FROM Table
WHERE (LastUpdated <> '2017-01-03')
AND (Name <> 'John' AND LastUpdated >= '2017-02-24')

But this way, it only returns the records with a LastUpdated greater then 2017-02-24

It seems pretty basic, but I can't work it out. Any ideas?

Upvotes: 1

Views: 396

Answers (2)

EzLo
EzLo

Reputation: 14189

You can transcript your requirements like this:

I want to remove all records where lastupdated equals 2017-01-03

This is correct from your attempt:

LastUpdated <> '2017-01-03'

I also want to remove all of John's records where lastupdated equal or is greater then 2017-02-24

This one is a little trickier. Either the record we want to display isn't John's OR the record is John's AND the date has to be lower than 2017-02-24. This is expressed like the following:

 (
    Name <> 'John' OR
    (Name = 'John' AND LastUpdated < '2017-02-24')
 )

So the final expression would be:

SELECT *
FROM [Table]
WHERE 
    LastUpdated <> '2017-01-03' AND
     (
        Name <> 'John' OR
        (Name = 'John' AND LastUpdated < '2017-02-24')
     )

Also if name can hold NULL values, you might need another OR expression that checks for NULL: 

 (
    Name <> 'John' OR
    Name IS NULL OR
    (Name = 'John' AND LastUpdated < '2017-02-24')
 )

Upvotes: 2

Salman Arshad
Salman Arshad

Reputation: 272096

You can write it as a NOT condition to keep it simple:

WHERE NOT (
    (lastupdated = 2017-01-03)
    OR
    (name = 'John' AND lastupdated >= '2017-02-24')
)

Upvotes: 1

Related Questions