unable to get the resource id or the avg
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)
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
Sep 12 '16, 10:32 a.m.screeshot reference: 401121