Report Hours worked for a resource during a period by project / ta

[yetters]

How can I query project to get a report that shows the hours worked by a
resource for a specific period grouped by project and task.

02/28/2005 - 03/04/2005

Resource A
ProjectA 20 hours
Task1 10 hours
Task2 10 hours
ProjectB 4 hours
Task1 4 hours
Resource B
ProjectA 1 hour
Task1 1 hour

If this can be done using project itself that would be great but I am
willing to go against the database if need be. Any help would be greatly
appreciated.

 

[John]

How can I query project to get a report that shows the hours worked by a
resource for a specific period grouped by project and task.

02/28/2005 - 03/04/2005

Resource A
ProjectA 20 hours
Task1 10 hours
Task2 10 hours
ProjectB 4 hours
Task1 4 hours
Resource B
ProjectA 1 hour
Task1 1 hour

If this can be done using project itself that would be great but I am
willing to go against the database if need be. Any help would be greatly
appreciated.

yetters,
As far as I know, there is no convenient way to get the type of report
you want directly from Project. It can be done however by either using
VBA or by querying the Project database. I have used the first method
many times so I favor that approach. I've never queried the database
directly so I can speak for that method.

Before you try either however you might want to check into a macro that
is already available for multi-projects. Go to the MVP website at:
http://www.mvps.org/project/vbacode.htm
and take a look at Rod Gill's "Multiple Project Reports" macro.

Hope this helps.
John
Project MVP

 

[Steven Yetter]

The management here is new to project management and there is no one here
who really understands the tool and I only have access to project through
the WEB interface. I however have access to the project database as it is on
a server that I have accesss to. I have built the following query but it
seems to drop some of the inputed work hours and I cannot determine why. Any
help would be greatly appreciated.

SELECT 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], w.WWORK_START as work_start,
w.WWORK_FINISH as work_finish
FROM MHCCSCP1.ProjectServer.dbo.MSP_WEB_RESOURCES r INNER JOIN
MHCCSCP1.ProjectServer.dbo.MSP_WEB_ASSIGNMENTS a ON
a.WRES_ID = r.WRES_ID INNER JOIN
MHCCSCP1.ProjectServer.dbo.MSP_WEB_PROJECTS p ON
p.WPROJ_ID = a.WPROJ_ID INNER JOIN
MHCCSCP1.ProjectServer.dbo.MSP_WEB_WORK w on
a.WASSN_ID = w.WASSN_ID
WHERE w.wwork_type = 1 and w.wwork_value > 0 and r.res_name like
'%thename%' and w.wwork_start between '01/01/2005' and '02/28/2005'

 

[MikeFrye]

From a stored proc of mine.. Strip out the stuff you dont need. You could
make a view(slow) from this querry

Mike Frye, Project Guy
mfrye at ahcfs dott comm



select

WA.WPROJ_ID,
WP.PROJ_ID, --new
PROJ_NAME,
TASK_NAME,
TASK_ID,
ASSN_START_DATE,
ASSN_FINISH_DATE,
WA.WASSN_ID,
TASK_UID_SUMMARY,
TASK_UID,
--convert(int,0) ProjectTimesheetPeriod , -- you wont have this function
WASSN_UPDATE_STATUS,
ASSN_WORK,
WA.WRES_ID,
WR.RES_NAME,
WR.RES_EUID, --new
WPROJ_IS_NONWORKING,
WPROJ_DELEG_ALLOWED,
WPROJ_TYPE,
OPT_DEF_START_TIME,
OPT_DEF_FINISH_TIME,
WD.WRES_ID_DELEGATOR,
WP.WPROJ_ADMINPROJECT ,
WWORK_VALUE,
WWORK_TYPE,
WWORK_START,
WWORK_FINISH,
WWORK_UPDATE_STATUS,
WWORK_APPROVAL_STATUS,
/********************************************************New */
WOBJ_DOC_REF_CNT,
WOBJ_ISSUE_REF_CNT,
WOBJ_RISK_REF_CNT,
ASSN_ACT_WORK,
WA.WASSN_NOTE_STATUS,
WA.RESERVED_DATA1 as TInsert,
WASSN_CURRENT_TRACKING_MODE,
WASSN_IS_NEW_ASSN,
WASSN_DELETED_IN_PROJ,
WR0.RES_NAME as TManagerName,
WASSN_CREATED_BY_RES + WASSN_ASSIGNED_TO_EXISTING as TNewTaskIndic,
abs(WDELEG_REJECT)+abs(WASSN_DELEG_PENDING - 1)+(CASE WHEN WR1.WRES_ID
is null THEN 0 ELSE 1 END)+(case when WA.WRES_ID_TEAM_LEAD is null then -1
else 2 end) as TDelegIndicator,
abs(WASSN_DELEG_PENDING) + abs(WASSN_DELETED_IN_PROJ) as TReadOnly,
(CASE WHEN WA.WRES_ID <> @ResourceID0 THEN 1 ELSE 0 END) as TFlagIsCopy,
WASSN_REQUEST_UPDATE,
WASSN_RESOURCE_UPDATE,
WASSN_ACTUALS_PENDING,
WASSN_SEND_UPDATE_DATE,
ASSN_REM_WORK,
WASSN_PCT_COMP,
TASK_IS_SUMMARY,
TASK_IS_MILESTONE,
WASSN_MGR_UPDATED,
WASSN_SEND_UPDATE_NEEDED,

WDELEG_DATE,
WRES_ID_TEAM_LEAD,
WNWRK_ENTRY_ID,
WR1.RES_NAME AS TDelegatorName,
WR2.RES_NAME AS TDelegeeName,
WR3.RES_NAME AS TLeadName





/*************************************************************/


into #aaaTempHours
FROM
(
select WASSN_ID from MSP_WEB_ASSIGNMENTS WA where (WA.WRES_ID =
@ResourceID0 and WA.WASSN_REMOVED_BY_RESOURCE = 0)
UNION
select WASSN_ID from MSP_WEB_ASSIGNMENTS WA (index =
I_ASSIGN_WRESID_TEAM_LEAD) where WA.WRES_ID_TEAM_LEAD = @ResourceID0
UNION
select WASSN_ID from MSP_WEB_ASSIGNMENTS WA
where WA.WRES_ID <> @ResourceID0
and WA.TASK_IS_SUMMARY = 0
and WA.WASSN_ID in (
select WDA2.WASSN_ID
from MSP_WEB_DELEGATIONS WD2
inner join
MSP_WEB_DELEGATION_ASSIGNMENTS WDA2 on WD2.WDELEG_ID = WDA2.WDELEG_ID
where WD2.WRES_ID_DELEGATOR = @ResourceID0
and WDA2.WDELEG_KEEP_COPY <> 0
and WD2.WDELEG_REJECT = 0
)
) as WT

INNER JOIN MSP_WEB_ASSIGNMENTS WA ON WT.WASSN_ID = WA.WASSN_ID
INNER JOIN MSP_WEB_PROJECTS WP ON WA.WPROJ_ID = WP.WPROJ_ID
INNER JOIN MSP_WEB_RESOURCES WR ON WA.WRES_ID = WR.WRES_ID
INNER JOIN MSP_WEB_WORK WK ON WK.WASSN_ID = WA.WASSN_ID AND WK.WRES_ID =
WR.WRES_ID --newmhf LEFT JOIN MSP_WEB_RESOURCES WR0 ON WA.WRES_ID_MGR =
WR0.WRES_ID
LEFT JOIN MSP_WEB_RESOURCES WR0 ON WA.WRES_ID_MGR = WR0.WRES_ID
LEFT JOIN MSP_WEB_RESOURCES WR3 ON WA.WRES_ID_TEAM_LEAD = WR3.WRES_ID
LEFT JOIN MSP_WEB_OBJECTS WO ON (WA.WPROJ_ID = WO.WOBJ_PROJ_ID AND
WA.TASK_UID = WO.WOBJ_TASK_ID)
LEFT JOIN MSP_WEB_DELEGATIONS WD ON WA.WASSN_LAST_DELEG_ID = WD.WDELEG_ID
LEFT JOIN MSP_WEB_RESOURCES WR1 ON WD.WRES_ID_DELEGATOR = WR1.WRES_ID
LEFT JOIN MSP_WEB_RESOURCES WR2 ON WD.WRES_ID_DELEGATEE = WR2.WRES_ID
WHERE WA.ASSN_BOOKING_TYPE = 0 and
(WA.WASSN_IS_CONFIRMED<>0 or WP.WPROJ_ADMINPROJECT = 1)
AND
(
(
WA.TASK_IS_SUMMARY<>0 OR
(
(WA.ASSN_START_DATE < @idt_BeginDate) AND
(WA.WASSN_PCT_COMP< 100)
)
OR
(
(
WA.ASSN_START_DATE IS NULL AND
(WA.ASSN_FINISH_DATE IS NULL OR WA.ASSN_FINISH_DATE<
@idt_BeginDate)
)
AND
WA.WASSN_PCT_COMP< 100
)
OR
(WA.WASSN_SEND_UPDATE_NEEDED <> 0) OR
(WA.WASSN_PCT_COMP< 100 AND WASSN_PCT_COMP>0) OR
(WA.WASSN_IS_NEW_ASSN <> 0) OR
(@p2Flag<>0)
)
OR
WP.WPROJ_ADMINPROJECT = 1
)
and
((WWORK_START>=@idt_BeginDate and WWORK_START<=@idt_EndDate) or
(WWORK_START<@idt_BeginDate and WWORK_FINISH>=@idt_BeginDate))

 

[Steven Yetter]

Mike many thanks for the SQL. I hope you do not mind but I have a few
questions. I have entered in 42 hours of work for the period of 2/27/2005
through 3/5/2005. When I run the query you gave me with the variables set
to:
select @ResourceID0 = 472 -- my resource ID
select @idt_BeginDate = '02/27/2005'
select @idt_EndDate = '03/05/2005'
select @p2Flag = 1

I get a sum for the work value of 102.0 when I expect to see 42.0 using the
TSQL you gave me.
What does the @p2Flag variable do? When I set it to zero I get a sum of
72.183333.

What I want to replicate in my report is what I can see on the WEB project
page when I select a date range I see the hours I have entered for that date
range by project and task.

Once again thanks!
Steve Yetter

SELECT
sum(WWORK_VALUE)/60000 as workvalue
FROM
(
select WASSN_ID from MSP_WEB_ASSIGNMENTS WA where (WA.WRES_ID =
@ResourceID0 and WA.WASSN_REMOVED_BY_RESOURCE = 0)
UNION
select WASSN_ID from MSP_WEB_ASSIGNMENTS WA (index =
I_ASSIGN_WRESID_TEAM_LEAD) where WA.WRES_ID_TEAM_LEAD = @ResourceID0
UNION
select WASSN_ID from MSP_WEB_ASSIGNMENTS WA
where WA.WRES_ID <> @ResourceID0
and WA.TASK_IS_SUMMARY = 0
and WA.WASSN_ID in (
select WDA2.WASSN_ID
from MSP_WEB_DELEGATIONS WD2
inner join
MSP_WEB_DELEGATION_ASSIGNMENTS WDA2 on WD2.WDELEG_ID = WDA2.WDELEG_ID
where WD2.WRES_ID_DELEGATOR = @ResourceID0
and WDA2.WDELEG_KEEP_COPY <> 0
and WD2.WDELEG_REJECT = 0
)
) as WT

INNER JOIN MSP_WEB_ASSIGNMENTS WA ON WT.WASSN_ID = WA.WASSN_ID
INNER JOIN MSP_WEB_PROJECTS WP ON WA.WPROJ_ID = WP.WPROJ_ID
INNER JOIN MSP_WEB_RESOURCES WR ON WA.WRES_ID = WR.WRES_ID
INNER JOIN MSP_WEB_WORK WK ON WK.WASSN_ID = WA.WASSN_ID AND WK.WRES_ID =
WR.WRES_ID --newmhf LEFT JOIN MSP_WEB_RESOURCES WR0 ON WA.WRES_ID_MGR =
WR0.WRES_ID
LEFT JOIN MSP_WEB_RESOURCES WR0 ON WA.WRES_ID_MGR = WR0.WRES_ID
LEFT JOIN MSP_WEB_RESOURCES WR3 ON WA.WRES_ID_TEAM_LEAD = WR3.WRES_ID
LEFT JOIN MSP_WEB_OBJECTS WO ON (WA.WPROJ_ID = WO.WOBJ_PROJ_ID AND
WA.TASK_UID = WO.WOBJ_TASK_ID)
LEFT JOIN MSP_WEB_DELEGATIONS WD ON WA.WASSN_LAST_DELEG_ID = WD.WDELEG_ID
LEFT JOIN MSP_WEB_RESOURCES WR1 ON WD.WRES_ID_DELEGATOR = WR1.WRES_ID
LEFT JOIN MSP_WEB_RESOURCES WR2 ON WD.WRES_ID_DELEGATEE = WR2.WRES_ID

WHERE WA.ASSN_BOOKING_TYPE = 0 and
(WA.WASSN_IS_CONFIRMED<>0 or WP.WPROJ_ADMINPROJECT = 1)
AND
(
(
WA.TASK_IS_SUMMARY<>0 OR
(
(WA.ASSN_START_DATE < @idt_BeginDate) AND
(WA.WASSN_PCT_COMP< 100)
)
OR
(
(
WA.ASSN_START_DATE IS NULL AND
(WA.ASSN_FINISH_DATE IS NULL OR WA.ASSN_FINISH_DATE <
@idt_BeginDate)
)
AND
WA.WASSN_PCT_COMP< 100
)
OR
(WA.WASSN_SEND_UPDATE_NEEDED <> 0) OR
(WA.WASSN_PCT_COMP< 100 AND WASSN_PCT_COMP>0) OR
(WA.WASSN_IS_NEW_ASSN <> 0) OR
(@p2Flag<>0)
)
OR
WP.WPROJ_ADMINPROJECT = 1
)
and
((WWORK_START>=@idt_BeginDate and WWORK_START<=@idt_EndDate) or
(WWORK_START<@idt_BeginDate and WWORK_FINISH>=@idt_BeginDate))

 

[Steven Yetter]

Mike thanks again and sorry for the multiple posts but the SQL I was using
is close to what I want but it does not take into account multiple entries
for a task during the same time period.
For instance if I enter into project the following:
S M T W T F S
Project A
Task1 4
Task2 4
Projet B
Task1 4 4

The SQL below returns the following
ProjectA SJY Task1 4
ProjectA SJY Task2 4
ProjectB SJY Task1 4

I somehow need to modify the SQL so that it finds all records in the
MSP_WEB_WORK table but I do not know how to do that.

Again any help would be greatly appreciated!

SELECT
p.PROJ_NAME,
r.RES_NAME,
a.TASK_NAME,
w.wwork_value/60000 as [work]
FROM
dbo.MSP_WEB_WORK w JOIN dbo.MSP_WEB_ASSIGNMENTS a ON
w.WASSN_ID = a.WASSN_ID
JOIN dbo.MSP_WEB_RESOURCES r ON
a.WRES_ID = r.WRES_ID
JOIN dbo.MSP_WEB_PROJECTS p ON
p.WPROJ_ID = a.WPROJ_ID
WHERE
w.wwork_type = 1
and w.wwork_value > 0
and res_name = 'sjy'
and w.WWORK_START between '3/6/2005' and '3/9/2005'
ORDER BY
p.PROJ_NAME,
r.RES_NAME,
a.TASK_NAME

 

[Gary L. Chefetz \(MVP\)]

Steven:

You found all the records, you simply didn't parse them correctly. Look at
the third record returned and note the start and end dates on the record.
You'll likely see that the end date of the record is one day after the start
and, therefore, that this record is recording 2 days work at 4hrs/day. This
is a standard record optimization routine in Project and Project Server.
Anytime a resource reports the same work value for repeating time periods,
the system condenses this to one record. This applies to all types of
time-phased data in the system.

Have you examined pjdb.htm and pjsvrdb.htm in your help files directory?
These are invaluable references for working with the database, and contain
both SQL samples to assist your dev efforts.

--

Gary L. Chefetz, MVP
"We wrote the books on Project Server"
http://www.msprojectexperts.com

For Project Server FAQs visit
http://www.projectserverexperts.com

For Project FAQs visit
http://www.mvps.org/project

-

 

[yetters]

Gary that rule makes sense to me now that I think about what I was seeing!

I did not have the help files you mentioned since I did not download the SDK
for project. I normally do not do development on project and our project
office, which just opened half way into a major project, does not have a
trained tool person she is learning from a book as we go.

I just have access to the database and this newsgroup. I appreciate the help
that everyone has given me.

If our project office whishes to enlist the help of contractors to assist
them develop other reports or tools to aid with project management, can you
or others on this newsgroup recommend some candidates?

Steve Yetter

Steven:

You found all the records, you simply didn't parse them correctly. Look at
the third record returned and note the start and end dates on the record.
You'll likely see that the end date of the record is one day after the start
and, therefore, that this record is recording 2 days work at 4hrs/day. This
is a standard record optimization routine in Project and Project Server.
Anytime a resource reports the same work value for repeating time periods,
the system condenses this to one record. This applies to all types of
time-phased data in the system.

Have you examined pjdb.htm and pjsvrdb.htm in your help files directory?
These are invaluable references for working with the database, and contain
both SQL samples to assist your dev efforts.

--

Gary L. Chefetz, MVP
"We wrote the books on Project Server"
http://www.msprojectexperts.com

For Project Server FAQs visit
http://www.projectserverexperts.com

For Project FAQs visit
http://www.mvps.org/project

-

Mike thanks again and sorry for the multiple posts but the SQL I was using
is close to what I want but it does not take into account multiple entries
for a task during the same time period.
For instance if I enter into project the following:
S M T W T F S
Project A
Task1 4
Task2 4
Projet B
Task1 4 4

The SQL below returns the following
ProjectA SJY Task1 4
ProjectA SJY Task2 4
ProjectB SJY Task1 4

I somehow need to modify the SQL so that it finds all records in the
MSP_WEB_WORK table but I do not know how to do that.

Again any help would be greatly appreciated!

SELECT
p.PROJ_NAME,
r.RES_NAME,
a.TASK_NAME,
w.wwork_value/60000 as [work]
FROM
dbo.MSP_WEB_WORK w JOIN dbo.MSP_WEB_ASSIGNMENTS a ON
w.WASSN_ID = a.WASSN_ID
JOIN dbo.MSP_WEB_RESOURCES r ON
a.WRES_ID = r.WRES_ID
JOIN dbo.MSP_WEB_PROJECTS p ON
p.WPROJ_ID = a.WPROJ_ID
WHERE
w.wwork_type = 1
and w.wwork_value > 0
and res_name = 'sjy'
and w.WWORK_START between '3/6/2005' and '3/9/2005'
ORDER BY
p.PROJ_NAME,
r.RES_NAME,
a.TASK_NAME

 

[Gary L. Chefetz \(MVP\)]

Steve:

You mean besides our company?<g>

--

Gary L. Chefetz, MVP
"We wrote the books on Project Server"
http://www.msprojectexperts.com

For Project Server FAQs visit
http://www.projectserverexperts.com

For Project FAQs visit
http://www.mvps.org/project

-

Gary that rule makes sense to me now that I think about what I was seeing!

I did not have the help files you mentioned since I did not download the SDK
for project. I normally do not do development on project and our project
office, which just opened half way into a major project, does not have a
trained tool person she is learning from a book as we go.

I just have access to the database and this newsgroup. I appreciate the help
that everyone has given me.

If our project office whishes to enlist the help of contractors to assist
them develop other reports or tools to aid with project management, can you
or others on this newsgroup recommend some candidates?

Steve Yetter

Steven:

You found all the records, you simply didn't parse them correctly. Look at
the third record returned and note the start and end dates on the record.
You'll likely see that the end date of the record is one day after the start
and, therefore, that this record is recording 2 days work at 4hrs/day. This
is a standard record optimization routine in Project and Project Server.
Anytime a resource reports the same work value for repeating time periods,
the system condenses this to one record. This applies to all types of
time-phased data in the system.

Have you examined pjdb.htm and pjsvrdb.htm in your help files directory?
These are invaluable references for working with the database, and contain
both SQL samples to assist your dev efforts.

--

Gary L. Chefetz, MVP
"We wrote the books on Project Server"
http://www.msprojectexperts.com

For Project Server FAQs visit
http://www.projectserverexperts.com

For Project FAQs visit
http://www.mvps.org/project

-

Mike thanks again and sorry for the multiple posts but the SQL I was using
is close to what I want but it does not take into account multiple entries
for a task during the same time period.
For instance if I enter into project the following:
S M T W T F S
Project A
Task1 4
Task2 4
Projet B
Task1 4 4

The SQL below returns the following
ProjectA SJY Task1 4
ProjectA SJY Task2 4
ProjectB SJY Task1 4

I somehow need to modify the SQL so that it finds all records in the
MSP_WEB_WORK table but I do not know how to do that.

Again any help would be greatly appreciated!

SELECT
p.PROJ_NAME,
r.RES_NAME,
a.TASK_NAME,
w.wwork_value/60000 as [work]
FROM
dbo.MSP_WEB_WORK w JOIN dbo.MSP_WEB_ASSIGNMENTS a ON
w.WASSN_ID = a.WASSN_ID
JOIN dbo.MSP_WEB_RESOURCES r ON
a.WRES_ID = r.WRES_ID
JOIN dbo.MSP_WEB_PROJECTS p ON
p.WPROJ_ID = a.WPROJ_ID
WHERE
w.wwork_type = 1
and w.wwork_value > 0
and res_name = 'sjy'
and w.WWORK_START between '3/6/2005' and '3/9/2005'
ORDER BY
p.PROJ_NAME,
r.RES_NAME,
a.TASK_NAME

 

[yetters]

Gary, who would I give as a contact in your company for our project office
people to contact?

Steve:

You mean besides our company?<g>

--

Gary L. Chefetz, MVP
"We wrote the books on Project Server"
http://www.msprojectexperts.com

For Project Server FAQs visit
http://www.projectserverexperts.com

For Project FAQs visit
http://www.mvps.org/project

-

Gary that rule makes sense to me now that I think about what I was seeing!

I did not have the help files you mentioned since I did not download the SDK
for project. I normally do not do development on project and our project
office, which just opened half way into a major project, does not have a
trained tool person she is learning from a book as we go.

I just have access to the database and this newsgroup. I appreciate the help
that everyone has given me.

If our project office whishes to enlist the help of contractors to assist
them develop other reports or tools to aid with project management, can you
or others on this newsgroup recommend some candidates?

Steve Yetter

Steven:

You found all the records, you simply didn't parse them correctly. Look at
the third record returned and note the start and end dates on the record.
You'll likely see that the end date of the record is one day after the start
and, therefore, that this record is recording 2 days work at 4hrs/day. This
is a standard record optimization routine in Project and Project Server.
Anytime a resource reports the same work value for repeating time periods,
the system condenses this to one record. This applies to all types of
time-phased data in the system.

Have you examined pjdb.htm and pjsvrdb.htm in your help files directory?
These are invaluable references for working with the database, and contain
both SQL samples to assist your dev efforts.

--

Gary L. Chefetz, MVP
"We wrote the books on Project Server"
http://www.msprojectexperts.com

For Project Server FAQs visit
http://www.projectserverexperts.com

For Project FAQs visit
http://www.mvps.org/project

-
Mike thanks again and sorry for the multiple posts but the SQL I was using
is close to what I want but it does not take into account multiple entries
for a task during the same time period.
For instance if I enter into project the following:
S M T W T F S
Project A
Task1 4
Task2 4
Projet B
Task1 4 4

The SQL below returns the following
ProjectA SJY Task1 4
ProjectA SJY Task2 4
ProjectB SJY Task1 4

I somehow need to modify the SQL so that it finds all records in the
MSP_WEB_WORK table but I do not know how to do that.

Again any help would be greatly appreciated!

SELECT
p.PROJ_NAME,
r.RES_NAME,
a.TASK_NAME,
w.wwork_value/60000 as [work]
FROM
dbo.MSP_WEB_WORK w JOIN dbo.MSP_WEB_ASSIGNMENTS a ON
w.WASSN_ID = a.WASSN_ID
JOIN dbo.MSP_WEB_RESOURCES r ON
a.WRES_ID = r.WRES_ID
JOIN dbo.MSP_WEB_PROJECTS p ON
p.WPROJ_ID = a.WPROJ_ID
WHERE
w.wwork_type = 1
and w.wwork_value > 0
and res_name = 'sjy'
and w.WWORK_START between '3/6/2005' and '3/9/2005'
ORDER BY
p.PROJ_NAME,
r.RES_NAME,
a.TASK_NAME

 

[Ed Morrison]

Steve,
You may contact Gary or myself at http://www.msprojectexperts.com.


--
Ed Morrison
msProjectExperts
"We wrote the books on Project Server"
http://www.msprojectexperts.com
FAQ - http://www.projectserverexperts.com

Gary, who would I give as a contact in your company for our project office
people to contact?

Steve:

You mean besides our company?<g>

--

Gary L. Chefetz, MVP
"We wrote the books on Project Server"
http://www.msprojectexperts.com

For Project Server FAQs visit
http://www.projectserverexperts.com

For Project FAQs visit
http://www.mvps.org/project

-

Gary that rule makes sense to me now that I think about what I was seeing!

I did not have the help files you mentioned since I did not download

the
SDK

for project. I normally do not do development on project and our project
office, which just opened half way into a major project, does not have a
trained tool person she is learning from a book as we go.

I just have access to the database and this newsgroup. I appreciate

the
help

that everyone has given me.

If our project office whishes to enlist the help of contractors to assist
them develop other reports or tools to aid with project management,

can
you

or others on this newsgroup recommend some candidates?

Steve Yetter


:

Steven:

You found all the records, you simply didn't parse them correctly.

Look
at

the third record returned and note the start and end dates on the record.
You'll likely see that the end date of the record is one day after

the
start

and, therefore, that this record is recording 2 days work at

4hrs/day.
This

is a standard record optimization routine in Project and Project Server.
Anytime a resource reports the same work value for repeating time periods,
the system condenses this to one record. This applies to all types of
time-phased data in the system.

Have you examined pjdb.htm and pjsvrdb.htm in your help files directory?
These are invaluable references for working with the database, and contain
both SQL samples to assist your dev efforts.

--

Gary L. Chefetz, MVP
"We wrote the books on Project Server"
http://www.msprojectexperts.com

For Project Server FAQs visit
http://www.projectserverexperts.com

For Project FAQs visit
http://www.mvps.org/project

-
Mike thanks again and sorry for the multiple posts but the SQL I

was
using

is close to what I want but it does not take into account multiple entries
for a task during the same time period.
For instance if I enter into project the following:
S M T W T F S
Project A
Task1 4
Task2 4
Projet B
Task1 4 4

The SQL below returns the following
ProjectA SJY Task1 4
ProjectA SJY Task2 4
ProjectB SJY Task1 4

I somehow need to modify the SQL so that it finds all records in the
MSP_WEB_WORK table but I do not know how to do that.

Again any help would be greatly appreciated!

SELECT
p.PROJ_NAME,
r.RES_NAME,
a.TASK_NAME,
w.wwork_value/60000 as [work]
FROM
dbo.MSP_WEB_WORK w JOIN dbo.MSP_WEB_ASSIGNMENTS a ON
w.WASSN_ID = a.WASSN_ID
JOIN dbo.MSP_WEB_RESOURCES r ON
a.WRES_ID = r.WRES_ID
JOIN dbo.MSP_WEB_PROJECTS p ON
p.WPROJ_ID = a.WPROJ_ID
WHERE
w.wwork_type = 1
and w.wwork_value > 0
and res_name = 'sjy'
and w.WWORK_START between '3/6/2005' and '3/9/2005'
ORDER BY
p.PROJ_NAME,
r.RES_NAME,
a.TASK_NAME

 

[MikeFrye]

Steven

I have a stored proc that reads my table and splits out the timephased data
by day. I would say it borders on complex.

You can reach me at
mfrye at dynlinkinc dot com

 

[Steven Yetter]

Thanks Mike I appreciate the offer. I too was able to come up with a
procedure that splits out the timephased data. I have two versions, the
first uses a cursor and the second uses unions of data extracted from rows
were the start_date is not equal to the finish_date.