asked on

Oracle SQL Max Date

In the table below I'm trying to select the max task complete date when the task type = 'Pre-schedule Review ' else I want the task complete date. Thanks

Task Type	Task Status	Task Completed Date
Pre-schedule Review	Closed	2021-01-21 14:53:41.0
Pre-schedule Review	Closed	2021-03-24 10:23:55.0
Pre-schedule Review	Closed	2021-03-22 10:42:14.0

Select Distinct
TY.C_NAME as "Task Type"
,TS.C_NAME as "Task Status"
,Case
When TY.C_NAME = 'Pre-schedule Review' Then (Select MAX (TA.C_COMPLETED_DT ) FROM T_APPEAL_TASK Where TA.C_TASK_TYPE = TY.ID)
Else TA.C_COMPLETED_DT End as "Task Completed Date"

From
T_APL A

LEFT OUTER JOIN T_APPEAL_TASK TA ON A.ID = TA.ID_BASE
Left outer join T_APPEAL_TASK_TYPE TY on TA.C_TASK_TYPE = TY.ID
Left outer join T_APPEAL_TASK_STATUS TS on TA.C_TASK_STATUS = TS.ID

ASKER CERTIFIED SOLUTION

Alex [***Alex140181***]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

shieldsco

ASKER

Final Solution:
,case
when TY.C_NAME = 'Pre-schedule Review' then
max(TA.C_COMPLETED_DT) over(partition by TY.C_NAME )
when TY.C_NAME = 'Decision Writing' then
max(TA.C_COMPLETED_DT) over(partition by TY.C_NAME )

when TY.C_NAME = 'Exhibiting' then
max(TA.C_COMPLETED_DT) over(partition by TY.C_NAME )

slightwv (䄆 Netminder)

Better version?

case
         when TY.C_NAME  in ( 'Pre-schedule Review', 'Decision Writing', 'Exhibiting') then
          max(TA.C_COMPLETED_DT) over(partition by TY.C_NAME )
else ...

Open in new window