0 Replies Latest reply: Apr 4, 2013 4:48 PM by 936133 RSS

    Customizing project status sql statement

    936133
      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
      )