How do I incorporate this CASE into my SELECT
Here's my current SELECT:
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!
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)) 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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Could I do something like this:
select case
when (SELECT EXPECTED_PHASE FROM task_metrics where RID = 95679) = 'DEPLOY'
then (select DEPLOY_START_DATE from task_metrics where RID = 95679)
when (SELECT EXPECTED_PHASE FROM task_metrics where RID = 95679) = 'BUILD'
then (select BUILD_START_DATE from task_metrics where RID = 95679)
when (SELECT EXPECTED_PHASE FROM task_metrics where RID = 95679) = 'DEPLOY'
then (select DEPLOY_START_DATE from task_metrics where RID = 95679)
when (SELECT EXPECTED_PHASE FROM task_metrics where RID = 95679) = 'ARCH'
then (select ARCH_START_DATE from task_metrics where RID = 95679)
when (SELECT EXPECTED_PHASE FROM task_metrics where RID = 95679) = 'TEST'
then (select TEST_START_DATE from task_metrics where RID = 95679)
else '' end as `Expected Milestone Target Date`,
(select ta.RID from tasks_accomplishments ta where
ta.COMPLETED_TASK IS NOT NULL
and ta.COMPLETED_TASK<>''
and ta.CREATED_DATE <=Expected Milestone Target Date) as `the date`
I'm trying to understand Pete's suggestion because that looks like it would save a lot of running around.
BTW: The whole SELECT looks like this:
Thanks for the input, guys! I'm still not there, though, and any help would be very much appreciated!
select case
when (SELECT EXPECTED_PHASE FROM task_metrics where RID = 95679) = 'DEPLOY'
then (select DEPLOY_START_DATE from task_metrics where RID = 95679)
when (SELECT EXPECTED_PHASE FROM task_metrics where RID = 95679) = 'BUILD'
then (select BUILD_START_DATE from task_metrics where RID = 95679)
when (SELECT EXPECTED_PHASE FROM task_metrics where RID = 95679) = 'DEPLOY'
then (select DEPLOY_START_DATE from task_metrics where RID = 95679)
when (SELECT EXPECTED_PHASE FROM task_metrics where RID = 95679) = 'ARCH'
then (select ARCH_START_DATE from task_metrics where RID = 95679)
when (SELECT EXPECTED_PHASE FROM task_metrics where RID = 95679) = 'TEST'
then (select TEST_START_DATE from task_metrics where RID = 95679)
else '' end as `Expected Milestone Target Date`,
(select ta.RID from tasks_accomplishments ta where
ta.COMPLETED_TASK IS NOT NULL
and ta.COMPLETED_TASK<>''
and ta.CREATED_DATE <=Expected Milestone Target Date) as `the date`
I'm trying to understand Pete's suggestion because that looks like it would save a lot of running around.
BTW: The whole SELECT looks like this:
SELECT t.ID AS '*ID',
t.ID as `@updateid`,
t.ID as `@tid`,
p.PROJ_NAME AS `*PROJ_NAME`,
t.INTID AS 'Task ID',
t.CUSTID AS `Customer ID`,
t.NAME AS 'Task Name',
cfg.DESCR as 'In-Scope',
t.HEALTH AS 'Health',
(tm.EARNED_VALUE),
(tm.PLANNED_VALUE),
tm.ACT_HOURS AS `Actual Hours`,
(t.HOURS) AS `Budget`,
tm.ACT_HOURS AS `*ACT_HOURS`,
(select case
when
'INTDM' IN (SELECT DISTINCT ROLE from resources where USER_ID='{$user_id}')
then
case
when
'{$projectlead}'<>'ALL'
then
(select concat(u.FIRST_NAME,' ', u.LAST_NAME) from users u
JOIN resources r ON r.USER_ID = u.ID
where r.INTID=t.ID
and r.ROLE='INTLEAD'
and u.ID='{$projectlead}'
ORDER BY r.EFFDT LIMIT 1)
else
(select concat(u.FIRST_NAME,' ', u.LAST_NAME) from users u
JOIN resources r ON r.USER_ID = u.ID
where r.INTID=t.ID
and r.ROLE='INTLEAD'
ORDER BY r.EFFDT DESC LIMIT 1)
end
else
(select concat(u.FIRST_NAME,' ', u.LAST_NAME) from users u
JOIN resources r ON r.USER_ID = u.ID
where r.INTID=t.ID
and r.ROLE='INTLEAD'
and r.USER_ID='{$user_id}' LIMIT 1)
end) AS `Integration Lead`,
intown.NAME AS `Integration Owner`,
tm.CURRENT_PHASE as `Actual Milestone`,
(select case
when tm.CURRENT_PHASE <> tm.EXPECTED_PHASE then case
when tm.EXPECTED_PHASE = 'BUILD' then case
when tm.CURRENT_PHASE = 'ARCH'
then concat(
'<div style=\"background-color:red; color:#fff; display:flex; align-items:center; justify-content: center; font-weight:bold; height:100%; width:100%;\">',
tm.EXPECTED_PHASE,
'</div>')
else concat(
'<div style=\"background-color:#058E09; color:#fff; display:flex; align-items:center; justify-content: center; font-weight:bold; height:100%; width:100%;\">',
tm.CURRENT_PHASE,
'</div>') end
when tm.EXPECTED_PHASE = 'ARCH' then concat(
'<div style=\"background-color:#058E09; color:#fff; display:flex; align-items:center; justify-content: center; font-weight:bold; height:100%; width:100%;\">',
tm.EXPECTED_PHASE, '</div>')
when tm.EXPECTED_PHASE = 'TEST' then case
when tm.CURRENT_PHASE = 'DEPLOY'
then concat(
'<div style=\"background-color:#058E09; color:#fff; display:flex; align-items:center; justify-content: center; font-weight:bold; height:100%; width:100%;\">',
tm.CURRENT_PHASE,
'</div>')
else concat(
'<div style=\"background-color:red; color:#fff; display:flex; align-items:center; justify-content: center; font-weight:bold; height:100%; width:100%;\">',
tm.EXPECTED_PHASE,
'</div>') end
when tm.EXPECTED_PHASE = 'DEPLOY' then concat(
'<div style=\"background-color:red; color:#fff; display:flex; align-items:center; justify-content: center; font-weight:bold; height:100%; width:100%;\">',
tm.EXPECTED_PHASE, '</div>')
else concat(
'<div style=\"background-color:#fff; color:#000; display:flex; align-items:center; justify-content: center; font-weight:bold; height:100%; width:100%;\">',
'Expected Phase Not Documented',
'</div>') end
else
case
when
tm.EXPECTED_PHASE='' AND tm.CURRENT_PHASE=''
then
concat(
'<div style=\"background-color:#fff; color:#000; display:flex; align-items:center; justify-content: center; font-weight:bold; height:100%; width:100%; text-align:center;\">',
'Target Dates Not Documented',
'</div>')
else
concat(
'<div style=\"background-color:#000; color:#fff; display:flex; align-items:center; justify-content: center; font-weight:bold; height:100%; width:100%; text-align:center;\">',
tm.EXPECTED_PHASE, '</div>')
end
end
from task_metrics tm
where RID = t.ID) as `Expected Milestone`,
(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)
else '' end) as `Expected Milestone Target Date`,
(select concat('<b>(created on: ',DATE_FORMAT(CREATED_DATE, '%c/%e/%Y'),'</b>)', ta.COMPLETED_TASK) from tasks_accomplishments ta where ta.RID = t.ID) as `Accomplishment for This Week`,
(select ta.NEXT_TASK from tasks_accomplishments ta where ta.RID = t.ID) as `Plan for Next Week`,
(select ta.ISSUES from tasks_accomplishments ta where ta.RID = t.ID) as `Issues | Concerns`,
(select ta.HOURS from tasks_accomplishments ta where ta.RID = t.ID) as `Hours Planned`,
(SELECT count(*)
FROM pending_items a
WHERE a.INTID = t.id
and a.DELETED = 0
and a.SCOPE = 'TASK'
and curdate() < a.DUE_DATE
and a.STATUS = 'Open') as `Pending Items Approaching Due Date`,
(SELECT count(*)
FROM pending_items a
WHERE a.INTID = t.id
and a.DELETED = 0
and a.SCOPE = 'TASK'
and curdate() > a.DUE_DATE
and a.STATUS = 'Open') as `Pending Items Past Due Date`
FROM tasks t
join idb_cfg.config cfg ON cfg.VALUE = t.IN_SCOPE AND cfg.OBJECT = 'IN_SCOPE'
JOIN task_metrics tm ON tm.rid = t.id
JOIN projects p ON t.PROJECT = p.ID
JOIN resources r ON r.INTID = t.id
left join (select r1.INTID,
r1.USER_ID,
MAX(r1.EFFDT) as EFFDT,
concat(u1.FIRST_NAME, ' ', u1.LAST_NAME) as NAME
from resources r1
join users u1 on r1.USER_ID = u1.ID
where r1.TYPE = 'TASK'
and ROLE = 'INTOWN'
and r1.ACTIVE = 1
group by INTID) intown on t.ID = intown.INTID
left join (select c1.*, MAX(c1.ID), concat(u2.FIRST_NAME, ' ', u2.LAST_NAME) as NAME
from contacts c1
join users u2 on c1.USER_ID = u2.ID
where c1.ROLE in ('TECH', 'PRIME')
and c1.RTYPE = 'TASK'
group by c1.RID) c on c.RID = t.ID
WHERE t.IN_SCOPE in ('IN_SCOPE', 'IN_SCOPE_DROP', 'ADD_REC')
and
case
when
'{$custid}'='ALL' AND '{$project}'='ALL' AND '{$asof}'='1900-01-01' AND '{$projectlead}'='ALL'
/* this is what's happening when there are no prompts selected */
then
case
when
'INTDM' IN (SELECT DISTINCT ROLE from resources where USER_ID='{$user_id}')
then
t.PROJECT IN (select project.PROJID from
projects p
join
(
select DISTINCT r.PROJID as `PROJID` from resources r where r.PROJID in (
SELECT DISTINCT PROJID from resources where ROLE='INTDM' and USER_ID='{$user_id}'and ACTIVE=1
)) project
on project.PROJID=p.ID
order by p.CUSTID)
else
case
when
'INTLEAD' IN (SELECT DISTINCT ROLE from resources where USER_ID='{$user_id}')
then
t.ID IN (select DISTINCT r.INTID from resources r
LEFT JOIN
(select USER_ID, INTID from resources where ROLE='INTLEAD' order by EFFDT DESC LIMIT 1) intlead
ON intlead.USER_ID = r.USER_ID
where r.USER_ID='{$user_id}'
and r.ACTIVE=1
and r.ROLE='INTLEAD'
and r.TYPE='TASK'
and r.PROJID IN (select DISTINCT PROJID from resources where USER_ID='{$user_id}' and ROLE='INTLEAD' and ACTIVE=1))
else
true
end
end
/* end of no pulldowns selected */
else
/* working backwards, starting with asof */
case
when
'{$asof}'<>'1900-01-01'
/* this is what's happening when user selects the as of date prompt */
then
case
when
/* again, you've got to work backwards, so this is when user has chosen all three prompts */
'{$projectlead}'<>'ALL'
then
/* start of project lead clause */
t.ID IN
(
select ta.RID from tasks_accomplishments ta where ta.NEXT_TASK<>''
and ta.TARGET_WEEK IS NOT NULL
and ta.TARGET_WEEK <='{$asof}'
and ta.RID IN
(
select r.INTID from resources r where
r.PROJID='{$project}'
and r.USER_ID='{$projectlead}'
and r.ROLE='INTOWN'
)
UNION ALL
select ta.RID from tasks_accomplishments ta where
ta.COMPLETED_TASK IS NOT NULL
and ta.COMPLETED_TASK<>''
and ta.CREATED_DATE <='{$asof}'
and ta.RID IN
(
select r.INTID from resources r where
r.PROJID='{$project}'
and r.USER_ID='{$projectlead}'
and r.ROLE='INTOWN'
)
)
/* end of project lead clause */
else
case
when
'{$project}'<>'ALL'
/* user has chose customer and project prompt */
/* start of project clause */
then
t.ID IN
(
select ta.RID from tasks_accomplishments ta where ta.NEXT_TASK<>''
and ta.TARGET_WEEK IS NOT NULL
and ta.TARGET_WEEK <='{$asof}'
and ta.RID IN
(
select ID from tasks t where 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.PROJID='{$project}'
and r.ROLE IN ('INTDM', 'INTLEAD','INTOWN')
and p.ACTIVE=1
)
)
UNION ALL
select ta.RID from tasks_accomplishments ta where
ta.COMPLETED_TASK IS NOT NULL
and ta.COMPLETED_TASK<>''
and ta.CREATED_DATE <='{$asof}'
and ta.RID IN
(
select ID from tasks t where 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.PROJID='{$project}'
and r.ROLE IN ('INTDM', 'INTLEAD','INTOWN')
and p.ACTIVE=1
)
)
)
/* end of project clause */
else
case
when
'{$custid}'<>'ALL'
/* user has chosen date and customer prompt */
then
t.ID IN
(
select ta.RID from tasks_accomplishments ta where ta.NEXT_TASK<>''
and ta.TARGET_WEEK IS NOT NULL
and ta.TARGET_WEEK <='{$asof}'
and ta.RID IN
(
select ID from tasks t where t.CUSTID ='{$custid}' 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
)
)
UNION ALL
select ta.RID from tasks_accomplishments ta where
ta.COMPLETED_TASK IS NOT NULL
and ta.COMPLETED_TASK<>''
and ta.CREATED_DATE <='{$asof}'
and ta.RID IN
(
select ID from tasks t where t.CUSTID ='{$custid}' 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
)
)
)
else
t.ID IN
(select RID from tasks_accomplishments where NEXT_TASK<>''
and TARGET_WEEK IS NOT NULL
and TARGET_WEEK <='{$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)
UNION ALL
select RID from tasks_accomplishments where
COMPLETED_TASK IS NOT NULL
and COMPLETED_TASK<>''
and 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))
end
end
end
else
/* project lead */
case
when
'{$projectlead}'<>'ALL'
/* this is what's happening when user selects the project lead prompt*/
then
t.ID IN (select r.INTID from resources r
where r.ROLE='INTOWN'
and r.PROJID='{$project}'
and r.USER_ID='{$projectlead}')
else
/* project */
case
when
'{$project}'<>'ALL'
/* this is what's happening when user selects the projects prompt */
then
t.PROJECT = (select DISTINCT t.PROJECT
FROM tasks t
JOIN task_metrics tm ON t.ID=tm.RID
JOIN resources r ON t.PROJECT=r.PROJID
where r.USER_ID={$user_id}
and r.ROLE IN ('INTDM', 'INTLEAD')
and r.PROJID='{$project}')
else
/* customer ID */
case
when
'{$custid}'<>'ALL'
/* this is what's happening when user selects the customer prompt */
then
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')
and p.ACTIVE=1
and t.CUSTID='{$custid}')
end
end
end
end
end
GROUP BY t.ID
ORDER BY t.CUSTID, t.PROJECTThanks for the input, guys! I'm still not there, though, and any help would be very much appreciated!
Hi,
For some cases, we need dynamic query (called through Stored proc). You can share clearly the need of you regarding the way to dynamically determine the value per your cases.
For some cases, we need dynamic query (called through Stored proc). You can share clearly the need of you regarding the way to dynamically determine the value per your cases.
The case part will return one of the start date. You can then embed such part into your original query, as one field.