How to get the current effective date in Oracle?

I have a table like the following:

TID  TName  EffectiveDate
1    A      2011-7-1
2    A      2011-8-1
3    A      2011-9-1
4    A      2011-10-1
5    B      2011-8-1
6    B      2011-9-1
7    B      2011-10-1
8    C      2011-9-1

If today is 2011-9-10, I wish the query result will be like this:

TID  TName  EffectiveDate  Status
1    A      2011-7-1       Invalid
2    A      2011-8-1       Invalid
3    A      2011-9-1       Valid
4    A      2011-10-1      Inactive
5    B      2011-8-1       Invalid
6    B      2011-9-1       Valid
7    B      2011-10-1      Inactive
8    C      2011-9-1       Valid

If today is 2011-10-2, the query result will be like this:

TID  TName  EffectiveDate  Status
1    A      2011-7-1       Invalid
2    A      2011-8-1       Invalid
3    A      2011-9-1       Invalid
4    A      2011-10-1      Valid
5    B      2011-8-1       Invalid
6    B      2011-9-1       Invalid
7    B      2011-10-1      Valid
8    C      2011-9-1       Valid

The query result will add one more column named ‘Status’, and the status value is based on today’s value and compare it to the column effectivedate. The max effective day will be show as ‘Valid’ Status. If today’s value is between two record, the latter is ‘Inactive’ status.

How to write a statement to get this result in oracle?

Answer

Try:

select TID,
       TName,
       EffectiveDate,
       decode(sign(EffectiveDate - (select max(T2.EffectiveDate) 
                                    from MyTable T2
                                    where T1.Tname=T2.Tname and 
                                          T2.EffectiveDate <= sysdate)),
              -1,'Invalid',
              0,'Valid',
              'Inactive') Status
from MyTable T1