troubleshooting Question

How do I incorporate this CASE into my SELECT

Avatar of Bruce Gust
Bruce GustFlag for United States of America asked on
MySQL Server
5 Comments2 Solutions27 ViewsLast Modified:
Here's my current SELECT:

select RID from tasks_accomplishments where CREATED_DATE <='{$asof}'
and  t.PROJECT IN (select DISTINCT t.PROJECT 
FROM tasks t 
JOIN task_metrics tm ON t.ID=tm.RID 
JOIN resources r ON t.PROJECT=r.PROJID
JOIN projects p ON p.ID=t.PROJECT
where r.USER_ID={$user_id}
and r.ROLE IN ('INTDM', 'INTLEAD','INTOWN')
and p.ACTIVE=1)) 

Open in new window

The problem is the very first line. I can't use CREATED_DATE. Instead, I have to use one of four values:





I'm going to use the value that's defined by the value in the EXPECTED_PHASE column...



...which in this case is DEPLOY. So, I need go grab "2020-02-08."

How?

I wrote this as part of another SELECT:

case when (SELECT EXPECTED_PHASE FROM task_metrics where RID = t.ID) = 'DEPLOY'
      then (select DEPLOY_START_DATE from task_metrics where RID = t.ID)
   when (SELECT EXPECTED_PHASE FROM task_metrics where RID = t.ID) = 'BUILD'
      then (select BUILD_START_DATE from task_metrics where RID = t.ID)
   when (SELECT EXPECTED_PHASE FROM task_metrics where RID = t.ID) = 'DEPLOY'
      then (select DEPLOY_START_DATE from task_metrics where RID = t.ID)
   when (SELECT EXPECTED_PHASE FROM task_metrics where RID = t.ID) = 'ARCH'
      then (select ARCH_START_DATE from task_metrics where RID = t.ID)
   when (SELECT EXPECTED_PHASE FROM task_metrics where RID = t.ID) = 'TEST'
      then (select TEST_START_DATE from task_metrics where RID = t.ID)

...but how would I incorporate that into the SELECT that I've got above?

Hit me!

ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros