livingsky - fixcourseobjectives

fixcourseobjectives.sql

update Settings set cValue='N' where cKey like 'LDAP/Login'


--begin transaction

declare @icourseObjectiveID int
declare @csubject varchar(500)
declare @mnotes varchar(500)

declare @icourseobjectiveid_tochangeto int
declare @icourseid_tochangeto int

declare @count int
set @count = 0

declare c cursor for
	select icourseObjectiveID from courseobjective where icourseid not in (select iCourseID from Course)
	
open c
fetch c into @icourseObjectiveID
while @@FETCH_STATUS = 0
begin
	
		
	
	--update vars
	set @csubject = (select cSubject from courseobjective where icourseobjectiveid = @icourseObjectiveID)	
	set @mnotes = (select mNotes from courseobjective where icourseobjectiveid = @icourseObjectiveID)		
	

	set @icourseobjectiveid_tochangeto = 
		(select top 1 icourseobjectiveid from courseobjective 
		where csubject = @csubject 
		and mnotes = @mnotes 
		and icourseid in (select icourseid from Course))

	
	set @icourseid_tochangeto = 
		(select icourseid from CourseObjective where iCourseObjectiveID = @icourseobjectiveid_tochangeto)	
	
	
	if( @icourseid_tochangeto > 0)
		--select 'before',* from CourseObjective where iCourseObjectiveID = @icourseObjectiveID
		update CourseObjective set iCourseID = @icourseid_tochangeto where iCourseObjectiveID = @icourseObjectiveID
	
	
	--select 'after', * from courseobjective where iCourseObjectiveID = @icourseObjectiveID
	
	
	--select 'before', * from StudentCourseObjective where iCourseObjectiveID = @icourseObjectiveID
	
	update StudentCourseObjective set iCourseObjectiveID = @icourseobjectiveid_tochangeto where iCourseObjectiveID = @icourseObjectiveID
		
	--select 'after' , * from StudentCourseObjective where iCourseObjectiveID = @icourseobjectiveid_tochangeto
	

fetch next from c into @icourseObjectiveID
end
close c
deallocate c


--select iStudentID, iReportPeriodID, icourseobjectiveid, count(*) from StudentCourseObjective
--group by  iStudentID, iReportPeriodID, icourseobjectiveid
--having count(*) > 1
--order by 4

--select * from Student where iStudentID = 1181
--select * from studentcourseobjective where iStudentID = 1181

--select * from courseobjective where icourseobjectiveid in (select iCourseObjectiveID from studentcourseobjective where iStudentID = 1181) order by icourseid

--rollback transaction



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


GO



declare @icourseObjectiveID int
declare @iCourseID int
declare @iOrder int



--loop through courses
declare c cursor for
	select distinct icourseid from CourseObjective
open c
fetch c into @icourseid
while @@FETCH_STATUS = 0
begin

	--select * from courseobjective where iCourseID = @icourseid
	
	
	--loop through unique cSubject/mNotes/iOrder groups (im using iOrder as its a group identifier)
	declare o cursor for
		select distinct iorder from CourseObjective 
		where iCourseID = @icourseid
		and (select count(*) 
			from CourseObjective 
			where iCourseID = @icourseid) > 1
		
		
	open o
	fetch o into @iOrder
	while @@FETCH_STATUS = 0
	begin
	
			set @icourseObjectiveID = (select min(iCourseObjectiveID) from CourseObjective where iOrder = @iOrder and iCourseID = @iCourseID )
			
			
			--determine if there is more than one courseobjective to deal with
			--select count(*) 
			--from CourseObjective 
			--where iOrder = @iOrder 
			--and iCourseID = @icourseid

				
			update StudentCourseObjective 
			set iCourseObjectiveID = @icourseObjectiveID
			from	
			(
					select 
						co.iCourseObjectiveID,
						co.iCourseID,
						co.iOrder,
						co.cSubject,
						co.mNotes,
						sco.istudentcourseobjectiveid as 'sss',
						sco.iStudentID
					from CourseObjective co
					full join StudentCourseObjective sco
						on co.iCourseObjectiveID = sco.iCourseObjectiveID
					where co.iOrder = @iOrder 
					and co.icourseid = @icourseid
					and sco.iCourseObjectiveID in (select iCourseObjectiveID from CourseObjective where iOrder = @iOrder and iCourseID = @icourseid)
			) a
				where iStudentCourseObjectiveID = a.sss
				


			--if there is a courseobjective record that is a duplicate and is unused, we will remove it
			delete
			from CourseObjective 
			where iOrder = @iOrder 
			and iCourseID = @icourseid
			and iCourseObjectiveID != @icourseObjectiveID
			and icourseobjectiveid not in (select iCourseObjectiveID from StudentCourseObjective)
			
	
	fetch next from o into @iOrder
	end
	close o
	deallocate o
	
	

fetch next from c into @icourseid
end
close c
deallocate c

for reference only