unable to get the resource id or the avg

kesav d (19●1●15) | asked Sep 12 '16, 6:45 a.m.

hi admin
i need a SQL query to solve this following problem
We are calculating utilization for individuals so to achieve that we created a task for each user and user has to close it on daily basis with time spent entered in work item.
We are using calculated values and storing the values for
1)     utilization for a day as timespent / 8 ,
2)     utilization for a week as tiemspent / 40(5 days **8)
3)     utilization for a month as tiemspent / 176 (22 days *8)

We have achieved it and got everything working fine and while triggering report I am unable to get the average of week

Screenshot 1: we have got all columns in report, we need a columns which should display week wise (avg of (utilization per week) /no of owner).
I tried in advanced sql but failed miserably so thought of bringing the count of resource id in resource id column and do the manipulation (count of resource id * utilization per week) so that eh graph week wise will show the sum.

But unable to bring the resource id count or if you have any other solution pls help me.

Script

SELECT T1.REFERENCE_ID,
       T4.FULL_NAME,
       T1.CREATOR_NAME,
       T2_1.LITERAL_NAME,
       T3.VAL AS VAL2,
       T5.VAL AS VAL1,
       T6.VAL AS VAL5,
       T7.VAL,
     (select distinct(T4.RESOURCE_ID) from RIDW.VW_RESOURCE T4 where T4.RESOURCE_ID=T1.OWNER_ID) AS w,
       T8.VAL AS VAL4
FROM RIDW.VW_REQUEST T1
LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T2
ON T2.REQUEST_ID=T1.REQUEST_ID AND T2.NAME='Week.dailytask.tsgtestingmangement'
LEFT OUTER JOIN RICALM.VW_RQST_ENUMERATION T2_1
ON T2_1.EXTERNAL_ID=T2.VAL AND T2_1.PROJECT_ID=T1.PROJECT_ID
    LEFT OUTER JOIN RICALM.VW_RQST_STRING_EXT T3
    ON T3.REQUEST_ID=T1.REQUEST_ID AND T3.NAME='Month.dailytask.tsgtestingmanagement'
      LEFT OUTER JOIN RIDW.VW_RESOURCE T4
      ON T4.RESOURCE_ID=T1.OWNER_ID
        LEFT OUTER JOIN RICALM.VW_RQST_DECIMAL_EXT T5
        ON T5.REQUEST_ID=T1.REQUEST_ID AND T5.NAME='TimeSpentInhrs.dailytask.tsgtestingmanagement'
          LEFT OUTER JOIN RICALM.VW_RQST_DECIMAL_EXT T6
          ON T6.REQUEST_ID=T1.REQUEST_ID AND T6.NAME='utilisation.dailytask.tsgtestingmanagement'
            LEFT OUTER JOIN RICALM.VW_RQST_DECIMAL_EXT T7
            ON T7.REQUEST_ID=T1.REQUEST_ID AND T7.NAME='utilisationperweek.dailytask.tsgtestingmanagment'
              LEFT OUTER JOIN RICALM.VW_RQST_DECIMAL_EXT T8
              ON T8.REQUEST_ID=T1.REQUEST_ID AND T8.NAME='monthsutilisation20.dailytask.tsgtestingmanagement'
WHERE T1.PROJECT_ID = 2072 AND
( T1.REQUEST_TYPE = 'Daily Task' AND
T1.REQUEST_STATE = 'Closed' AND
(T2_1.LITERAL_NAME = 'Request Enumeration Literal Name Missing' OR T2_1.LITERAL_NAME = 'Week 1' OR T2_1.LITERAL_NAME = 'Week 2' OR T2_1.LITERAL_NAME = 'Week 3' OR T2_1.LITERAL_NAME = 'Week 4' OR T2_1.LITERAL_NAME = 'Week 5' ) AND
LOWER(T3.VAL) LIKE '%sep%'
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL)

Comments

kesav d commented Sep 12 '16, 10:32 a.m.

screeshot reference: 401121

Be the first one to answer this question!

Register or log in to post your answer.

Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.

It's all about the answers!

Related questions

unable to get the resource id or the avg

Be the first one to answer this question!