|
SELECT T.SCD_EVALUATION_ID , X.XLATLONGNAME AS SCD_RESP_STATUS , (CASE WHEN T.STAT_COUNT IS NULL THEN 0 ELSE T.STAT_COUNT END) STAT_COUNT FROM PSXLATITEM X INNER JOIN ( SELECT H.SCD_EVALUATION_ID , H.SCD_RESP_STATUS , COUNT(1) AS STAT_COUNT FROM PS_SCD_USR_EVAL_HD H INNER JOIN ps_SCD_ASSGNCNT_VW w ON h.scd_evaluation_id=w.scd_evaluation_id AND h.scd_evaluator_id=w.scd_evaluator_id GROUP BY H.SCD_EVALUATION_ID , H.SCD_RESP_STATUS) T ON X.FIELDVALUE = T.SCD_RESP_STATUS WHERE X.FIELDNAME = 'SCD_RESP_STATUS' AND EFFDT = ( SELECT MAX(X2.EFFDT) FROM PSXLATITEM X2 WHERE X.FIELDNAME = X2.FIELDNAME AND X.FIELDVALUE = X2.FIELDVALUE AND X2.EFFDT <= %CurrentDateIn AND X2.EFF_STATUS = 'A' )
|