how to write a stored procedure for this

Question

how to write a stored procedure for this

iiit.raju

Old Hand

Points: 348
More actions
September 19, 2013 at 4:44 am

#297943

i have a table emp with columns empname and highprority jobs.
for example i have 3 emp and 10 high priority jobs(assume that high priority jobs are from h1- h10).I want to make sure to assign highpriority jobs in such a way that each employee must assign same no of jobs,( for example ,tsaliki has here 2 high priority jobs,sasi 2 and srinivas 1 .SO while inserting the next high priority job first it should assign the high priorty job h6 to srinivas since he has low high priorrity jobs compared to others.likewise everytime while assigning the jobs to each employee i want to make sure that all employess must have same no of high priority jobs or just 1 more .Becasue here there are 10 high priority jobs.So ultimately my goal is to assign 3 high priority jobs to each emp and the other can be assigned to any emp since all other 3 has same high proroty jobs.
The records of emp table are as follows-
empname highprorityjos
tsaliki h1
sasi h2
Srinivas h3
tsaliki h4
sasi h5.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

It seems you need some help with this. Many people around here would be happy to help. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 2

iiit.raju (9/19/2013)
i have a table emp with columns empname and highprority jobs.
for example i have 3 emp and 10 high priority jobs(assume that high priority jobs are from h1- h10).I want to make sure to assign highpriority jobs in such a way that each employee must assign same no of jobs,( for example ,tsaliki has here 2 high priority jobs,sasi 2 and srinivas 1 .SO while inserting the next high priority job first it should assign the high priorty job h6 to srinivas since he has low high priorrity jobs compared to others.likewise everytime while assigning the jobs to each employee i want to make sure that all employess must have same no of high priority jobs or just 1 more .Becasue here there are 10 high priority jobs.So ultimately my goal is to assign 3 high priority jobs to each emp and the other can be assigned to any emp since all other 3 has same high proroty jobs.
The records of emp table are as follows-
empname highprorityjos
tsaliki h1
sasi h2
Srinivas h3
tsaliki h4
sasi h5.

To add to what Sean already stated, I think you're making a mistake in considering only the priority. You should also consider the estimated duration of each job. You also haven't accounted for absenteeism. What happens when someone has 2 high priority jobs the day they go on vacation? You also haven't considered that if there are enough high priority jobs that none of the low priority jobs will ever be accomplished.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

enriquezreyjoseph Ten Centuries Points: 1291 More actions · Answer 3

Can you show us the code??..

cheers!!

"Be Brave even though you are not no one can tell the difference" - sir bernard tan

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 4

enriquezreyjoseph (9/20/2013)
Can you show us the code??..
cheers!!
"Be Brave even though you are not no one can tell the difference" - sir bernard tan

To whom are you speaking?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)