It's all about the answers!

Ask a question

JRS Report Builder v6.0.2 DB2 SQL Percentage Conversion of Actual_Work divided by Planned_Work does not work


Richard Kissel (3019) | asked Apr 07 '17, 7:22 p.m.

CLM v6.0.2

SELECT DISTINCT
       T3_1.LITERAL_NAME AS LITERAL_NAME2,
       T1.NAME AS URL1_title,
       T1.URL AS URL1,
       T2_1.LITERAL_NAME,
       T1.REQUEST_STATE,
       T1.ACTUAL_WORK/3600.0 AS ActualWork,
       T1.PLANNED_WORK/3600.0 AS PlannedWork,
       T1.PLANNED_WORK/3600.0 - T1.ACTUAL_WORK/3600.0 AS Variance,
       CASE WHEN T1.ACTUAL_WORK > 0 THEN
            (T1.ACTUAL_WORK/3600.0)/(T1.PLANNED_WORK/3600.0)   --This Does Not Work

       ELSE .22 END AS Percentage,

       CASE WHEN T1.ACTUAL_WORK > 0 THEN          -- This Case shows that it is getting inside the case and performing the calculation on the Decimals
            8.0/12.0
       ELSE .33 END AS Percentage1,
       CASE WHEN T1.ACTUAL_WORK > 0 THEN          -- This Case shows that it can convert the T1.ACTUAL_WORK value into a Decimal
            T1.ACTUAL_WORK/3600.0
       ELSE .44 END AS Decimal1,
       CASE WHEN T1.ACTUAL_WORK > 0 THEN          -- This Case shows that it can convert the T1.PLANNED_WORK value into a Decimal
            T1.PLANNED_WORK/3600.0
       ELSE .55 END AS Decimal2

FROM RIDW.VW_REQUEST T1
LEFT OUTER JOIN RICALM.VW_RQST_STRING_M_EXT T2
ON T2.REQUEST_ID=T1.REQUEST_ID AND T2.NAME='migrateorarchive'
  LEFT OUTER JOIN RICALM.REQUEST_ATTRDEF T2_2
  ON T2_2.REQUEST_TYPE_ID=T1.REQUEST_TYPE_ID AND T2_2.NAME=T2.NAME
    LEFT OUTER JOIN RICALM.VW_RQST_ENUMERATION T2_1
    ON T2.VAL LIKE CONCAT(CONCAT('%|',T2_1.EXTERNAL_ID), '|%') AND T2_1.PROJECT_ID=T1.PROJECT_ID AND T2_1.ENUMERATION_NAME=T2_2.DATATYPE
      LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T3
      ON T3.REQUEST_ID=T1.REQUEST_ID AND T3.NAME='Complexity2'
        LEFT OUTER JOIN RICALM.REQUEST_ATTRDEF T3_2
        ON T3_2.REQUEST_TYPE_ID=T1.REQUEST_TYPE_ID AND T3_2.NAME=T3.NAME
          LEFT OUTER JOIN RICALM.VW_RQST_ENUMERATION T3_1
          ON T3_1.EXTERNAL_ID=T3.VAL AND T3_1.PROJECT_ID=T1.PROJECT_ID AND T3_1.ENUMERATION_NAME=T3_2.DATATYPE
WHERE T1.PROJECT_ID = 52  AND
(  T1.REQUEST_TYPE = 'CLM Migration Inventory' AND
  T2_1.LITERAL_NAME = 'Migrate' AND
  T1.REQUEST_STATE <> 'Completed'
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL) ORDER BY T3_1.LITERAL_NAME asc

this question will not allow me to insert a picture of my results...Anyway

Time Spent = 8.00

Estimated = 12.00

Variance = 4.00

Percentage = 0%

Test_Percentage = 66.7%

Test_Decimal1 = 8.00

Test_Decimal2 = 12.00

basically I am getting into the case statement, I can obtain the values, but for some reason the values (T1.ACTUAL_WORK/3600.0)/(T1.PLANNED_WORK/3600.0) (Percentage) will not divide to give me the percentage I need to display instead it gives me 0% although I can use 8.00/12.00 and it works (Test_Percentage) and gives me 66.7%

2 answers



permanent link
Richard Kissel (3019) | answered Apr 18 '17, 5:09 p.m.

Answer from IBM Support: Use a Double and check for both Actual and Planned

SELECT DISTINCT
       T3_1.LITERAL_NAME AS LITERAL_NAME2,
       T1.NAME AS URL1_title,
       T1.URL AS URL1,
       T2_1.LITERAL_NAME,
       T1.REQUEST_STATE,
       T1.ACTUAL_WORK/3600 AS TimeSpent,
       T1.PLANNED_WORK/3600 AS Estimate,
       T1.PLANNED_WORK/3600-T1.ACTUAL_WORK/3600 AS Variance,
       CASE WHEN (T1.ACTUAL_WORK > 0 AND T1.PLANNED_WORK > 0)
   THEN DOUBLE(T1.ACTUAL_WORK/3600)/DOUBLE(T1.PLANNED_WORK/3600)
            ELSE 0 END As Percentage

FROM RIDW.VW_REQUEST T1
LEFT OUTER JOIN RICALM.VW_RQST_STRING_M_EXT T2
ON T2.REQUEST_ID=T1.REQUEST_ID AND T2.NAME='migrateorarchive'
  LEFT OUTER JOIN RICALM.REQUEST_ATTRDEF T2_2
  ON T2_2.REQUEST_TYPE_ID=T1.REQUEST_TYPE_ID AND T2_2.NAME=T2.NAME
    LEFT OUTER JOIN RICALM.VW_RQST_ENUMERATION T2_1
    ON T2.VAL LIKE CONCAT(CONCAT('%|',T2_1.EXTERNAL_ID), '|%') AND T2_1.PROJECT_ID=T1.PROJECT_ID AND T2_1.ENUMERATION_NAME=T2_2.DATATYPE
      LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T3
      ON T3.REQUEST_ID=T1.REQUEST_ID AND T3.NAME='Complexity2'
        LEFT OUTER JOIN RICALM.REQUEST_ATTRDEF T3_2
        ON T3_2.REQUEST_TYPE_ID=T1.REQUEST_TYPE_ID AND T3_2.NAME=T3.NAME
          LEFT OUTER JOIN RICALM.VW_RQST_ENUMERATION T3_1
          ON T3_1.EXTERNAL_ID=T3.VAL AND T3_1.PROJECT_ID=T1.PROJECT_ID AND T3_1.ENUMERATION_NAME=T3_2.DATATYPE
WHERE T1.PROJECT_ID = 52  AND
(  T1.REQUEST_TYPE = 'CLM Migration Inventory' AND
  T2_1.LITERAL_NAME = 'Migrate' AND
  T1.REQUEST_STATE = 'Completed'
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL) ORDER BY T3_1.LITERAL_NAME asc


permanent link
Salman Shaikh (2221346) | answered Apr 19 '17, 5:18 a.m.
edited Apr 19 '17, 5:20 a.m.

Don't go like this but you can modify the query as per your requirement. I have implemented the same type of report in BIRT and is working perfectly.

Example to calculate Values first
SUM(T1.PLANNED_WORK/3600) as PLANNED_WORK,
       SUM(T1.ACTUAL_WORK/3600) as ACTUAL_WORK,

Then go for Percentage.
In birt there is option to add computed columns where you can add values and calculate percentage.

Your answer


Register or to post your answer.