Avatar of shieldsco
shieldsco
Flag for United States of America 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 TypeTask StatusTask Completed Date
Pre-schedule ReviewClosed 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


* Oracle PL/SQLSQL

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Alex [***Alex140181***]

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck