CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Gopal
Gopal

Reputation: 11982

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

How to make a query for the above condition.

Need query Help.

Upvotes: 0

Views: 215

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

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

Upvotes: 0

Tanvir Kaiser
Tanvir Kaiser

Reputation: 253

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

Upvotes: 2

Thomas
Thomas

Reputation: 64645

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

Upvotes: 0

Related Questions