sql - How to get the current effective date in Oracle? -

i have table following:

tid tname effectivedate
1 2011-7-1
2 2011-8-1
3 2011-9-1
4 2011-10-1
5 b 2011-8-1
6 b 2011-9-1
7 b 2011-10-1
8 c 2011-9-1
etc...

if today 2011-9-10, wish query result this:

tid tname effectivedate status
1 2011-7-1 invalid
2 2011-8-1 invalid
3 2011-9-1 valid
4 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 2011-10-2, query result this:

tid tname effectivedate status
1 2011-7-1 invalid
2 2011-8-1 invalid
3 2011-9-1 invalid
4 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 add 1 more column named 'status', , status value based on today's value , compare column effectivedate. max effective day show 'valid' status. if today’s value between 2 record, latter 'inactive' status.

how write statement result in oracle?

thanks in advance.

try:

select tid,        tname,        effectivedate,        decode(sign(effectivedate - (select max(t2.effectivedate)                                      mytable t2                                     t1.tname=t2.tname ,                                            t2.effectivedate <= sysdate)),               -1,'invalid',               0,'valid',               'inactive') status mytable t1