need to have avg and sum in a query in report builder.
hi
i am trying to bring summation and its avg in one column but system is bringing the average based on the no of work items ,i tired using distinct full name too but system shows the average based on no of work items ,i should have the average based on no of owners not no of work items.
pls help
SELECT T1.CREATOR_NAME,
T3.FULL_NAME,
sum (T4.VAL) as sums,
sum (T4.VAL)/count(T3.FULL_NAME) as totalavg
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 RIDW.VW_RESOURCE T3
ON T3.RESOURCE_ID=T1.OWNER_ID
LEFT OUTER JOIN RICALM.VW_RQST_DECIMAL_EXT T4
ON T4.REQUEST_ID=T1.REQUEST_ID AND T4.NAME='utilisationperweek.dailytask.tsgtestingmanagment'
WHERE T1.PROJECT_ID = 2072 AND
( T1.REQUEST_TYPE = 'Daily Task' AND
T1.REQUEST_STATE = 'Closed' AND
T2_1.LITERAL_NAME = 'Week 2'
) AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL)
group by T3.FULL_NAME, T1.CREATOR_NAME
Comments
kesav d
Sep 19 '16, 10:49 a.m.pls update ..any one got answer for this :)