October 29, 2014 at 1:28 pm
I have 2 tables table1 which I drop and recreate every day to get the new data and table2 which keeps historic records for all the students.
I need to compare both the table with matching studentid and combination of and program and plan.
There is a specialization subcategory “special_program” which could be “HN”’ or “SH”
A student can choose either HN or SH first and change it later or vice versa.
So it is possible that there could be 2 comments for SpecialProgram in table1 and If one record exist in table2 for 1 comment, after comparing I need to add the other record with other comment or if the record does not exist in table2 but does exist in table1, I need to update table2.
Below is my code:
Insert into table2 [Studentid,program,plan,specialprogram]
Select a.Studentid, a.program,a.plan,a.specialprogram from table1 a left outer join table2
On a.studentid=b.studentid
Where b.studentid is null or (a.specialProgram<>b.specialprogram)
This works fine if a student id does not exist in table 2 and I need to insert a new record from table 1 but I am getting duplicates with the (a.specialProgram<>b.specialprogram) when one specialprogram code exist and I am trying to enter another changed code for a student.
This is an issue since I will have multiple duplicates in table2 for the students where specialprogram has changed
Any suggestions please?
Thanks,
Blyzzard
October 29, 2014 at 2:25 pm
October 29, 2014 at 2:38 pm
It looks like your join criteria might be too loose what's happening is that if you already have say two records in your archive table with the same student id and different special programs it will always add a new record for that studentid as it will always find a match for the studentid with a different special program, try changing your join criteria to
ON a.Studentid = b.Studentid AND a.program = b.program AND a.plan = b.plan AND a.specialprogram = b.specialprogram
and take out the a.specialprogram <> b.specialprogram clause in your where statement.
October 31, 2014 at 12:24 pm
Thanks ZZartin and JeeTee. It worked.
My Table Structure is :
Table1 :
Studentid,
Program,
Plan,
ACT score,
SAT score,
Rank,
SpecialProgram
Table2:
Studentid,
Program,
Plan,
SpecialProgram,
HN,
SH
where HN and SH are bit field to mark 1 or 0 based on the scores and the criteria.
Now I have another part of the problem.
Since we check test scores from Table1 and check if a student is eligible for SH or HN, based on some criteria, If a student was not eligible for either special program and reapplies with new test scores, I need to check if new test scores makes him eligible for either of the special program and update the flag field (SH or HN) accordingly.
I was thinking to create a temptable with last day's data for table1 as "temptable1" and compare it with today's data from table1 and if the test scores has changed for any of the student, then update table2 flag fields but not sure how to do this.
Please help.
Thanks,
Blyzzard
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply