It's all about the answers!

Ask a question

How would I add a Column to my Report Builder Report that gives me the percentage of Time Spent (Actual Work) to my Estimate (Planned Work)


Richard Kissel (30112) | asked Feb 22 '17, 9:34 p.m.

I am using Report Builder version 6.0.2

My SQL Query is below.

Sorry Just learning SQL...Thanks For Your Help

How would I add to my SQL Query to add a Percentage Column in my Report?

SELECT DISTINCT T2_1.LITERAL_NAME,
       T1.REFERENCE_ID,
       T1.NAME AS URL1_title,
       T1.URL AS URL1,
       T1.ACTUAL_WORK,
       T1.PLANNED_WORK
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='complexity'
  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
WHERE T1.PROJECT_ID = 52  AND
(  T1.REQUEST_TYPE = 'CLM Migration Inventory'
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL) ORDER BY T2_1.LITERAL_NAME asc

Accepted answer


permanent link
Lily Wang (4.9k714) | answered Feb 23 '17, 9:56 p.m.

You can add a new column "((T1.ACTUAL_WORK100)/T1.PLANNED_WORK) AS PERCENTAGE". To make sure the aggregation works, you also need to add a condition that "Plan Work" is not zero.

The SQL query in my environment looks like:
---------------------------------------------------------

SELECT DISTINCT T1.PROJECT_NAME,
       T1.REFERENCE_ID,
       T1.NAME AS URL1_title,
       T1.URL AS URL1,
       T1.PLANNED_WORK,
       T1.ACTUAL_WORK,
       ((T1.ACTUAL_WORK100)/T1.PLANNED_WORK) AS PERCENTAGE
FROM RIDW.VW_REQUEST T1
WHERE T1.PROJECT_ID = 8  AND
(  T1.PLANNED_WORK <> 0 
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL)

Richard Kissel selected this answer as the correct answer

Comments
1
Richard Kissel commented Feb 24 '17, 11:39 a.m.

Thank You Lilly. After much Web Searching I actually worked something out for myself but I did not post my answer. Your answer appears Spot on and for That I thank You Gratefully and mark your answer as the Accepted Answer. This is what I came up with before I left work. Do you see anything Wrong? I'm not sure why you added ( T1.PLANNED_WORK <> 0).

SELECT DISTINCT
       T2_1.LITERAL_NAME,
       T1.REFERENCE_ID,
       T1.NAME AS URL1_title,
       T1.URL AS URL1,
       INT(T1.ACTUAL_WORK/3600) AS TimeSpent,
       INT(T1.PLANNED_WORK/3600) AS Estimate,
       CASE WHEN INT(T1.ACTUAL_WORK) > 0
            THEN (INT(T1.PLANNED_WORK)/INT(T1.ACTUAL_WORK))
            ELSE 0 END as Percentage
FROM RIDW.VW_REQUEST T1


Lily Wang commented Feb 26 '17, 8:34 p.m.

I'm glad to know you have find solution yourself.

As in my sql query I used "T1.planned_work" as divisor so I added “T1.PLANNED_WORK <> 0" to avoid error.

Your answer


Register or to post your answer.