如何在Oracle中获得当前的生效日期? [英] How to get the current effective date in Oracle?

问题描述

我有一个如下表:

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
etc...

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
etc...

如果今天是2011-9-10，我希望查询结果是这样的:

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

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

如果今天是2011-10-2，则查询结果将如下所示:

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

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.

如何编写语句以在oracle中获得此结果?

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

谢谢.

推荐答案

尝试:

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

这篇关于如何在Oracle中获得当前的生效日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！