MSP_TIMEPHASEDDATA Interpretation

[JScannell]

After exporting a project to Access we import into SQL Server, the TIMEPHASEDDATA table's value_x fields are rather cryptic, as you know. The stored procedure "sp_pjactualsbyweek" that comes with the SQL Server SDK does a good job of totaling the hours for each resource, but we need the hours per month like they are shown in the "resource usage" view inside project.

Any ideas on how to do this?
----------------------------------------------------------
Also, many posts to this forum refer to having an external program that automates the export to Access process. If anyone has some VB code they are will into share, it will be most appreciated.

Thanks,
Jerry

Jerry Scannell
JerryScannell@cox.net or
JScannell@citystatecomputer.com

 

[PDQBach]

Please supply sp_pjactualsbyweek code. Thanks.

 

[JScannell]

It does a great job of converting the value_x values into grand totals, but you lose the granularity of the resource's monthly hours.

Here's the script:

--
-- sp_psactualsbyweek.sql
--
-- This stored procedure generates a report of the actual hours worked by a
-- resource by project and task for a specific date range from the table
-- MSP_WEB_WORK.
--
--DROP procedure sp_psactualsbyweek;
--GO

CREATE procedure sp_psactualsbyweek
-- date range variables
@startdate datetime,
@enddate datetime
as

-- cursor variables
declare @res_name nvarchar(510)
declare @proj_name nvarchar(510)
declare @task_name nvarchar(510)
declare @start datetime
declare @finish datetime
declare @value decimal(25,6)
declare @days int
declare @count int
declare @date datetime

-- temp table to hold normalized data
create table #actuals_data (
RES_NAME nvarchar(510),
PROJ_NAME nvarchar(510),
TASK_NAME nvarchar(510),
WORK_DAY datetime,
HOURS decimal(25,6) )

-- cursor to convert denormalized data
declare actuals_csr cursor for
select r.RES_NAME,
p.PROJ_NAME,
a.TASK_NAME,
w.WWORK_START,
w.WWORK_FINISH,
w.WWORK_VALUE
from MSP_WEB_RESOURCES r,
MSP_WEB_ASSIGNMENTS a,
MSP_WEB_PROJECTS p,
MSP_WEB_WORK w
where w.WWORK_TYPE = 1 -- actual work
and w.WASSN_ID = a.WASSN_ID
and a.WPROJ_ID = p.WPROJ_ID
and a.WRES_ID = r.WRES_ID
and ((w.WWORK_START between @startdate and @enddate) or (w.WWORK_FINISH between @startdate and @enddate))
order by 1, 2, 3, 4

open actuals_csr
fetch next from actuals_csr into @res_name, @proj_name, @task_name, @start, @finish, @value
while @@fetch_status = 0
begin
select @days = DATEDIFF(day, @start, @finish) + 1
select @count = 0
while @count < @days
begin
select @date = DATEADD(day ,@count, @start)
insert into #actuals_data values( @res_name, @proj_name, @task_name, @date, ((@value/1000)/60) )
select @count = @count + 1
end

fetch next from actuals_csr into @res_name, @proj_name, @task_name, @start, @finish, @value
end
close actuals_csr
deallocate actuals_csr

-- return results
select RES_NAME, PROJ_NAME, sum(HOURS) as 'TOTAL HOURS'
from #actuals_data
where WORK_DAY between @startdate and @enddate
group by RES_NAME, PROJ_NAME

drop table #actuals_data

GO


Jerry Scannell
JerryScannell@cox.net or
Paul.Scannell@ibtco.com

 

[PDQBach]

The 1000 is because Project stores the data in thousandths of a minute (go figure).

The 60 is because there are 60 minutes in an hour.

So ... it seems to me that the the routine is returning one day's work which means that someone has written a routine to call this one. If I'm correct then the routine he/she wrote has some code to pick off a week at a time. Look at that code and change it to pick off a month at a time.

In other words, here's what I think is happening:

Get StartDate
Get FinishDate
Adjust StartDate to Monday (might be Sunday)
Adjust FinishDate to Friday (might be Saturday)
Determine number of weeks between StartDate and FinishDate

for each resource
set loopTotalWork to 0
loop for NumberOfWeeks
set loopWork to 0
set loopDate to StartDate
loop 7 times
cal StoredProcedure to get work for loopDate
add work to loopWork
add 1 to loopDate
endloop
add loopWork to loopTotalWork
endloop
display resource name and work
next resource

Now, a month is not 4 weeks so you'll have to play with that loop so you're doing something like:

get start
get finish
adjust start to 1st of month
determine number of months

for each resource
for each month
set loopStartDate to 1st of the month
set loopEndDate to last of the month
determine number of days in this particular month
set loopWork to 0
loop NumberOfDaysThisMonth
call StoredProcedure
yada
yada
yada
end loop
display resource name, month, work
next month
next resources