Design a table with Autometic sub category along with autometic Category

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Design a table with Autometic sub category along with autometic Category

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-12 : 10:57:26

Syed writes "I have a project which need to add subtask to a main task
I am creating a databse for a Helpdesk department
Peopel send their request online, but if they want to add some more stuff into the same request then database should assign a subtask Id to the same request number.
For example:

Jason send a request and the database assigned his request number: 101
Now Jason wants to add some minor detail to this request No 101
Now the Syestem should provide a subtask ID to this request
which is this
101-01
Next day Jason's Manager want add some more details to the project, so now system should assigned a
101-02 to the same project.
Marray Create a New request Now system would give an automatic Id No:102
Next day she wants to add some more stuff to the same request
Now system should assigned the id
102-01
So over all the databse table should be looks like this

Id- SubTask
101 01
101 02
101 03
102 01
102 02
103 00
104 01
104 02
104 03
104 04
104 05
104 06
105 01
105 02 "

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-09-12 : 11:58:18

You might want to try some of the following queries to update your fields.

when a request is modified you will have the current ID.

So,


SELECT (Max(SubTask) + 1) as NewSubTask FROM Tasks WHERE Id = @JobID

When a request is add you do not have a job id so you need to create on


SELECT (Max(Id) + 1) as NewJobID FROM Tasks

You could easily wrap all of this in a stored procedure


CREATE PROCEDURE sp_InsertJobTask (
@JobID integer,
@Col1 varchar,
@Col2 varchar
)

-- i am assuming Col1 and Col2 to be extra columns that you might need

if (@JobID Is NULL) begin
     --we don't have a current job so we need to add on
     INSERT INTO Tasks (Id, Col1, Col2) VALUES
     ((SELECT (Max(Id) + 1) as NewJobID FROM Tasks), Col1, Col2)
end else begin
     INSERT INTO Tasks (Id, SubTask, Col1, Col2) VALUES
     (@JobID, (SELECT (Max(SubTask) + 1) as NewSubTask FROM Tasks WHERE Id = @JobID), Col1, Col2
end

Got SQL?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources