I am trying to create a query that will show a month of reported work from a user on MS Project 2012.
I have this query:
select nome,data,work from (
select
r.ResourceName Nome,
convert(varchar(10), ad.TimeByDay, 103) data,
sum(ad.AssignmentActualWork) Work
FROM
dbo.MSP_EpmAssignmentByDay ad,
dbo.msp_epmassignment a,
dbo.msp_epmresource r,
dbo.msp_epmtask t
WHERE
a.TaskUID = t.taskuid
AND a.assignmentuid = ad.assignmentuid
AND a.ResourceOwnerUID = r.ResourceUID
AND ad.timebyday >= convert(varchar(10), '01/05/14', 103)
AND ad.timebyday <= convert(varchar(10), '31/05/14', 103)
group by r.ResourceName,ad.TimeByDay
) t
This query print this result:
Name Date Sum(work)
Frederico 01/05/2014 0.000000
Frederico 02/05/2014 8.000000
Frederico ... 0.000000
Frederico 31/05/2014 8.000000
Other user 01/05/2014 3.000000
I need the following result:
Name | 01/05/14 | 02/05/14 | ... | 31/05/14 |
Frederico 0.0 8.0 0.0 0.0
Other user 3.0 0.0 0.0 0.0
Thanks for all!
Uhuuu! I got it !
That's it: