Just wanted to check, before I start looking to reinvent the wheel, if someone has a query/ NSQL to pull up the Primary Role Vs Investment Role Vs Task Role for resources ?
tables and columns as follows
Primary Role: COLUMN = prprimaryroleid TABLE = prj_resources
Project Role: COLUMN = prroleid TABLE = prteam
Assignment Role: COLUMN = prresourceid TABLE = prassignment
I'll leave the fun for you to write, if you give up on the problem you can buy the answer for a consultancy fee
So you already have an answer?
If i just paste the answer, where's the fun / learning in that
I am not saying that, just wanted to know if you already had one (or more).
We can compare if you come to next UK UG meeting.
I do, and wrote a process to keep them aligned
Look forward to seeing you
A process? Which aligns task and project roles with primary role or vice versa?
Sound like vertical silos where the resources in the organization are only allowed to do what their job description says or am I missing something?
... and each task assignment does have to have a role defined?
I will take a stab at it (the portlet)
Got this query from a near and dear friend of mine
select srmr.UNIQUE_NAME resource_id,
srmr_prim.full_name as primary_role,
task_dtl.prname as task_name,
from srm_resources srmr
join prj_resources prjr on srmr.id = prjr.prid and prjr.prisrole = 0
left join srm_resources srmr_prim on prjr.prprimaryroleid = srmr_prim.id
left join (select pt.prresourceid, invi.name as project_name, invi.id, srmr_proj.full_name as project_role
from prteam pt
join inv_investments invi on invi.id = pt.prprojectid
join srm_resources srmr_proj on pt.prroleid = srmr_proj.id
) proj_role on proj_role.prresourceid = srmr.id
left join (select pra.PRRESOURCEID, srmr_task.full_name as task_role, invi.id, pt.prname
from prassignment pra
join srm_resources srmr_task on pra.role_id = srmr_task.id
join prtask pt on pt.prid = pra.prtaskid
) task_dtl on task_dtl.id = proj_role.id and task_dtl.PRRESOURCEID = srmr.id
Certainly gives the correct columns
Just wondering if the left joins are good. In the above there are only ten resources and half a dozen of project and quite lot of nulls.
Say you have a couple of thousand resources and projects. Then some ten to hundred tasks in each project and each resource allocated into ten projects or so and a dozen assignments in each. In every case a huge number of results rows and if somebody do the huge job of defining task roles in addition to project and primary roles there will be nulls.
The option for that is to have separate queries for the three types of roles and the query to return only those that have roles and no nulls.
Retrieving data ...