CODEWITHSUNDEEP
booleansql-server-2012
Kevin Coulombe
Kevin Coulombe

Reputation: 1575

SQL Server : comparing boolean expression results

I have a query that tells me when a client has not communicated with our server in the last hour. When that happens, I need to send an email.

This is the query that retrieves all of the clients that haven't communicated in time.

SELECT * FROM Client 
    WHERE DATEADD(HOUR, 1, LastCommunication) < GetDate()

However, if a client has not communicated in days, I only want to send one single email. I also want to send an email when a previously broken client comes back online. For this purpose, I added a bit column in the table named "IsErrorNotified".

I want to only get the clients whose error state has changed. Normally, I would do this :

SELECT * FROM Client 
    WHERE DATEADD(HOUR, 1, LastCommunication) < GetDate() = IsErrorNotified

However, this doesn't work. How should I do this?

Upvotes: 0

Views: 1820

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 180867

This looks simpler than the expression you have;

SELECT * FROM Client
WHERE CASE WHEN DATEADD(HOUR, -1, GetDate()) > LastCommunication 
           THEN  IsErrorNotified ELSE ~IsErrorNotified END = 0;

An SQLfiddle to test with.

Upvotes: 1

Kevin Coulombe
Kevin Coulombe

Reputation: 1575

I had to work with some boolean logic equivalence to write the complete condition like this : 

SELECT * FROM Client 
    WHERE (LastCommunication >= DATEADD(HOUR, -1, GetDate()) AND IsErrorNotified = 1) 
    OR (LastCommunication < DATEADD(HOUR, -1, GetDate()) AND IsErrorNotified = 0)

I would still like to find a better way of doing a boolean XOR (which is the same a comparing booleans for equality) with SQL Server...

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Are you looking for this?

SELECT * FROM Client 
    WHERE DATEADD(HOUR, 1, LastCommunication) < GetDate()
        AND IsErrorNotified = 1

but it is good to use sargable predicate

SELECT * FROM Client 
    WHERE LastCommunication < DATEADD(HOUR, -1, GetDate())
        AND IsErrorNotified = 1

Upvotes: 2

Related Questions