CODEWITHSUNDEEP
t-sqlsql-server-2008sql-server-2008-r2
BenAlabaster
BenAlabaster

Reputation: 39846

How do I achieve exclusive OR in T-SQL?

I have a data table where there's a list of columns (boiled down to the pertinent ones for this example):

users(
  usr_pkey int identity(1, 1) primary key,
  usr_name nvarchar(64),
  ...,
)

accounts(
  acc_pkey int identity(1, 1) primary key,
  usr_key int foreign_key references users(usr_pkey),
  acc_effective datetime,
  acc_expires datetime,
  acc_active bit,
  ...,
)

From this table I'm looking to grab all records where:

So - if an active record exists where today's date falls between the account's effective and expiry dates, I want that record. Only if no match was found do I want any account for this user having the most recent expiry date.

Upvotes: 4

Views: 12394

Answers (2)

BenAlabaster
BenAlabaster

Reputation: 39846

Here's one solution I've found:

select top 1 *
from accounts
where usr_key = @specified_user
order by
  acc_active desc,
  case 
    when getdate() between acc_effective and acc_expires then 0
    else 1
  end,
  acc_expires desc

This would effectively order the records in the right priority sequence allowing me to pick the top one off the list

Strictly speaking, it doesn't achieve exclusive or, but it could be applied to this data set to achieve the same end.

Upvotes: 4

The Evil Greebo
The Evil Greebo

Reputation: 7138

Unless something has radically changed in TSQL 2008, it's brute force.

select *
from table
where (    ( condition 1 OR condition 2)
    AND NOT ( condition 1 AND condition 2)    )

Upvotes: 5

Related Questions