M
Mike N.
Sorry but this is a long one, bear with me. We're trying to extract the timesheet records from the SQL Server database - everything seems to go fine until the PM rejects a task then the data we're pulling seems to go completely wrong. The strange thing is that the time sheet screens still reflect the data correctly so it has to be that we're pulling it out wrong but I can't see where. Below is one of the test scenarios we've used to reproduce the problem, plus the main SQL statement used - if anyone can show me what I've got wrong here, I'd appreciate it
Using two projects (numbered 2 and 3), each has four tasks numbered 1 through 4 (referred to as 2/1 - 2/4 and 3/1 - 3/4) all have more than 100 hours duration
In a given week for which the tasks 2/1, 3/1 and 3/4 are current, time is booked by user TM as follows..
m t w th
2/1 1 5 1
3/1 1 2 3 4
This is Updated (dialog appears confirming tasks sent to PM).
PM Accepts values (confirmed as correct) and Updates, Project is saved and published
TM screen refreshed values are all confirmed
Process is run to extract data from SQL Server ProjectServer database - values all correspond with expected values
In same week, values are changed by TM to..
m t w th
2/1 1 5 6 4
3/1 5 4 3 2
This is Updated (dialog appears confirming tasks sent to PM).
PM Accepts values (confirmed as correct) and Updates, Project is saved and published
TM screen refreshed values are all confirmed
Process is run to extract data from SQL Server ProjectServer database - values all correspond with expected values
Again in same week, values are changed by TM to..
m t w th
2/1 0 [del] 6 [del]
3/1 5.25 1.75 8 1
This is Updated (dialog appears confirming tasks sent to PM).
PM Accepts values (confirmed as correct) and Updates, Project is saved and published
TM screen refreshed values are all confirmed
Process is run to extract data from SQL Server ProjectServer database - values all correspond with expected values
Now, in same week, values are changed by TM to..
m t w th
2/1 0 1.5 2.5 .5
3/1 10 10 10 10 1
3/4 2 4 8 16 2
This is Updated (dialog appears confirming tasks sent to PM).
PM Accepts 2/1 and 3/1 values (confirmed as correct), rejects 3/4 then Updates, Project is saved and published
TM screen refreshed values are all confirmed showing 3/4 rejected
Process is run to extract data from SQL Server ProjectServer database..
Values for 2/1t, 2/1w, 2/1th and 3/1m are found to be correct - OK
No values for 3/1t, 3/1w, 3/1th amd 3/1f are not found at all - wrong
Values for 3/4 (all days) come through as approved - wrong
A number of ather scenarios have also been run and it only seems to be once a reject occurs that it goes wrong
The SQL statement used to extract the data is as follows..
SELECT MSP_WEB_ASSIGNMENTS.WPROJ_ID AS ProjectID, MSP_WEB_ASSIGNMENTS.TASK_UID AS TaskID,
MSP_WEB_RESOURCES.WRES_ID AS ResourceID, MSP_WEB_WORK.WWORK_START AS EntryDate, MSP_WEB_WORK.WWORK_VALUE AS Hours,
MSP_VIEW_PROJ_RES_ENT.ResourceEnterpriseNumber1 AS PayNumber, MSP_VIEW_PROJ_TASKS_ENT.TaskEnterpriseText1 AS WorkOrde
FROM MSP_WEB_PROJECTS INNER JOI
MSP_WEB_ASSIGNMENTS ON MSP_WEB_PROJECTS.WPROJ_ID = MSP_WEB_ASSIGNMENTS.WPROJ_ID INNER JOI
MSP_WEB_WORK ON MSP_WEB_ASSIGNMENTS.WASSN_ID = MSP_WEB_WORK.WASSN_ID INNER JOI
MSP_VIEW_PROJ_TASKS_ENT ON MSP_WEB_ASSIGNMENTS.WPROJ_ID = MSP_VIEW_PROJ_TASKS_ENT.WPROJ_ID AND
MSP_WEB_ASSIGNMENTS.TASK_UID = MSP_VIEW_PROJ_TASKS_ENT.ENT_TaskUniqueID INNER JOI
MSP_VIEW_PROJ_RES_ENT ON MSP_WEB_ASSIGNMENTS.WPROJ_ID = MSP_VIEW_PROJ_RES_ENT.WPROJ_ID INNER JOI
MSP_WEB_RESOURCES ON MSP_WEB_WORK.WRES_ID = MSP_WEB_RESOURCES.WRES_ID AND
MSP_VIEW_PROJ_RES_ENT.ENT_ResourceEnterpriseUniqueID = MSP_WEB_RESOURCES.RES_EUI
WHERE (MSP_WEB_WORK.WWORK_TYPE = 1) AND (MSP_WEB_WORK.WWORK_START >= CONVERT(DATETIME, '29-Mar-2004', 102)) AND
(MSP_WEB_WORK.WWORK_START <= CONVERT(DATETIME, '09-May-2004', 102)) AND (MSP_WEB_WORK.WWORK_APPROVAL_STATUS = 1)
Using two projects (numbered 2 and 3), each has four tasks numbered 1 through 4 (referred to as 2/1 - 2/4 and 3/1 - 3/4) all have more than 100 hours duration
In a given week for which the tasks 2/1, 3/1 and 3/4 are current, time is booked by user TM as follows..
m t w th
2/1 1 5 1
3/1 1 2 3 4
This is Updated (dialog appears confirming tasks sent to PM).
PM Accepts values (confirmed as correct) and Updates, Project is saved and published
TM screen refreshed values are all confirmed
Process is run to extract data from SQL Server ProjectServer database - values all correspond with expected values
In same week, values are changed by TM to..
m t w th
2/1 1 5 6 4
3/1 5 4 3 2
This is Updated (dialog appears confirming tasks sent to PM).
PM Accepts values (confirmed as correct) and Updates, Project is saved and published
TM screen refreshed values are all confirmed
Process is run to extract data from SQL Server ProjectServer database - values all correspond with expected values
Again in same week, values are changed by TM to..
m t w th
2/1 0 [del] 6 [del]
3/1 5.25 1.75 8 1
This is Updated (dialog appears confirming tasks sent to PM).
PM Accepts values (confirmed as correct) and Updates, Project is saved and published
TM screen refreshed values are all confirmed
Process is run to extract data from SQL Server ProjectServer database - values all correspond with expected values
Now, in same week, values are changed by TM to..
m t w th
2/1 0 1.5 2.5 .5
3/1 10 10 10 10 1
3/4 2 4 8 16 2
This is Updated (dialog appears confirming tasks sent to PM).
PM Accepts 2/1 and 3/1 values (confirmed as correct), rejects 3/4 then Updates, Project is saved and published
TM screen refreshed values are all confirmed showing 3/4 rejected
Process is run to extract data from SQL Server ProjectServer database..
Values for 2/1t, 2/1w, 2/1th and 3/1m are found to be correct - OK
No values for 3/1t, 3/1w, 3/1th amd 3/1f are not found at all - wrong
Values for 3/4 (all days) come through as approved - wrong
A number of ather scenarios have also been run and it only seems to be once a reject occurs that it goes wrong
The SQL statement used to extract the data is as follows..
SELECT MSP_WEB_ASSIGNMENTS.WPROJ_ID AS ProjectID, MSP_WEB_ASSIGNMENTS.TASK_UID AS TaskID,
MSP_WEB_RESOURCES.WRES_ID AS ResourceID, MSP_WEB_WORK.WWORK_START AS EntryDate, MSP_WEB_WORK.WWORK_VALUE AS Hours,
MSP_VIEW_PROJ_RES_ENT.ResourceEnterpriseNumber1 AS PayNumber, MSP_VIEW_PROJ_TASKS_ENT.TaskEnterpriseText1 AS WorkOrde
FROM MSP_WEB_PROJECTS INNER JOI
MSP_WEB_ASSIGNMENTS ON MSP_WEB_PROJECTS.WPROJ_ID = MSP_WEB_ASSIGNMENTS.WPROJ_ID INNER JOI
MSP_WEB_WORK ON MSP_WEB_ASSIGNMENTS.WASSN_ID = MSP_WEB_WORK.WASSN_ID INNER JOI
MSP_VIEW_PROJ_TASKS_ENT ON MSP_WEB_ASSIGNMENTS.WPROJ_ID = MSP_VIEW_PROJ_TASKS_ENT.WPROJ_ID AND
MSP_WEB_ASSIGNMENTS.TASK_UID = MSP_VIEW_PROJ_TASKS_ENT.ENT_TaskUniqueID INNER JOI
MSP_VIEW_PROJ_RES_ENT ON MSP_WEB_ASSIGNMENTS.WPROJ_ID = MSP_VIEW_PROJ_RES_ENT.WPROJ_ID INNER JOI
MSP_WEB_RESOURCES ON MSP_WEB_WORK.WRES_ID = MSP_WEB_RESOURCES.WRES_ID AND
MSP_VIEW_PROJ_RES_ENT.ENT_ResourceEnterpriseUniqueID = MSP_WEB_RESOURCES.RES_EUI
WHERE (MSP_WEB_WORK.WWORK_TYPE = 1) AND (MSP_WEB_WORK.WWORK_START >= CONVERT(DATETIME, '29-Mar-2004', 102)) AND
(MSP_WEB_WORK.WWORK_START <= CONVERT(DATETIME, '09-May-2004', 102)) AND (MSP_WEB_WORK.WWORK_APPROVAL_STATUS = 1)