Oracle通过bind变量找出延迟的天数

Question

Table PROJECT

PROJECT     PJID     STARTDYA       ENDDAY         STATUS
Lakers      P01      03/01/2021     04/01/2021     SExceed
Lakers      P03      04/01/2021     05/01/2021     NonExceed
Lakers      P04      02/01/2021     03/01/2021     NonExceed
Bulls       P05      09/01/2021     10/01/2021     EExceed
Bulls       P06      07/01/2021     08/01/2021     EExceed
Heat        P07      08/01/2021     09/01/2021     SExceed
Heat        P08      05/01/2021     06/01/2021     NonExceed

假设用户设置绑定变量为10/31/2021，如果状态为exceed，则计算(绑定变量- STARTDYA)延迟的天数，如果状态为EExceed，则计算(绑定变量- ENDDYA)延迟的天数。如果状态为"不超过"，则不需要计算延迟时间。以下是我的预期输出。

预期输出:

PROJECT      PJID      DaysDelayed
Lakers       P01       244
Bulls        P05       30
Bulls        P06       91
Heat         P07       60

下面是我目前正在思考的伪代码

Select PROJEC,  PJID, (bind variable - STARTDYA) as DaysDelayed, (bind variable - STARTDYA) as DaysDelayed
From
PROJECT
Where
STATUS = 'SExceed' or STATUS = 'EExceed'

我可以问一下如何可能编码它吗?

Answer 1

这可以使用select case和date函数来实现。

Select PROJECT,  PJID, 
CASE WHEN STATUS = 'SExceed' THEN
TO_DATE('10/31/2021', 'MM/DD/YYYY') - TO_DATE(STARTDYA, 'MM/DD/YYYY')
WHEN STATUS = 'EExceed' THEN
TO_DATE('10/31/2021', 'MM/DD/YYYY') - TO_DATE(ENDDAY, 'MM/DD/YYYY')
END as Days_Delayed
From
PROJECT
Where
STATUS = 'SExceed' or STATUS = 'EExceed'