|
|
Posted by DarkHades on 08/15/06 18:05
What you need to know?
The tables belong to MS Project 2003. You can have projects and Web
project. My application uses web project, but the problem is that web
project must be linked to project to uses Outline Codes (user defined
values). I need to retrieve all the tasks (MSP_VIEW_PROJ_TASKS_STD) for
a specific user (passed as @resourceName) which is stored in that table
in column TaskResourceNames (yes, if there's more resource assigned to
a task, they're all there seperate with a comma). I must retrieve these
data for a specific department which is stored is the
MSP_OUTLINE_CODES.
MSP_OUTLINE_CODES is used to hold departments as well as other
information which is also required in the query.
So basically, I need to get all the tasks from MSP_VIEW_PROJ_TASKS_ENT
different from -1. Then I need to filter the result so that only the
task for a specific enterprise is there (passed as @companyName). That
information is stored in the MSP_OUTLINE_CODES too. But there's more.
In that filtered result, I must look if the resource is assigned to it
(@resourceName) and if so, I must query again MSP_OUTLINE_CODES to get
the department. And then, I must return values from every step.
Maybe I just complicated things, let me know if I need to clarify
things!
Chris
ZeldorBlat wrote:
> DarkHades wrote:
> > Hi all, I have a stored proc which returns twice the result and I dont
> > know why. Can someone have a look at the following code?
> >
> > BTW, I commented the last SELECT/JOIN, cause that one doubled the
> > result too.
> >
> > CREATE procedure ent_tasks_per_user_company (
> > @companyName as varchar(50),
> > @resourceName as varchar(50)
> > )
> > AS
> >
> > SELECT
> > tasks.WPROJ_ID as WPROJ_ID, tasks.ENT_ProjectUniqueID as
> > ProjectUniqueID, tasks.ENT_TaskUniqueID as TaskUniqueID,
> > tasks.TaskEnterpriseOutlineCode1ID as TaskEnterpriseOutlineCode1ID,
> > codes.OC_NAME as OC_NAME, codes.OC_DESCRIPTION as OC_DESCRIPTION,
> > codes.OC_CACHED_FULL_NAME as OC_CACHED_FULL_NAME,
> > taskStd.TaskName as TaskName, taskStd.TaskResourceNames as
> > TaskResourceNames, taskStd.TaskPercentComplete as TaskPercentComplete
> >
> > INTO #myTemp
> >
> > FROM MSP_VIEW_PROJ_TASKS_ENT as tasks
> >
> > INNER JOIN MSP_OUTLINE_CODES as codes
> > ON
> > (
> > codes.CODE_UID = tasks.TaskEnterpriseOutlineCode1ID
> > AND
> > codes.OC_CACHED_FULL_NAME LIKE @companyName + '.%'
> > )
> >
> > INNER JOIN MSP_VIEW_PROJ_TASKS_STD as taskStd
> > ON
> > (
> > taskStd.WPROJ_ID = tasks.WPROJ_ID
> > AND
> > taskStd.TaskUniqueID = tasks.ENT_TaskUniqueID
> > --AND
> > --taskStd.TaskResourceNames LIKE '%' + @resourceName + '%'
> > )
> >
> > WHERE (tasks.TaskEnterpriseOutlineCode1ID <> -1)
> >
> > /*SELECT #myTemp.*, taskCode.OC_NAME as Department FROM #myTemp
> >
> > INNER JOIN MSP_OUTLINE_CODES taskCode
> > ON
> > (
> > taskCode.CODE_UID = #myTemp.TaskEnterpriseOutlineCode1ID
> > )*/
> >
> > SELECT * FROM #myTemp WHERE #myTemp.TaskResourceNames LIKE '%' +
> > @resourceName + '%'
> >
> >
> > Thank you!
> >
> > Chris
>
> Tough to say, especially since we don't know anything whatsoever about
> your tables.
Navigation:
[Reply to this message]
|