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 (30●4●17) | 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.9k●7●14) | 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

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.

It's all about the answers!

Related questions

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)

Accepted answer

Your answer