CODEWITHSUNDEEP
sqlsql-server-2005
Brezhnews
Brezhnews

Reputation: 1569

Determine if the starting date is before ending date

I have a table named data with columns: CD, beginDate, endDate. suffix. I need to determine if any row I want to insert, the beginDate is earlier than endDate in any other row with the same suffix value.

Code with what I am trying and no luck:

select * from data a1
join data a2 on a2.cd=a1.cd
where
    a1.suffix=a2.suffix and 
    a1.beginDate<a2.endDate

Any idea, hot can I do that? Thanks in advance

Upvotes: 0

Views: 43

Answers (1)

criticalfix
criticalfix

Reputation: 2870

You want an inequality on the cd condition in the join:

...the beginDate is earlier than endDate in any other row with the same suffix value.

You can also put all of the conditions into the ON clause of the join:

select a1.cd, a2.cd, a1.suffix, a1.beginDate, a2.endDate
from data a1
join data a2 
on a2.cd <> a1.cd
and a1.suffix = a2.suffix
and a1.beginDate < a2.endDate

Upvotes: 1

Related Questions