H
Hernandezz
Hi,
first, happy holidays…
I use EPM 2007 and wish buid a view in SQL for see the hours submitted
by resources every week. In this group I founded a great query but
don’t have the hours submitted by task and within a period of time
from webpart "my tasks"
This view allow see if submitted was accepted, was published, task,
project and resource name but no work submitted by task in a period of
time.
I search in the other tables (include MSP_TASKS_SUBMITTED) but no
founded where is actual_work_submitted field or other like that.
I hope this it possible, because it's necessary for my company to
verify every week if the resources submitted 40h.
Can you give me any support please?
thanks
Query I actually have is:
SELECT
Case TR.ASSN_TRANS_ACTION_ENUM
When 0 Then 'Pending'
When 1 Then 'Accepted'
When 2 Then 'Rejected' End As Accepted,
CASE
WHEN P.WPROJ_LAST_PUB > TR.ASSN_TRANS_SUBMIT_DATE
THEN 'Yes'
ELSE 'No'
END AS Is_Published,
A.TASK_NAME,
p.PROJ_NAME,
R.RES_NAME as ResourceName,
RB.RES_NAME as StatusManager,
TR.ASSN_TRANS_SUBMIT_DATE
FROM
dbo.MSP_ASSIGNMENT_TRANSACTIONS TR WITH(READPAST)
LEFT OUTER JOIN
dbo.MSP_CONVERSIONS C
ON
((C.CONV_VALUE = TR.ASSN_TRANS_ERROR_ENUM)
AND (STRING_TYPE_UID =
'EB15F998-82B2-4E2E-BFD4-BBF15AD90C23'))
LEFT OUTER JOIN
dbo.MSP_ASSIGNMENTS_SAVED A WITH(READPAST)
ON TR.ASSN_UID = A.ASSN_UID
LEFT OUTER JOIN
dbo.MSP_PROJECTS p
ON A.PROJ_UID = p.PROJ_UID
LEFT OUTER JOIN
dbo.MSP_TASKS_SAVED TA
ON TR.TASK_UID = TA.TASK_UID
LEFT OUTER JOIN
dbo.MSP_PROJECT_RESOURCES R
ON A.RES_UID = R.RES_UID
AND R.PROJ_UID = p.PROJ_UID
LEFT OUTER JOIN
dbo.MSP_PROJECT_RESOURCES RD
ON TR.ASSN_TRANS_DELEGATEE_RES_UID = RD.RES_UID
AND p.PROJ_UID = RD.PROJ_UID
LEFT OUTER JOIN
dbo.MSP_RESOURCES RD1
ON TR.ASSN_TRANS_DELEGATEE_RES_UID = RD1.RES_UID
LEFT OUTER JOIN
dbo.MSP_PROJECT_RESOURCES RS
ON TR.ASSN_TRANS_SUBMITTER_RES_UID = RS.RES_UID
AND p.PROJ_UID = RS.PROJ_UID
LEFT OUTER JOIN
dbo.MSP_RESOURCES RS1
ON TR.ASSN_TRANS_SUBMITTER_RES_UID = RS1.RES_UID
LEFT OUTER JOIN dbo.MSP_RESOURCES RA
ON TR.ASSN_TRANS_APPROVER_RES_UID = RA.RES_UID
LEFT OUTER JOIN
dbo.MSP_RESOURCES RB
ON A.WRES_UID_Manager = RB.RES_UID
WHERE
((TR.ASSN_TRANS_ACTION_ENUM = 1
and p.WPROJ_LAST_PUB < TR.ASSN_TRANS_SUBMIT_DATE)
OR tr.ASSN_TRANS_ACTION_ENUM = 0)
and RA.RES_NAME is not null
ORDER BY p.PROJ_NAME
first, happy holidays…
I use EPM 2007 and wish buid a view in SQL for see the hours submitted
by resources every week. In this group I founded a great query but
don’t have the hours submitted by task and within a period of time
from webpart "my tasks"
This view allow see if submitted was accepted, was published, task,
project and resource name but no work submitted by task in a period of
time.
I search in the other tables (include MSP_TASKS_SUBMITTED) but no
founded where is actual_work_submitted field or other like that.
I hope this it possible, because it's necessary for my company to
verify every week if the resources submitted 40h.
Can you give me any support please?
thanks
Query I actually have is:
SELECT
Case TR.ASSN_TRANS_ACTION_ENUM
When 0 Then 'Pending'
When 1 Then 'Accepted'
When 2 Then 'Rejected' End As Accepted,
CASE
WHEN P.WPROJ_LAST_PUB > TR.ASSN_TRANS_SUBMIT_DATE
THEN 'Yes'
ELSE 'No'
END AS Is_Published,
A.TASK_NAME,
p.PROJ_NAME,
R.RES_NAME as ResourceName,
RB.RES_NAME as StatusManager,
TR.ASSN_TRANS_SUBMIT_DATE
FROM
dbo.MSP_ASSIGNMENT_TRANSACTIONS TR WITH(READPAST)
LEFT OUTER JOIN
dbo.MSP_CONVERSIONS C
ON
((C.CONV_VALUE = TR.ASSN_TRANS_ERROR_ENUM)
AND (STRING_TYPE_UID =
'EB15F998-82B2-4E2E-BFD4-BBF15AD90C23'))
LEFT OUTER JOIN
dbo.MSP_ASSIGNMENTS_SAVED A WITH(READPAST)
ON TR.ASSN_UID = A.ASSN_UID
LEFT OUTER JOIN
dbo.MSP_PROJECTS p
ON A.PROJ_UID = p.PROJ_UID
LEFT OUTER JOIN
dbo.MSP_TASKS_SAVED TA
ON TR.TASK_UID = TA.TASK_UID
LEFT OUTER JOIN
dbo.MSP_PROJECT_RESOURCES R
ON A.RES_UID = R.RES_UID
AND R.PROJ_UID = p.PROJ_UID
LEFT OUTER JOIN
dbo.MSP_PROJECT_RESOURCES RD
ON TR.ASSN_TRANS_DELEGATEE_RES_UID = RD.RES_UID
AND p.PROJ_UID = RD.PROJ_UID
LEFT OUTER JOIN
dbo.MSP_RESOURCES RD1
ON TR.ASSN_TRANS_DELEGATEE_RES_UID = RD1.RES_UID
LEFT OUTER JOIN
dbo.MSP_PROJECT_RESOURCES RS
ON TR.ASSN_TRANS_SUBMITTER_RES_UID = RS.RES_UID
AND p.PROJ_UID = RS.PROJ_UID
LEFT OUTER JOIN
dbo.MSP_RESOURCES RS1
ON TR.ASSN_TRANS_SUBMITTER_RES_UID = RS1.RES_UID
LEFT OUTER JOIN dbo.MSP_RESOURCES RA
ON TR.ASSN_TRANS_APPROVER_RES_UID = RA.RES_UID
LEFT OUTER JOIN
dbo.MSP_RESOURCES RB
ON A.WRES_UID_Manager = RB.RES_UID
WHERE
((TR.ASSN_TRANS_ACTION_ENUM = 1
and p.WPROJ_LAST_PUB < TR.ASSN_TRANS_SUBMIT_DATE)
OR tr.ASSN_TRANS_ACTION_ENUM = 0)
and RA.RES_NAME is not null
ORDER BY p.PROJ_NAME