Time sheet report

N

Nixm

I need a query that will display actual hours worked by resource for each
project and by project for each resource, totaled out by week. The date
stuff is easy and the groups etc. But I am having a fit figuring out where
the hours worked per date is stored. Is there a std query for this info?
 
S

Steven Yetter

I built this stored procedure with help from this group. It may not be the
most efficient but it works for me.


CREATE PROCEDURE [dbo].[USP_MSPA_ALL_TASK_TIME_BY_RESOURCE_FOR_DATE_RANGE]
@Res_Name as varchar(50) ,
@Begin_Date as DateTime,
@End_Date as DateTime

AS

Begin

SELECT w1.res_name as Lead, p.PROJ_NAME as Project, r.RES_NAME as
Resource, a.TASK_NAME as Task, a.ASSN_ACT_WORK / 60000 AS actual_hours,
a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000
AS assigned_work, a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work,
a.WASSN_SEND_UPDATE_DATE AS update_sent_date, (w.wwork_value) / 60000 AS
[work],
w.wwork_start AS work_date, w.wwork_start as
work_start, w.wwork_finish as work_finish
FROM dbo.MSP_WEB_RESOURCES r INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON a.WRES_ID = r.WRES_ID
INNER JOIN
dbo.MSP_WEB_PROJECTS p ON p.WPROJ_ID = a.WPROJ_ID
INNER JOIN
dbo.MSP_WEB_WORK w ON a.WASSN_ID = w.WASSN_ID
LEFT JOIN dbo.MSP_WEB_RESOURCES w1 ON a.WRES_ID_MGR = w1.WRES_ID
WHERE w.wwork_type = 1 AND w.wwork_value > 0
and r.Res_Name like @res_name
and w.wwork_start between @Begin_Date and @End_Date
UNION
SELECT w1.res_name as Lead, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME,
a.ASSN_ACT_WORK / 60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000
AS assigned_work, a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work,
a.WASSN_SEND_UPDATE_DATE AS update_sent_date, (w.wwork_value) / 60000 AS
[work],
DateAdd(d, 1, w.WWORK_START) AS work_date,
w.wwork_start, w.wwork_finish
FROM dbo.MSP_WEB_RESOURCES r INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON a.WRES_ID = r.WRES_ID
INNER JOIN
dbo.MSP_WEB_PROJECTS p ON p.WPROJ_ID = a.WPROJ_ID
INNER JOIN
dbo.MSP_WEB_WORK w ON a.WASSN_ID = w.WASSN_ID
LEFT JOIN dbo.MSP_WEB_RESOURCES w1 ON a.WRES_ID_MGR = w1.WRES_ID
WHERE w.wwork_type = 1 AND w.wwork_value > 0 AND w.WWORK_START <>
w.WWORK_FINISH AND DateAdd(d, 1, w.WWORK_START)
<= w.WWORK_FINISH
and r.Res_Name like @res_name
and w.wwork_start between @Begin_Date and @End_Date
UNION
SELECT w1.res_name as Lead, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME,
a.ASSN_ACT_WORK / 60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000
AS assigned_work, a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work,
a.WASSN_SEND_UPDATE_DATE AS update_sent_date, (w.wwork_value) / 60000 AS
[work],
DateAdd(d, 2, w.WWORK_START) AS work_date,
w.wwork_start, w.wwork_finish
FROM dbo.MSP_WEB_RESOURCES r INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON a.WRES_ID = r.WRES_ID
INNER JOIN
dbo.MSP_WEB_PROJECTS p ON p.WPROJ_ID = a.WPROJ_ID
INNER JOIN
dbo.MSP_WEB_WORK w ON a.WASSN_ID = w.WASSN_ID
LEFT JOIN dbo.MSP_WEB_RESOURCES w1 ON a.WRES_ID_MGR = w1.WRES_ID
WHERE w.wwork_type = 1 AND w.wwork_value > 0 AND w.WWORK_START <>
w.WWORK_FINISH AND DateAdd(d, 2, w.WWORK_START)
<= w.WWORK_FINISH
and r.Res_Name like @res_name
and w.wwork_start between @Begin_Date and @End_Date
UNION
SELECT w1.res_name as Lead, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME,
a.ASSN_ACT_WORK / 60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000
AS assigned_work, a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work,
a.WASSN_SEND_UPDATE_DATE AS update_sent_date, (w.wwork_value) / 60000 AS
[work],
DateAdd(d, 3, w.WWORK_START) AS work_date,
w.wwork_start, w.wwork_finish
FROM dbo.MSP_WEB_RESOURCES r INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON a.WRES_ID = r.WRES_ID
INNER JOIN
dbo.MSP_WEB_PROJECTS p ON p.WPROJ_ID = a.WPROJ_ID
INNER JOIN
dbo.MSP_WEB_WORK w ON a.WASSN_ID = w.WASSN_ID
LEFT JOIN dbo.MSP_WEB_RESOURCES w1 ON a.WRES_ID_MGR = w1.WRES_ID
WHERE w.wwork_type = 1 AND w.wwork_value > 0 AND w.WWORK_START <>
w.WWORK_FINISH AND DateAdd(d, 3, w.WWORK_START)
<= w.WWORK_FINISH
and r.Res_Name like @res_name
and w.wwork_start between @Begin_Date and @End_Date
UNION
SELECT w1.res_name as Lead, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME,
a.ASSN_ACT_WORK / 60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000
AS assigned_work, a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work,
a.WASSN_SEND_UPDATE_DATE AS update_sent_date, (w.wwork_value) / 60000 AS
[work],
DateAdd(d, 4, w.WWORK_START) AS work_date,
w.wwork_start, w.wwork_finish
FROM dbo.MSP_WEB_RESOURCES r INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON a.WRES_ID = r.WRES_ID
INNER JOIN
dbo.MSP_WEB_PROJECTS p ON p.WPROJ_ID = a.WPROJ_ID
INNER JOIN
dbo.MSP_WEB_WORK w ON a.WASSN_ID = w.WASSN_ID
LEFT JOIN dbo.MSP_WEB_RESOURCES w1 ON a.WRES_ID_MGR = w1.WRES_ID
WHERE w.wwork_type = 1 AND w.wwork_value > 0 AND w.WWORK_START <>
w.WWORK_FINISH AND DateAdd(d, 4, w.WWORK_START)
<= w.WWORK_FINISH
and r.Res_Name like @res_name
and w.wwork_start between @Begin_Date and @End_Date
UNION
SELECT w1.res_name as Lead, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME,
a.ASSN_ACT_WORK / 60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000
AS assigned_work, a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work,
a.WASSN_SEND_UPDATE_DATE AS update_sent_date, (w.wwork_value) / 60000 AS
[work],
DateAdd(d, 5, w.WWORK_START) AS work_date,
w.wwork_start, w.wwork_finish
FROM dbo.MSP_WEB_RESOURCES r INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON a.WRES_ID = r.WRES_ID
INNER JOIN
dbo.MSP_WEB_PROJECTS p ON p.WPROJ_ID = a.WPROJ_ID
INNER JOIN
dbo.MSP_WEB_WORK w ON a.WASSN_ID = w.WASSN_ID
LEFT JOIN dbo.MSP_WEB_RESOURCES w1 ON a.WRES_ID_MGR = w1.WRES_ID
WHERE w.wwork_type = 1 AND w.wwork_value > 0 AND w.WWORK_START <>
w.WWORK_FINISH AND DateAdd(d, 5, w.WWORK_START)
<= w.WWORK_FINISH
and r.Res_Name like @res_name
and w.wwork_start between @Begin_Date and @End_Date
UNION
SELECT w1.res_name as Lead, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME,
a.ASSN_ACT_WORK / 60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000
AS assigned_work, a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work,
a.WASSN_SEND_UPDATE_DATE AS update_sent_date, (w.wwork_value) / 60000 AS
[work],
DateAdd(d, 6, w.WWORK_START) AS work_date,
w.wwork_start, w.wwork_finish
FROM dbo.MSP_WEB_RESOURCES r INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON a.WRES_ID = r.WRES_ID
INNER JOIN
dbo.MSP_WEB_PROJECTS p ON p.WPROJ_ID = a.WPROJ_ID
INNER JOIN
dbo.MSP_WEB_WORK w ON a.WASSN_ID = w.WASSN_ID
LEFT JOIN dbo.MSP_WEB_RESOURCES w1 ON a.WRES_ID_MGR = w1.WRES_ID
WHERE w.wwork_type = 1 AND w.wwork_value > 0 AND w.WWORK_START <>
w.WWORK_FINISH AND DateAdd(d, 6, w.WWORK_START)
<= w.WWORK_FINISH
and r.Res_Name like @res_name
and w.wwork_start between @Begin_Date and @End_Date
UNION
SELECT w1.res_name as Lead, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME,
a.ASSN_ACT_WORK / 60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000
AS assigned_work, a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work,
a.WASSN_SEND_UPDATE_DATE AS update_sent_date, (w.wwork_value) / 60000 AS
[work],
DateAdd(d, 7, w.WWORK_START) AS work_date,
w.wwork_start, w.wwork_finish
FROM dbo.MSP_WEB_RESOURCES r INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON a.WRES_ID = r.WRES_ID
INNER JOIN
dbo.MSP_WEB_PROJECTS p ON p.WPROJ_ID = a.WPROJ_ID
INNER JOIN
dbo.MSP_WEB_WORK w ON a.WASSN_ID = w.WASSN_ID
LEFT JOIN dbo.MSP_WEB_RESOURCES w1 ON a.WRES_ID_MGR = w1.WRES_ID
WHERE w.wwork_type = 1 AND w.wwork_value > 0 AND w.WWORK_START <>
w.WWORK_FINISH AND DateAdd(d, 7, w.WWORK_START) <= w.WWORK_FINISH
and r.Res_Name like @res_name
and w.wwork_start between @Begin_Date and @End_Date

End
GO
 
J

JBG

Hi Steven

I am using project server 2003 SP2.

I have written some VBA code to extract the number hours that someone have
captured as working on an task for a specific day. I used the timescaledata
function with the type pj?????actualwork.

The strange thing is that I have an employee who has been scheduled to
perform task A in project A on a date, but he then had to work on Task A in
project B for that day. He actually captured his worked hours against
project B and in the view "timesheet summary" of the PWA it is displayed
appropriately.

For some reason using the VBA code, and also I now executed your stored
procedure as per this posting, I get the records for the day he was booked
but did not work. When I open the project with MS project professional, then
the data is also displayed as captured against the incorrect task. I now
looks as if he has worked 16 hours for that day.

Have you actually seen something similar? or is there some setting I am not
doing correctly?

Steven Yetter said:
I built this stored procedure with help from this group. It may not be the
most efficient but it works for me.


CREATE PROCEDURE [dbo].[USP_MSPA_ALL_TASK_TIME_BY_RESOURCE_FOR_DATE_RANGE]
@Res_Name as varchar(50) ,
@Begin_Date as DateTime,
@End_Date as DateTime

AS

Begin

SELECT w1.res_name as Lead, p.PROJ_NAME as Project, r.RES_NAME as
Resource, a.TASK_NAME as Task, a.ASSN_ACT_WORK / 60000 AS actual_hours,
a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000
AS assigned_work, a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work,
a.WASSN_SEND_UPDATE_DATE AS update_sent_date, (w.wwork_value) / 60000 AS
[work],
w.wwork_start AS work_date, w.wwork_start as
work_start, w.wwork_finish as work_finish
FROM dbo.MSP_WEB_RESOURCES r INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON a.WRES_ID = r.WRES_ID
INNER JOIN
dbo.MSP_WEB_PROJECTS p ON p.WPROJ_ID = a.WPROJ_ID
INNER JOIN
dbo.MSP_WEB_WORK w ON a.WASSN_ID = w.WASSN_ID
LEFT JOIN dbo.MSP_WEB_RESOURCES w1 ON a.WRES_ID_MGR = w1.WRES_ID
WHERE w.wwork_type = 1 AND w.wwork_value > 0
and r.Res_Name like @res_name
and w.wwork_start between @Begin_Date and @End_Date
UNION
SELECT w1.res_name as Lead, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME,
a.ASSN_ACT_WORK / 60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000
AS assigned_work, a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work,
a.WASSN_SEND_UPDATE_DATE AS update_sent_date, (w.wwork_value) / 60000 AS
[work],
DateAdd(d, 1, w.WWORK_START) AS work_date,
w.wwork_start, w.wwork_finish
FROM dbo.MSP_WEB_RESOURCES r INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON a.WRES_ID = r.WRES_ID
INNER JOIN
dbo.MSP_WEB_PROJECTS p ON p.WPROJ_ID = a.WPROJ_ID
INNER JOIN
dbo.MSP_WEB_WORK w ON a.WASSN_ID = w.WASSN_ID
LEFT JOIN dbo.MSP_WEB_RESOURCES w1 ON a.WRES_ID_MGR = w1.WRES_ID
WHERE w.wwork_type = 1 AND w.wwork_value > 0 AND w.WWORK_START <>
w.WWORK_FINISH AND DateAdd(d, 1, w.WWORK_START)
<= w.WWORK_FINISH
and r.Res_Name like @res_name
and w.wwork_start between @Begin_Date and @End_Date
UNION
SELECT w1.res_name as Lead, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME,
a.ASSN_ACT_WORK / 60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000
AS assigned_work, a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work,
a.WASSN_SEND_UPDATE_DATE AS update_sent_date, (w.wwork_value) / 60000 AS
[work],
DateAdd(d, 2, w.WWORK_START) AS work_date,
w.wwork_start, w.wwork_finish
FROM dbo.MSP_WEB_RESOURCES r INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON a.WRES_ID = r.WRES_ID
INNER JOIN
dbo.MSP_WEB_PROJECTS p ON p.WPROJ_ID = a.WPROJ_ID
INNER JOIN
dbo.MSP_WEB_WORK w ON a.WASSN_ID = w.WASSN_ID
LEFT JOIN dbo.MSP_WEB_RESOURCES w1 ON a.WRES_ID_MGR = w1.WRES_ID
WHERE w.wwork_type = 1 AND w.wwork_value > 0 AND w.WWORK_START <>
w.WWORK_FINISH AND DateAdd(d, 2, w.WWORK_START)
<= w.WWORK_FINISH
and r.Res_Name like @res_name
and w.wwork_start between @Begin_Date and @End_Date
UNION
SELECT w1.res_name as Lead, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME,
a.ASSN_ACT_WORK / 60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000
AS assigned_work, a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work,
a.WASSN_SEND_UPDATE_DATE AS update_sent_date, (w.wwork_value) / 60000 AS
[work],
DateAdd(d, 3, w.WWORK_START) AS work_date,
w.wwork_start, w.wwork_finish
FROM dbo.MSP_WEB_RESOURCES r INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON a.WRES_ID = r.WRES_ID
INNER JOIN
dbo.MSP_WEB_PROJECTS p ON p.WPROJ_ID = a.WPROJ_ID
INNER JOIN
dbo.MSP_WEB_WORK w ON a.WASSN_ID = w.WASSN_ID
LEFT JOIN dbo.MSP_WEB_RESOURCES w1 ON a.WRES_ID_MGR = w1.WRES_ID
WHERE w.wwork_type = 1 AND w.wwork_value > 0 AND w.WWORK_START <>
w.WWORK_FINISH AND DateAdd(d, 3, w.WWORK_START)
<= w.WWORK_FINISH
and r.Res_Name like @res_name
and w.wwork_start between @Begin_Date and @End_Date
UNION
SELECT w1.res_name as Lead, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME,
a.ASSN_ACT_WORK / 60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000
AS assigned_work, a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work,
a.WASSN_SEND_UPDATE_DATE AS update_sent_date, (w.wwork_value) / 60000 AS
[work],
DateAdd(d, 4, w.WWORK_START) AS work_date,
w.wwork_start, w.wwork_finish
FROM dbo.MSP_WEB_RESOURCES r INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON a.WRES_ID = r.WRES_ID
INNER JOIN
dbo.MSP_WEB_PROJECTS p ON p.WPROJ_ID = a.WPROJ_ID
INNER JOIN
dbo.MSP_WEB_WORK w ON a.WASSN_ID = w.WASSN_ID
LEFT JOIN dbo.MSP_WEB_RESOURCES w1 ON a.WRES_ID_MGR = w1.WRES_ID
WHERE w.wwork_type = 1 AND w.wwork_value > 0 AND w.WWORK_START <>
w.WWORK_FINISH AND DateAdd(d, 4, w.WWORK_START)
<= w.WWORK_FINISH
and r.Res_Name like @res_name
and w.wwork_start between @Begin_Date and @End_Date
UNION
SELECT w1.res_name as Lead, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME,
a.ASSN_ACT_WORK / 60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000
AS assigned_work, a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work,
a.WASSN_SEND_UPDATE_DATE AS update_sent_date, (w.wwork_value) / 60000 AS
[work],
DateAdd(d, 5, w.WWORK_START) AS work_date,
w.wwork_start, w.wwork_finish
FROM dbo.MSP_WEB_RESOURCES r INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON a.WRES_ID = r.WRES_ID
INNER JOIN
dbo.MSP_WEB_PROJECTS p ON p.WPROJ_ID = a.WPROJ_ID
INNER JOIN
dbo.MSP_WEB_WORK w ON a.WASSN_ID = w.WASSN_ID
LEFT JOIN dbo.MSP_WEB_RESOURCES w1 ON a.WRES_ID_MGR = w1.WRES_ID
WHERE w.wwork_type = 1 AND w.wwork_value > 0 AND w.WWORK_START <>
w.WWORK_FINISH AND DateAdd(d, 5, w.WWORK_START)
<= w.WWORK_FINISH
and r.Res_Name like @res_name
and w.wwork_start between @Begin_Date and @End_Date
UNION
SELECT w1.res_name as Lead, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME,
a.ASSN_ACT_WORK / 60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000
AS assigned_work, a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work,
a.WASSN_SEND_UPDATE_DATE AS update_sent_date, (w.wwork_value) / 60000 AS
[work],
DateAdd(d, 6, w.WWORK_START) AS work_date,
w.wwork_start, w.wwork_finish
FROM dbo.MSP_WEB_RESOURCES r INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON a.WRES_ID = r.WRES_ID
INNER JOIN
dbo.MSP_WEB_PROJECTS p ON p.WPROJ_ID = a.WPROJ_ID
INNER JOIN
dbo.MSP_WEB_WORK w ON a.WASSN_ID = w.WASSN_ID
LEFT JOIN dbo.MSP_WEB_RESOURCES w1 ON a.WRES_ID_MGR = w1.WRES_ID
WHERE w.wwork_type = 1 AND w.wwork_value > 0 AND w.WWORK_START <>
w.WWORK_FINISH AND DateAdd(d, 6, w.WWORK_START)
<= w.WWORK_FINISH
and r.Res_Name like @res_name
and w.wwork_start between @Begin_Date and @End_Date
UNION
SELECT w1.res_name as Lead, p.PROJ_NAME, r.RES_NAME, a.TASK_NAME,
a.ASSN_ACT_WORK / 60000 AS actual_hours, a.ASSN_START_DATE AS start_date,
a.ASSN_FINISH_DATE AS finish_date, a.ASSN_WORK / 60000
AS assigned_work, a.ASSN_REM_WORK / 60000 AS remaining_work,
a.WASSN_LAST_WORK / 60000 AS assigned_last_work,
a.WASSN_SEND_UPDATE_DATE AS update_sent_date, (w.wwork_value) / 60000 AS
[work],
DateAdd(d, 7, w.WWORK_START) AS work_date,
w.wwork_start, w.wwork_finish
FROM dbo.MSP_WEB_RESOURCES r INNER JOIN
dbo.MSP_WEB_ASSIGNMENTS a ON a.WRES_ID = r.WRES_ID
INNER JOIN
dbo.MSP_WEB_PROJECTS p ON p.WPROJ_ID = a.WPROJ_ID
INNER JOIN
dbo.MSP_WEB_WORK w ON a.WASSN_ID = w.WASSN_ID
LEFT JOIN dbo.MSP_WEB_RESOURCES w1 ON a.WRES_ID_MGR = w1.WRES_ID
WHERE w.wwork_type = 1 AND w.wwork_value > 0 AND w.WWORK_START <>
w.WWORK_FINISH AND DateAdd(d, 7, w.WWORK_START) <= w.WWORK_FINISH
and r.Res_Name like @res_name
and w.wwork_start between @Begin_Date and @End_Date

End
GO


Nixm said:
I need a query that will display actual hours worked by resource for each
project and by project for each resource, totaled out by week. The date
stuff is easy and the groups etc. But I am having a fit figuring out where
the hours worked per date is stored. Is there a std query for this info?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Resource name inside resource sheet: per person, work per hours per week 0
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Work Budget Salary vs Real Time Salary 0
How to automate update on weekly reports spreadsheet 0
Timescalevalue update gives error 1101 0
Resource Pool changes actual hours in 2010 0
MS Project Overwrites Max. Resource hrs 0
Level resource allocation error 1

Top