Update value in table if value exists more than specified

Question

Update value in table if value exists more than specified

Viewing 7 posts - 16 through 21 (of 21 total)

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

MMartin1 One Orange Chip Points: 27795 More actions · Answer 1

I would still not mind knowing how this applies to a real world situation. Or are we just solving a fun programming problem? I read you that you want us to ignore the ID column but I find that difficult for it seems I am taking a dog and assigning him to a different breed. What is this solving?

----------------------------------------------------

Raxter Right there with Babe Points: 724 More actions · Answer 2

MMartin1 (2/25/2016)
I would still not mind knowing how this applies to a real world situation. Or are we just solving a fun programming problem? I read you that you want us to ignore the ID column but I find that difficult for it seems I am taking a dog and assigning him to a different breed. What is this solving?

Real word situation:

I have a temp table of data that has 'RANDOMLY' assigned instructor names to a students record.

We notice that some instructors have randomly been assigned to 10 different students (they should only be assigned to 5 students) and some instructors have only randomly been assigned to 3 students (should also be 5 students). How do I run an update to fix the records so each instructor is assigned to only 5 students (or less if the records are not even). The way I am thinking is that I need to loop through the records and perform an update until each instructor is only in the database 5 times. Is this a better example?

MMartin1 One Orange Chip Points: 27795 More actions · Answer 3

This does make more sense. How do you handle situations when all professors are maxed out and can take no more assignments? Create a new professor? If you provide create table statements and sample data to work with that is more representative of the situation, I can try to work off of that (hint: have three or more professors).

----------------------------------------------------

Raxter Right there with Babe Points: 724 More actions · Answer 4

MMartin1 (2/25/2016)
This does make more sense. How do you handle situations when all professors are maxed out and can take no more assignments? Create a new professor? If you provide create table statements and sample data to work with that is more representative of the situation, I can try to work off of that (hint: have three or more professors).

No, we can not create a new instructor. The goal is for each instructor to have the equal amount but if a few are within 1-2 less it is fine (because the table may not have an equal number of students and instructors). I just cant have 1 instructor be assigned to 6 students and another instructor be assigned to 20. It may help for me to post my statements that are inserting the records into the temp table. I am sure a better way of doing it than how I have it but I am not a SQL developer at all.. 😉

Here is what i have:

--CREATE TEMP TABLES

create table #mult_nUF (APPNo varchar(8), Major varchar(8));

create table #TempNomTable (APPNo varchar(8), InstructorID varchar(8));

create table #tempInstructor(InstructorID varchar(8), Speciality varchar(8));

--insert test data (I am providing 35 test records. in my real world case, i could have over 1000)

insert into #mult_nUF values(99988456, 'DESIGN');

insert into #mult_nUF values(00165102, 'SOFTDEV');

insert into #mult_nUF values(32178987, 'VETMED');

insert into #mult_nUF values(00089654, 'DESIGN');

insert into #mult_nUF values(20558796, 'NURSING');

insert into #mult_nUF values(33355894, 'VETMED');

insert into #mult_nUF values(11111555, 'NURSING');

insert into #mult_nUF values(99999999, 'SOFTDEV');

insert into #mult_nUF values(12345678, 'POLITS');

insert into #tempInstructor values('ABC22', 'DESIGN');

insert into #tempInstructor values('CDR4', 'POLITS');

insert into #tempInstructor values('RR34', 'VETMED');

insert into #tempInstructor values('CCR45', 'SOFTDEV');

insert into #tempInstructor values('NUTB22', 'NURSING');

insert into #tempInstructor values('TT5B22', 'BIOL');

--this is how I am randomly assigning the instructors to the student records. This is inserting into a temp table making sure an instructor is not assigned to a student whom has the same major as the instructors speciality. The goal is to make sure each instructor has an equal (or as equal as we can get) number of students assigned. Any ideas of how to modify or add on to accomplish this?

WITH Match_StudentsWithInstructors AS(

SELECT DISTINCT

APPNo,

RTRIM(Major) AS Major,

COUNT(1) as rowcnt

FROM #mult_nuf

GROUP BY APPNo,

RTRIM(Major)

)

, rownum_matches AS (

SELECT

m.APPNo,

t.InstructorID,

m.rowcnt,

ROW_NUMBER() OVER (PARTITION BY m.APPNo order by newid()) AS rownum

FROM Match_StudentsWithInstructors m

join #TempInstructor t ON t.Speciality != m.major

GROUP BY m.AppNo,

t.InstructorID, m.rowcnt

HAVING COUNT(t.InstructorID) <= 6

)

INSERT INTO #TempNomTable

SELECT APPNo, InstructorID FROM rownum_matches rm

WHERE rownum <= rowcnt

Steve Jones - SSC Editor SSC Guru Points: 736222 More actions · Answer 5

That makes more sense when you describe it. Part of the issue was that your original description and clarifications did not note that it doesn't matter what the new assignment is, only that you need balance. That changes things.

I don't have time to work on it, but I think you'd look to take everyone that has more than xx (say 5) and then do an assignment, taking a professor (or breed, color, car, whatever) and that has < xx and using them as the assignee.

I suspect there might be a couple passes here to do balancing, but I think that is what this type of problem really is. Balancing a load.

Raxter Right there with Babe Points: 724 More actions · Answer 6

Thanks for the information. Do you recommend I utilize cursors or a while statement to loop through and assign?

drew.allen SSC Guru Points: 76985 More actions · Answer 7

You don't need to use cursors. As I said earlier, NTILE is your friend. Here is a solution that gets close. I'm dividing each major as evenly as possible into each professor's specialty where they don't match. I guarantee that they don't match by using an NTILE over one fewer than the total number of specialties, adding the NTILE to the "id" of the major and taking the mod based on the total number of specialties. It's a bit confusing, but the math does work.

WITH Specialties AS (

SELECT ti.Speciality, ROW_NUMBER()OVER(ORDER BY ti.Speciality) - 1 AS specialty_id

FROM #tempInstructor ti

)

,

Student_advisors_specialty AS (

SELECT mnu.APPNo, mnu.Major, (s.specialty_id + NTILE( (SELECT COUNT(*) - 1 FROM Specialties) ) OVER(PARTITION BY mnu.Major ORDER BY NEWID())) % ( SELECT COUNT(*) FROM Specialties ) AS advisor_specialty

FROM Specialties AS s

INNER JOIN #mult_nUF mnu

ON s.Speciality = mnu.Major

)

SELECT *

FROM Student_advisors_specialty AS sas

INNER JOIN Specialties AS s

ON sas.advisor_specialty = s.specialty_id

INNER JOIN #tempInstructor ti

ON s.Speciality = ti.Speciality

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA