Compare the date from other table

Using SQL Sever 2005

Table1

ID StartDate EndDate

001 02/23/2010 07/22/2010
002 05/03/2010 null
003 02/02/2011 null
...

Table2

Date

02/24/2011
02/25/2011
...
...

Condition

• If the enddate is not null then startDate compare with max(date) from table2, if the startdate > 6 month then it should display as "Expired"

How to make a query for the above condition.

N开发者_运维问答eed query Help.

---

Select 
  StartDate,
  [Status]=Case  
        when DATEDIFF(m,StartDate,(Select MAX(Date) from Table2))>6
        then 'Expired' 
        Else 'Valid'
        end

  From Table1
  where EndDate is not null

---

Select Case
        When EndDate Is Not Null Then EndDate
        When Table1.StartDate > DateAdd(mm, 6, T2.MaxDate) Then 'Expired'
        End
From Table1
    Cross Join  (
                Select Max( [Date] ) As MaxDate
                From Table2
                ) As T2

---

The question is incomplete

1. If the enddate is not null then startDate compare with max(date) from table2
   • how to compare? greater than? less than? equal to?
2. if the startdate > 6 month then it should display as "Expired"

But let's assume you mean

1. If the enddate is not null then startDate compare with max(date) from table2
   • it is expired if enddate < max(date)
2. otherwise if the startdate is older than 6 month then it should display as "Expired"

Then the query becomes

select
  case when EndDate < MaxDate then 'Expired'
       when EndDate is null and StartDate < DateAdd(m,-6,GetDate()) then 'Expired'
       else 'OK'
  end
from Table1 T1
cross join (select max(date) MaxDate from Table2) T2