Looping through an sql server Table, counting values and inseritng data in another table. C#/SQL erver

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

This is the structure of tables in my database. I need to Check Preferences value for each Student in "Preferences Column" and insert a "Group ID" ins "Students Table" for, for example "Stud A" and and all entries in "Preferences column" for "Stud A". I need to generate "group ID" using a Loop or something but i have to check that in "Students Table" minimum 5 and maximum 8 students can have same "group ID". Can anyone provide some code examples for similar problems.

Preference_Table
--------------------------
|student | Preferences |
--------------------------
Stud A   |  Stud B  
Stud A   |  Stud C
Stud B   |  Stud E
Stud B   |  Stud A
Stud C   |  Stud F
Stud F   |  Stud B
--------------------------


    (Prefrences_Table)
--------------------------
    |student | Group ID/Name |
    --------------------------
    Stud A   |    
    Stud B   |  
    Stud C   |  
    Stud D   |  
    Stud E   |  
    Stud F   |  
    --------------------------

Posted 8-Oct-13 1:06am

Mubshir

Add a Solution

Comments

RedDk 8-Oct-13 12:05pm

How is this not the same question?

http://www.codeproject.com/Questions/660571/Divide-students-in-groups-based-on-preferences

http://www.codeproject.com/Questions/661776/Check-how-many-columns-contain-value-in-each-row-o

http://www.codeproject.com/Questions/664815/check-column-values-in-sql-server-table-compare-th

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mehdy Moini · Accepted Answer · 2013-10-08T08:10:00

Hi, if you remember your last question with three Choices for preferring for each students,i rated for most preferred students and give them rate, and the sql code is below , you need to continue and set down the students in their group,in fact groups are found base on most preferred students , you only need set the students in their group they said prefer:

SQL

<pre>--table for  rate of preference for prefered students
if OBJECT_ID(N'StudentCoreRate','U') !=0
drop table StudentCoreRate 
create table StudentCoreRate (StudentID int, Rate decimal(10,2))
--drop table #FirstPrefer
--drop table #SecondPrefer
--drop table #ThirdPrefer

select  FirstPrefer.Prefere1 as sid, Count(FirstPrefer.Prefere1)  FirstPrefer into #FirstPrefer
from Preference FirstPrefer 
group by FirstPrefer.Prefere1
--select * from #FirstPrefer


--update more prefered students by thier ID as thier groupID
insert into StudentCoreRate select Students.ID , FirstPrefer
from Students  
inner join #FirstPrefer on Students.ID = #FirstPrefer.SID
where FirstPrefer > 1

select  SecondPrefer.Prefere2 sid, Count(SecondPrefer.Prefere2)  SecondPrefer into #SecondPrefer
from  Preference SecondPrefer 
group by SecondPrefer.Prefere2

--I divide secondPrefer into 2 as it is SecondPrefer
--In this Update add secondPrefered to students rate who get rate from first prefered, and in next insert i add students who
--only have rated as second prefere or third prefered
update StudentCoreRate set Rate = Rate + cast(SecondPrefer as decimal)/2
from #SecondPrefer 
inner join StudentCoreRate on StudentCoreRate.StudentID = #SecondPrefer.SID
where SecondPrefer > 1
--i add students who only have rated as second prefere or third prefered
insert into StudentCoreRate select Students.ID , cast(#SecondPrefer.SecondPrefer as decimal)/2--,  StudentCoreRate.StudentID 
from Students  
inner join #SecondPrefer on Students.ID = #SecondPrefer.SID
left join StudentCoreRate on StudentCoreRate.StudentID = #SecondPrefer.SID
where StudentCoreRate.StudentID is null
and #SecondPrefer.SecondPrefer > 1



select  ThirdPrefer.Prefere3 sid, Count(ThirdPrefer.Prefere3)  ThirdPrefer into #ThirdPrefer
from Preference ThirdPrefer 
group by ThirdPrefer.Prefere3



update StudentCoreRate set Rate = Rate + cast(ThirdPrefer as decimal)/3
from #ThirdPrefer 
inner join StudentCoreRate on StudentCoreRate.StudentID = #ThirdPrefer.SID
where ThirdPrefer > 1
--i add students who only have rated as second prefere or third prefered
insert into StudentCoreRate select Students.ID , cast(#ThirdPrefer.ThirdPrefer as decimal)/3
from Students  
inner join #ThirdPrefer on Students.ID = #ThirdPrefer.SID
left join StudentCoreRate on StudentCoreRate.StudentID = #ThirdPrefer.SID
where StudentCoreRate.StudentID is null
and ThirdPrefer > 1

--then you need the rate of perefering for students who prefered more prefered students

select * from StudentCoreRate
order by rate desc
select * from #SecondPrefer
select * from #ThirdPrefer
select * from Preference

drop table #FirstPrefer
drop table #SecondPrefer
drop table #ThirdPrefer