Issue estimation

issue_estimation.sql

-- estimation storage table
CREATE TABLE ISSUE_ESTIMATION(
  N_ESTIMATION_ID NUMBER,
  VC_PREDICTOR    VARCHAR2(64) NOT NULL,
  VC_ISSUE        VARCHAR2(64) NOT NULL,
  N_ESTIMATION    NUMBER NOT NULL,
  D_ESTIMATION    DATE NOT NULL);
  
ALTER TABLE ISSUE_ESTIMATION
add constraint PK_ISSUE_ESTIMATION primary key (N_ESTIMATION_ID);
 
-- global procedure for estimation
CREATE OR REPLACE PROCEDURE ESTIMATE_ISSUE(
  vch_VC_ISSUE              IN ISSUE_ESTIMATION.VC_ISSUE%TYPE,
  num_N_ESTIMATION          IN ISSUE_ESTIMATION.N_ESTIMATION%TYPE)
IS
  vch_VC_PREDICTOR         VARCHAR2(64);
  num_N_ESTIMATION_ID         NUMBER;
BEGIN
  dbms_application_info.read_client_info(vch_VC_PREDICTOR);
  
  BEGIN
    SELECT N_ESTIMATION_ID
    INTO   num_N_ESTIMATION_ID
    FROM   ISSUE_ESTIMATION
    WHERE  VC_PREDICTOR = vch_VC_PREDICTOR
    AND    VC_ISSUE = vch_VC_ISSUE;
  EXCEPTION WHEN no_data_found THEN
    num_N_ESTIMATION_ID := NULL;
  END;
  
  IF num_N_ESTIMATION_ID IS NULL THEN
    num_N_ESTIMATION_ID := MAIN.GET_LOG_AUTO_NUMBER;
    INSERT INTO ISSUE_ESTIMATION(
      N_ESTIMATION_ID,
      VC_PREDICTOR,
      VC_ISSUE,
      N_ESTIMATION,
      D_ESTIMATION)
    VALUES(
      num_N_ESTIMATION_ID,
      vch_VC_PREDICTOR,
      vch_VC_ISSUE,
      num_N_ESTIMATION,
      SYSDATE);
  ELSE
    UPDATE ISSUE_ESTIMATION
    SET   N_ESTIMATION = num_N_ESTIMATION
    WHERE N_ESTIMATION_ID = num_N_ESTIMATION_ID;
  END IF;
  
  COMMIT;
END ESTIMATE_ISSUE;

-- estimation process

-- execute once for a session so that you avoid passing predictor name everytime
exec dbms_application_info.set_client_info('vela'); 

-- estimation in hours
exec ESTIMATE_ISSUE('AIS-3774', 3)

-- diverging estimations
SELECT *
FROM   ISSUE_ESTIMATION
WHERE  VC_ISSUE IN (
  SELECT VC_ISSUE
  FROM  (
    SELECT VC_ISSUE,
           MAX(N_ESTIMATION)    N_MAX_ESTIMATION,
           MIN(N_ESTIMATION)    N_MIN_ESTIMATION
    FROM   ISSUE_ESTIMATION
    GROUP BY VC_ISSUE)
  WHERE ABS(N_MAX_ESTIMATION - N_MIN_ESTIMATION) >= 0.5)
ORDER BY VC_ISSUE, N_ESTIMATION DESC

-- average estimation
SELECT 'Average time for ' || VC_ISSUE     || ' is ' ||
                 TO_CHAR(TRUNC(N_EST))     || 'h '   ||
                 ROUND(MOD(N_EST, 1) * 60) || 'min ' ||
                 '(' || N_EST_NUM || ' estimation(s) from ' || VC_PREDICTORS || ')'
FROM (
  SELECT AVG(N_ESTIMATION)                                          N_EST,
         LISTAGG(VC_PREDICTOR, ', ') WITHIN GROUP (ORDER BY NULL)   VC_PREDICTORS,
         COUNT(*)                                                   N_EST_NUM,
         VC_ISSUE
  FROM   ISSUE_ESTIMATION
  WHERE  VC_ISSUE = 'AIS-5456'
  GROUP BY VC_ISSUE)