This discussion is archived
0 Replies Latest reply: Apr 4, 2013 2:48 PM by 936133 RSS

Customizing project status sql statement

936133 Newbie
Currently Being Moderated
The sql below takes into account the max(EFFDT) and max(EFFSEQ) to determine the status of a project, it also takes into account all later EFFDTs that are after CLOSED status such as FINISHED (company doesnt want this) - so what i eventually have are blank statuses for most projects since the sql also nulls any values outside Approved, Closed or Open. I need help in customizing the sql to get a max(EFFDT) of a project when it is in either an APPROVED,OPEN OR CLOSED status only. Here is what i have so far - thanks

SELECT P.EFFDT AS EFFDT,
P.EFFSEQ AS EFFSEQ,
(CASE P.PROJECT_STATUS WHEN 'A' THEN 'APPROVED' WHEN 'C' THEN 'CLOSED' WHEN 'N' THEN 'OPEN' ELSE NULL END) AS PROJECT_STATUS,
P.PROJECT_PRIORITY AS PROJECT_PRIORITY,
P.BUSINESS_UNIT AS BUSINESS_UNIT,
P.PROJECT_ID AS PROJECT_ID
FROM PS_PROJECT_STATUS P
WHERE P.EFFDT =
(SELECT MAX(EFFDT)
FROM PS_PROJECT_STATUS P1
WHERE P1.BUSINESS_UNIT = P.BUSINESS_UNIT
AND P1.PROJECT_ID = P.PROJECT_ID
AND P1.EFFDT < '$$$SessStartTime'
GROUP BY P1.BUSINESS_UNIT,
P1.PROJECT_ID
)
AND P.EFFSEQ =
(SELECT MAX(EFFSEQ)
FROM PS_PROJECT_STATUS P2
WHERE P2.BUSINESS_UNIT = P.BUSINESS_UNIT
AND P2.PROJECT_ID = P.PROJECT_ID
GROUP BY P2.BUSINESS_UNIT,
P2.PROJECT_ID
)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points