Chris
Fortunately, I love tearing apart the database. Attached is one possible answer to your request, but I must preface this with some additional informatio
0) This query works on my PS03 server which uses MS SQL server for the database
1) I assumed you are after actual work (WWORK_TYPE=1), not the planned work that is in the task list
2) Figuring EXACTLY which actuals to count is non-trivial. I believe that the query below counts hours that are approved (but see note #4). It does not count (for example) hours that have been updated by the resource, but not yet approved. There are a lot of nuances in figuring out what should be counted when looking at the task list
3) Task List hours are stored as STARTDATE, ENDDATE, and Hours/day. So one entry may represent hours for more than one day. You will need to expand this data to get down to a true "Flat" view
4) I have tried to reverse-engineer the WebWork and Transaction tables to collect this data. I am sure I do not know all of the possible values and their meaning. I believe for your request (approved actuals only), the transaction table can be ignored, but I would do some testing of this query to validate that it does show what you are expecting, especially for time submissions that are in-progress, partially rejected, etc
5) Work in the DB is dangerous. Be careful. And don't blame me if you bust something.
The Query
SELECT MSP_WEB_PROJECTS.PROJ_NAME, MSP_WEB_ASSIGNMENTS.TASK_NAME, MSP_WEB_RESOURCES.RES_NAME,
MSP_WEB_WORK.WWORK_START, MSP_WEB_WORK.WWORK_FINISH, MSP_WEB_WORK.WWORK_VALUE / 60000.0 AS Hour
FROM MSP_WEB_WORK INNER JOI
MSP_WEB_ASSIGNMENTS ON MSP_WEB_WORK.WASSN_ID = MSP_WEB_ASSIGNMENTS.WASSN_ID INNER JOI
MSP_WEB_PROJECTS ON MSP_WEB_ASSIGNMENTS.WPROJ_ID = MSP_WEB_PROJECTS.WPROJ_ID INNER JOI
MSP_WEB_RESOURCES ON MSP_WEB_WORK.WRES_ID = MSP_WEB_RESOURCES.WRES_I
WHERE (MSP_WEB_WORK.WWORK_TYPE = 1) AND (MSP_WEB_WORK.WWORK_UPDATE_STATUS = 0
ORDER BY MSP_WEB_PROJECTS.PROJ_NAME, MSP_WEB_ASSIGNMENTS.TASK_NAME, MSP_WEB_RESOURCES.RES_NAME,
MSP_WEB_WORK.WWORK_STAR
Hope that helps..
Mik
----- Chris Walsh wrote: ----
I was wondering if someone has designed a SQL query that exports all data from Project Server 2003 into a flat file
I'm just after the timesheet data, I don't want to rip apart the database mysel
Thank
Chris Walsh
