/* This script will: Populate the Unit table with a Unit called "IY Unit" Populate the Assessment table with an assessment called "IY Assessment 1" - within this "IY Unit" Assign the Unit and Assessment to each Student Group in 19/20. Compatible With ProMonitor Version 8-19. To run this script, open it in a query window in SQL Management Studio, make any changes necessary and press [!Execute] button Ensure that the ProMonitor / ProMonitorTraining Database is selected. *****YOU WILL NEED TO ADJUST THE VARIABLES AT THE TOP OF THE SCRIPT TO SUIT****** *****ALWAYS TEST ON ProMonitorTraining / Test Database before applying to a LIVE database****** *****USE THIS SCRIPT AT YOUR OWN RISK***** If you wish to add more assessments in, simply change the @AssessmentCode, @Title, @DateSet and @DateDue variables to suit. - note that the Academic Year is 19/20 in this example... - the script is re-runnable - note the WHERE NOT EXISTS for each INSERT statement... From within ProMonitor Markbook, Tutors can enter InYear Grades for students. */ DECLARE @UnitCode AS VARCHAR(12) DECLARE @UnitTitle AS VARCHAR(100) DECLARE @AssessmentCode AS VARCHAR(8) DECLARE @AssessmentTitle AS VARCHAR(100) DECLARE @AcademicYear AS VARCHAR(5) DECLARE @AssessmentDateSet AS DATETIME DECLARE @AssessmentDateDue AS DATETIME SET @UnitCode = 'IY' SET @UnitTitle = 'IY Unit' SET @AssessmentCode = 'IY1' --FOR IY2, CHANGE TO 'IY2' SET @AssessmentTitle = 'IY Assessment 1' --FOR IY2, CHANGE TO 'IY Assessment 2' SET @AcademicYear = '19/20' SET @AssessmentDateSet = '20191125' --FOR IY2, CHANGE TO e.g.'20200106' SET @AssessmentDateDue = '20191129' --FOR IY2, CHANGE TO e.g.'2020010' --INSERT Unit into Unit table for all Courses that are used by StudentGroups in AcYr INSERT Unit ( CourseID , ExternalUnitCode , UnitCode , UnitTitle , DoNotUse , Weighting , ShowOnRptToParent , UnitLevelID , AuditDateCreated , AuditCreatedBy , LearningOutcomes ) SELECT DISTINCT Course.CourseID , @UnitCode AS ExternalUnitCode , @UnitCode AS UnitCode , @UnitTitle AS UnitTitle , 0 AS DoNotUse , 0 AS Weighting , --This will set the unit to be zero weighted 0 AS ShowOnRptToParent , ( SELECT UnitLevelID FROM UnitLevel WHERE UnitLevel.LevelCode = '3' ) AS UnitLevelID , GETDATE() AS AuditDateCreated , 'administrator' AS AuditCreatedBy , NULL AS LearningOutcomes FROM Course INNER JOIN StudentGroup ON Course.CourseID = StudentGroup.CourseID AND StudentGroup.AcademicYearID = @AcademicYear WHERE NOT EXISTS ( SELECT U.UnitID FROM Unit U WHERE U.UnitCode = @UnitCode AND U.CourseID = Course.CourseID ) --INSERT Assessment against each Unit we have just created INSERT Assessment ( UnitID , AssessmentCode , AssessmentTitle , AssessmentTypeID , DoNotUse , OutOfPoints , Weighting , AuditDateCreated , AuditCreatedBy ) SELECT DISTINCT Unit.UnitID , @AssessmentCode AS AssessmentCode , @AssessmentTitle AS AssessmentTitle , ( SELECT AssessmentTypeID FROM AssessmentType WHERE AssessmentType.AssessmentTypeDescription = 'Assignment' ) AS AssessmentTypeID , 0 AS DoNotUse , 100 AS OutOfPoints , 0 AS Weighting , --This will set the assessment to be zero weighted GETDATE() AS AuditDateCreated , 'administrator' AS AuditCreatedBy FROM Unit WHERE Unit.UnitCode = @UnitCode AND NOT EXISTS ( SELECT A.AssessmentID FROM Assessment A WHERE A.AssessmentCode = @AssessmentCode AND A.UnitID = Unit.UnitID ) --Allocate the Unit to each StudentGroup (of the type in the academic year) INSERT StudentGroupUnit ( StudentGroupID , UnitID , AuditDateCreated , AuditCreatedBy ) SELECT StudentGroup.StudentGroupID , Unit.UnitID , GETDATE() AS AuditDateCreated , 'administrator' AS AuditCreatedBy FROM StudentGroup INNER JOIN Unit ON StudentGroup.CourseID = Unit.CourseID AND StudentGroup.AcademicYearID = @AcademicYear AND Unit.UnitCode = @UnitCode WHERE NOT EXISTS ( SELECT SGU.StudentGroupUnitID FROM StudentGroupUnit SGU WHERE SGU.StudentGroupID = StudentGroup.StudentGroupID AND SGU.UnitID = Unit.UnitID ) --Allocate the Assessment to each StudentGroup (of the type in the academic year) INSERT StudentGroupAssessment ( StudentGroupID , AssessmentID , StaffID , DateSet , DateExpected , AuditDateCreated , AuditCreatedBy ) SELECT StudentGroup.StudentGroupID , Assessment.AssessmentID , NULL AS StaffID , @AssessmentDateSet AS DateSet ,--Set this to the date the Assess is set. @AssessmentDateDue AS DateDue ,--Set this to the date the Assess is due. GETDATE() AS AuditDateCreated , 'administrator' AS AuditCreatedBy FROM StudentGroup INNER JOIN Unit ON StudentGroup.CourseID = Unit.CourseID AND StudentGroup.AcademicYearID = @AcademicYear AND Unit.UnitCode = @UnitCode INNER JOIN Assessment ON Assessment.UnitID = Unit.UnitID AND Assessment.AssessmentCode = @AssessmentCode WHERE NOT EXISTS ( SELECT SGA.StudentGroupassessmentID FROM StudentGroupAssessment SGA WHERE SGA.StudentGroupID = StudentGroup.StudentGroupID AND SGA.AssessmentID = Assessment.AssessmentID )