WWORK_APPROVAL_STATUS field

[nick_cam]

Hi,

I've written some sql to extract timephased data from the PS database
MSP_WEB_WORK table.

I'm only trying to extract approved hours so I've set the query to only
extract records where -

WWORK_UPDATE_STATUS = 0 and
WWORK_APPROVAL_STATUS = 1

The problem I have with this is that when WWORK_APPROVAL_STATUS = 1 I
am getting significatly less values than for the same time period in
the Portfolio Analyzer.

I've also checked resources timesheets and the query appears to be
missing some approved hours.

When I omit WWORK_APPROVAL_STATUS = 1 and only use
WWORK_UPDATE_STATUS = 0 as a filter I'm getting the correct results.

I was under the impression that to only pick up approved hours I'd have
to include WWORK_APPROVAL_STATUS = 1; or is there a reason why this
might filter legitimately approved actual hours?

We're using managed periods (if that makes a difference, wouldn't think
so though) and I have studied the DB files which didn't explain my
question.

Any help would be greatly appreciated,
Cheers,
NIck

 

[ITG_Mike]

Nick,

We get data from MSP_WEB_WORK also, but I wrote a procedure to "Expand" the
entries before we do any reporting. The problem for us (and this may be your
problem) is that multiple days with the same work value are represented by
one row in the Web Work table. If I work 8 hours a day on TASK1 this week,
that will be represented as one entry with a start date of 5/29 and a finish
date of 6/2 and a web work value of 480,000.

You need to take into account the cases where an entry represents more than
one day.

 

[nick_cam]

Hi ITG_Mike,

Thanks for the response.
I had the same problem when first writing the SQL but I've accounted
for this and am returning data based on daily values.

The problem I'm having is that with WWORK_APPROVAL_STATUS set to 1 I am
receiving incomplete data, but with that WHERE condition removed I seem
to recieve complete data. I thought that WWORK_APPROVAL_STATUS must be
set to 1 to only include approved hours?

I guess I'm just asking what is the risk of removing the
WWORK_APPROVAL_STATUS = 1 condition, will I be then picking up rejected
and unapproved hours?
If so, why am I missing out on approved hours when I include the
condtion?

Thanks,
Nick

 

[ITG_Mike]

Nick,

When I look for approved time, I query on WWORK_TYPE = 1 and
WWORK_UPDATE_STATUS = 0. I do not use WWORK_APPROVAL_STATUS at all.

Mike

 

[ITG_Mike]

I should add that we have been using WWORK_TYPE = 1 and
WWORK_UPDATE_STATUS = 0 to extract approved time for years and have always
seen good data -- this was not simply a guess.

Mike

 

[nick_cam]

Thanks Mike,

Thats exactly what I use when I seem to be getting the correct results.

I just remember reading somewhere (possibly in this group) that it is
necessary to use WWORK_APPROVAL_STATUS = 1 to filter unapproved or
rejected hours.

Thanks,
Nick