April 3, 2003 at 7:01 pm
Hello GREAT gurus!
I have a problem but before I explain, please allow me to briefly explain what is going on.
I coded a stored proc that associates tasks to a particular project.
What this means, according to the original spec is that all tasks are standard. For instance, the client standardized the tasks by saying that any time a new project is created, that project must be associated with the same 8 tasks.
That was fine so I coded the stored proc. Please see the original stored proc.
Now the client has changed its mind and are now asking us to add a new field called projectType.
Now, tasks are associated to a project based on the project type.
Here is the table structures:
Task:
taskid int primary key,
taskName varchar(50). This table is prepopulated with data.
Eg.
taskID taskName
1 define Problem
2 analyze problem
3 feasibility study
4 code/programming
5 testing
6 implementation
7 training
8 maintenance
This is the task table schema ( with above data already populated)
Status table
statusId int primary key,
statusName varchar(50) - this also has prepopulated data
e.g.
StatusID StatusName
1 Not Started
2 Started
3 In progress
4 On hold
5 Completed.
Finally project table
ProjectID int primary key identity,
projName varchar(50),
ProjType varchar(50),
assignedTo varchar(50),
startDate datetime,
endDate datetime
(just a few fields, there are more)
My question:
How can I modify this stored proc so that when a client selects a prjectType and a projectName, the tasks associated to that project depends on the projectType.
Example, if projectType is "New DB Creation", there could be only 3 tasks associated with it out of the 8 tasks listed above.
Can someone please help me modify.
Here is the current stored proc.
Thanks in advance for your help!
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spInsertGISProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spInsertGISProject]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Create Procedure spInsertGISProject(
@requestorname varchar(50),
@division varchar(50),
@dueDate datetime,
@projName varchar(50),
@justification varchar(2000),
@mapTitle varchar(50),
@reqDate datetime,
@description varchar(4000),
@EstCompDate datetime,
@Assigned varchar(50),
@Priority varchar(50),
@Purpose varchar(500),
@Geographic varchar(200),
@name varchar(50))
AS
Declare
@ProjectID int
/* Create and populate new Project record */
Insert into tblProjects(
Section,
requestorName,
division,
startDate,
projName,
justification,
mapTitle,
requestedCompletionDate,
email,
description,
estimatedCompletionDate,
AssignedTo,
Priority,
Purpose,
Geographic,
fullName,
Manager,
PR_Percent
)
VALUES(
'IT',
@requestorName,
@division ,
@dueDate,
@projName,
@justification,
@mapTitle,
@reqDate ,
'jane@yhoo.com',
@description,
@EstCompDate,
@Assigned,
@Priority,
@Purpose,
@Geographic,
@name,
'Jane Doe',
0)
/* Move the identity value of the new record into a variable */
SET @ProjectID = @@IDENTITY
/* Create and populate new Task record */
INSERT INTO tblProjectTasks (
Project_ID,
TaskID,
statusID
)
SELECT @ProjectID,
t.taskID,
s.StatusID
FROM tblTasks t
CROSS JOIN tblstatus s
where s.statusID = 1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
April 4, 2003 at 1:29 am
You have not specified the relationship between projectType and Task.
Assuming two new tables
tblProjectType:
typeID int primary key
projectType varchar(50)
tblTypeTask
typeID int
taskID int
Then change insert/select to
INSERT INTO tblProjectTasks (
Project_ID,
TaskID,
statusID
)
SELECT @ProjectID,
t.taskID,
s.StatusID
FROM (SELECT b.taskID FROM tblProjectType a INNER JOIN tblTypeTask b ON b.typeID = a.typeID WHERE a.projectType = 'New DB Creation') t
CROSS JOIN tblstatus s
where s.statusID = 1
Far away is close at hand in the images of elsewhere.
Anon.
April 4, 2003 at 6:50 am
hi David!
That is great, thank you but I still some issues here.
First, there will be a combination of projecttypes with tasks.
In other words, there could be one task associated with a projectType, just as there could be 8 tasks associated with a project Type.
Do you think the inner join will capture this?
Secondly, the 'New DB Creation' is an example of a projectType. There are more.
So what I am trying to do basically is to ensure that when a project type is selected, a task or tasks associated with it is(are) displayed.
Thank for being helpful, David!
April 4, 2003 at 7:01 am
What I outlined should work fo you. My tblProjectType would contain the project types, e.g.
1 New DB Creation
2 DB Amendment
...
and tblTypeTask would contain the relationships of projectTypes and Tasks, e.g.
1 1
1 4
2 1
2 2
2 3
would state that projectType 1 (New DB Creation) would be associated with tasks 1 & 4 and projectType 2 with tasks 1,2,3.
The inner join would only give you the tasks associated with the selected projectType which would then be cross joined with the status table.
Far away is close at hand in the images of elsewhere.
Anon.
April 4, 2003 at 7:07 am
You are very good, indeed.
Thank you!(again)
April 4, 2003 at 8:11 am
Hi David!
Please forgive me for bothering you again.
I am still confused about one thing.
If we say:
WHERE a.projectType = 'New DB Creation', are we not restricted the display to just this only ProjectType 'New DB Creation'?
In a situation such as ours where we have about 12 ProjectTypes, will this solve the problem.
Please be patient with this last question.
Thanks you very much!
April 4, 2003 at 9:03 am
Declare a new parameter in your proc
@projectType varchar(50)
and change the where to
WHERE a.projectType = @projectType
I have noticed that you have specified projectType in your list of 'project table' columns but not in your insert statement in your proc. Are these different tables?
Far away is close at hand in the images of elsewhere.
Anon.
April 4, 2003 at 11:02 am
hi David!
I thank you much for your patience and help.
To answer your question concerning projectType column, that entire stored proc was the original one where the tasks are same no matter what new project is created.
This is the new one, including the lastest change you recommended.
--Create table ProjectTypeTasks (ProjectType varchar(50) NOT NULL, TaskName varchar(50) NOT NULL Constraint PK_ProjectTypeTasks PRIMARY KEY(ProjectType, TaskName))
--insert into ProjectTypeTasks
--values(1,'New IT Equipment Order','Complete Work Order')
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spInsertNProject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spInsertNProject]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Create Procedure spInsertNProject(
@requestorname varchar(50),
@requestorLoc varchar(50),
@dueDate datetime,
@projName varchar(50),
@justification varchar(1000),
@reqDate datetime,
@description varchar(2000),
@Assigned varchar(50),
@Priority varchar(50),
@EstCompDate datetime,
@name varchar(50))
AS
Declare
@ProjectID int,
@projectType varchar(50)
/* Create and populate new Project record */
Insert into nettblProjects(
Section,
requestorName,
requestorLoc,
division,
startDate,
projName,
justification,
mapTitle,
requestedCompletionDate,
email,
description,
estimatedCompletionDate,
AssignedTo,
Priority,
Purpose,
Geographic,
fullName,
Manager,
PR_Percent)
VALUES(
'Networking',
@requestorName,
@requestorLoc,
'Not Applicable' ,
@dueDate,
@projName,
@justification,
'Not Applicable',
@reqDate ,
'ryan.fernandes@co.fulton.ga.us',
@description,
@EstCompDate,
@Assigned,
@Priority,
'NA',
'NA',
@name,
'Ryan Fernandes',
0)
/* Move the identity value of the new record into a variable */
SET @ProjectID = @@IDENTITY
/* Create and populate new tblprojecttasks record */
INSERT INTO NettblProjectTasks (
Project_ID,
TaskID,
statusID
)
SELECT @ProjectID,
t.taskID,
s.StatusID
FROM (SELECT b.taskID FROM NettblProjectType a INNER JOIN NettblTypeTask b ON b.typeID = a.typeID WHERE a.projectType = @projectType) t
CROSS JOIN tblstatus s
where s.statusID = 1
Please accept my many thanks.
April 7, 2003 at 2:26 am
Thats great, it's what we're here for.
What I meant with my last post was to create a parameter to the proc not in, such as
Create Procedure spInsertNProject(
@requestorname varchar(50),
@requestorLoc varchar(50),
@dueDate datetime,
@projName varchar(50),
@justification varchar(1000),
@reqDate datetime,
@description varchar(2000),
@Assigned varchar(50),
@Priority varchar(50),
@EstCompDate datetime,
@name varchar(50),
@projectType varchar(50))
AS
Declare
@ProjectID int
...
In your proc you never set @projectType and therefore it will be null. Sorry if I caused any confusion.
Far away is close at hand in the images of elsewhere.
Anon.
April 7, 2003 at 12:19 pm
thanks again, you've been very helpful indeed.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply