Blackboard Learn: Fix broken Group Assignments (T-SQL / bb_bb60)

bb_step1-3.sql

/* 
 **Fix broken Group Assignments (T-SQL / bb_bb60)
   assumes step_0 is complete and that tables
		group_attempt_files_prv, attempt_prv and group_attempt_prv
   exist. see https://gist.github.com/hanleybrand/8ef04daac0e960b4da14
*/ 
use bb_bb60;

declare @COURSEID varchar
set @COURSEID = '111111111111111' # replace with valid course id

/* Repeat steps 1,2,3 for all affected course_IDs 
   from the results of the detection query. (And Commit;) */

/* Step 1 handle group_attempt_files  (T-SQL / bb_bb60) */ 

INSERT INTO group_attempt_files_prv
	      ([pk1],[files_pk1],[group_attempt_pk1],[file_type])
	      
	SELECT [pk1],[files_pk1],[group_attempt_pk1],[file_type] 
	FROM group_attempt_files where group_attempt_pk1 in(
		select pk1 from group_attempt where gradebook_main_pk1 in (
			select pk1 from gradebook_main where crsmain_pk1 in (
				select pk1 from course_main where course_id = @COURSEID)
			) and groups_pk1 is NULL);

DELETE group_attempt_files 
	where group_attempt_pk1 in (
		select pk1 from group_attempt where gradebook_main_pk1 in (
					select pk1 from gradebook_main where crsmain_pk1 in	(	
						select pk1 from course_main where course_id = @COURSEID
					)
			) and groups_pk1 is NULL);


/* Step 2 handle attempt (T-SQL / bb_bb60) */ 

INSERT INTO attempt_prv 
	   ([GRADEBOOK_GRADE_PK1], [PK1], [QTI_RESULT_DATA_PK1], [LINKREFID], [SCORE], 
		[GRADE], [STUDENT_COMMENTS], [INSTRUCTOR_COMMENTS], [INSTRUCTOR_NOTES], [STATUS], 
		[ATTEMPT_DATE], [DATE_ADDED], [DATE_MODIFIED], [LATEST_IND], [comment_public_ind], 
		[exempt_ind], [override_ind], [group_attempt_pk1], [student_submission], 
		[text_format_type], [feedback_format_type], [notes_format_type], 
		[first_graded_date], [last_graded_date], [graded_anonymously_ind], 
		[reconciliation_mode], [show_staged_feedback])

	SELECT  [GRADEBOOK_GRADE_PK1], [PK1], [QTI_RESULT_DATA_PK1], [LINKREFID], [SCORE], 
			[GRADE], [STUDENT_COMMENTS], [INSTRUCTOR_COMMENTS], [INSTRUCTOR_NOTES], [STATUS], 
			[ATTEMPT_DATE], [DATE_ADDED], [DATE_MODIFIED], [LATEST_IND], [comment_public_ind], 
			[exempt_ind], [override_ind], [group_attempt_pk1], [student_submission], 
			[text_format_type], [feedback_format_type], [notes_format_type], 
			[first_graded_date], [last_graded_date], [graded_anonymously_ind], 
			[reconciliation_mode], [show_staged_feedback] 
	FROM attempt where group_attempt_pk1 in (
		select pk1 from group_attempt where gradebook_main_pk1 in (
			select pk1 from gradebook_main where crsmain_pk1 in (
				select pk1 from course_main where course_id = @COURSEID
				)
			) and groups_pk1 is NULL);
		 
DELETE attempt where group_attempt_pk1 in (
	select pk1 from group_attempt where gradebook_main_pk1 in (
		select pk1 from gradebook_main where crsmain_pk1 in (
			select pk1 from course_main where course_id = @COURSEID
			)
	) and groups_pk1 is NULL);
		
		
/* Step 3 handle group_attempt (T-SQL / bb_bb60) */ 

INSERT INTO group_attempt_prv 
	 ([pk1] ,[gradebook_main_pk1],[course_content_group_pk1],[users_pk1],[score],
	  [grade],[student_comments],[instructor_comments],
	  [comment_public_ind], [instructor_notes], [status], [attempt_date],
	  [date_added],[date_modified],[student_submission],[text_format_type],
	  [feedback_format_type],[notes_format_type],[groups_pk1],[group_name],
	  [first_graded_date],[last_graded_date],[graded_anonymously_ind],
	  [reconciliation_mode],[show_staged_feedback])
	SELECT 
		 [pk1] ,[gradebook_main_pk1],[course_content_group_pk1],[users_pk1],[score],[grade],[student_comments],[instructor_comments],
		 [comment_public_ind], [instructor_notes], [status], [attempt_date],[date_added],[date_modified],[student_submission],[text_format_type],
		 [feedback_format_type],[notes_format_type],[groups_pk1],[group_name],[first_graded_date],[last_graded_date],[graded_anonymously_ind],
		 [reconciliation_mode],[show_staged_feedback] 
	FROM group_attempt 
	where pk1 in(
		select pk1 from group_attempt where gradebook_main_pk1 in (
			select pk1 from gradebook_main where crsmain_pk1 in (
				select pk1 from course_main where course_id = @COURSEID
				)
		) and groups_pk1 is NULL);

DELETE group_attempt where pk1 in(
	select pk1 from group_attempt where gradebook_main_pk1 in (
		select pk1 from gradebook_main where crsmain_pk1 in (
			select pk1 from course_main where course_id = @COURSEID
			)
		) 
	and groups_pk1 is NULL);