Skip to content

Instantly share code, notes, and snippets.

@othtim

othtim/mCompareNonOwnershipFields.sql

Created March 31, 2014 23:09
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save othtim/9904415 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save othtim/9904415 to your computer and use it in GitHub Desktop.
Download ZIP
post-migration database compare - needs to be redone from scratch
Raw
mCompareNonOwnershipFields.sql
use eastcentral
SET NOCOUNT ON
GO
--maybe create a generic stored procedure later to reorder
--fix the Marks section to join to markshistory if iMarksHistoryID is a valid id. right now im just comparing to see if they both exist, not to see if they contain the same info.
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID ( 'dbo.CompareLists', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.CompareLists;
GO
CREATE PROCEDURE dbo.CompareLists
@listPRE varchar(max),
@listPOST varchar(max),
@tmpGovNumber varchar(100),
@Area varchar(100) = ''
AS
-- some assumptions this procedure makes:
-- iIndex is a unique identifier that must exist for each column in the table. it will be used to sort and determine which table should be compared to which.
SET NOCOUNT ON
declare @v_Derp int -- used to loop through records in cDerp
declare @v_DerpColumns varchar(100) -- used to loop through columns in cDerpColumns
declare @commandString varchar(max)
set @commandString = '
declare cDerp cursor for
select iIndex from ' + @listPRE + '
order by iIndex'
exec(@commandstring)
open cDerp
fetch cDerp into @v_Derp
while @@FETCH_STATUS = 0
begin
--clean out the temp table
IF OBJECT_ID ( N'tmpDerp', 'U' ) IS NOT NULL
DROP table tmpDerp
BEGIN --Column processing
--grab the current record from both 'pre' and 'post' lists
set @commandString ='
select * INTO tmpDerp FROM (
select *
from [' + @listPRE + ']
where iIndex = (select MAX(iIndex) from [' + @listPRE + '])
UNION ALL
select *
from [' + @listPOST + ']
where iIndex = (select MAX(iIndex) from [' + @listPOST + '])
) un '
exec(@commandstring)
declare cDerpColumns cursor for
select column_name from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME like 'tmpDerp%'
and COLUMN_NAME not in ('iDB', 'iIndex', 'iIdentity')
---fields below i am specifically excluding due to known issues with 4.2.98 migrationtool build
AND COLUMN_NAME not in ('iLV_LanguageID', 'iLV_CompletionStatusID', 'i2_FieldsID', 'i3_FieldsID' )
open cDerpColumns
fetch cDerpColumns into @v_DerpColumns
while @@FETCH_STATUS = 0
begin
set @commandString ='
if (select MIN(CAST([' + @v_DerpColumns + '] as varchar)) from tmpDerp) != (select MAX(cast([' + @v_DerpColumns + '] as varchar)) from tmpDerp)
select
rtrim(iIndex) as ''iIndex'',
rtrim(cgovernmentnumber) as ''cgovernmentnumber'',
rtrim(clastname) as ''clastname'',
rtrim(cfirstname) as ''cfirstname'',
rtrim([' + @v_DerpColumns + ']) as ''' + @v_DerpColumns + ''',
rtrim(ischoolid) as ''ischoolid'',
rtrim(iDB) as ''db'',
''' + @Area + ''' as ''area'',
*
from tmpDerp'
exec(@commandString)
fetch next from cDerpColumns into @v_DerpColumns
end
close cDerpColumns
deallocate cDerpColumns
END
set @commandString = '
delete from [' + @listPRE + ']
where iIndex = (select MAX(iIndex) from [' + @listPRE + '])
delete from [' + @listPOST + ']
where iIndex = (select MAX(iIndex) from [' + @listPOST + '])'
exec(@commandstring)
fetch next from cDerp into @v_Derp
end
close cDerp
deallocate cDerp
--exec(@commandString)
GO
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
--general
declare @v_MasterDBName varchar(100) --upsized sql db
declare @v_OwnershipDBName varchar(100) --ownership file converted to a db
declare @v_govnumber varchar(20) --used to loop through students
declare @v_columns varchar(100) --used to loop through the columns for averaging
declare @commandString varchar(max) --string for building sql commands
declare @v_currentChildDBName varchar(100) --used to loop through schools
declare @iIndex int; --used to order records
---history
declare @v_StudentHistory int --used to look through history records
declare @v_StudentHistoryIndex int --used to store indexes for renumbering
declare @v_StudentHistoryCounter int --incremental counter var
declare @v_StudentHistoryColumns varchar(100) --used to store names to compare individual history columns
drop table tmpPREStudentHistory
drop table tmpPOSTStudentHistory
create table tmpPREStudentHistory(
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
clastname varchar(100),
cfirstname varchar(100),
cgovernmentnumber varchar(100),
cStudentNumber varchar(100),
nSchoolYear varchar(50),
iSchoolid varchar(50),
iGradesid varchar(50),
nAbsences varchar(50),
nBlocks varchar(50),
nAverage varchar(50),
nCourseAverage varchar(50),
nCreditAverage varchar(50),
nCreditEarnedAverage varchar(50),
nCourseBonusAverage varchar(50),
nStraightGPA varchar(50),
nCreditGPA varchar(50),
nCreditEarnedGPA varchar(50),
nCourseGPA varchar(50),
nCourseBonusGPA varchar(50),
nRank varchar(50),
nClassSize varchar(50),
CONSTRAINT pk_tmpPREStudentHistory PRIMARY KEY (cGovernmentNumber, iSchoolid, nSchoolYear) --this constraint matches dbo.History in the db
)
create table tmpPOSTStudentHistory(
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
clastname varchar(100),
cfirstname varchar(100),
cgovernmentnumber varchar(100),
cStudentNumber varchar(100),
nSchoolYear varchar(50),
iSchoolid varchar(50),
iGradesid varchar(50),
nAbsences varchar(50),
nBlocks varchar(50),
nAverage varchar(50),
nCourseAverage varchar(50),
nCreditAverage varchar(50),
nCreditEarnedAverage varchar(50),
nCourseBonusAverage varchar(50),
nStraightGPA varchar(50),
nCreditGPA varchar(50),
nCreditEarnedGPA varchar(50),
nCourseGPA varchar(50),
nCourseBonusGPA varchar(50),
nRank varchar(50),
nClassSize varchar(50),
CONSTRAINT pk_tmpPOSTStudentHistory PRIMARY KEY (cGovernmentNumber, iSchoolid, nSchoolYear)
)
-----------------------------------------------------------------------------------------------------------------------------------------------
--markshistory
declare @v_MarksHistoryIndex int
declare @v_MarksHistoryCounter int
drop table tmpPREMarksHistory
drop table tmpPOSTMarksHistory
create table tmpPREMarksHistory (
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
clastname varchar(50),
cfirstname varchar(50),
cgovernmentnumber varchar(50),
iCourseID varchar(50),
nFinalMark varchar(20),
nSchoolMark varchar(20),
nExamMark varchar(20),
nCreditPossible varchar(20),
nCreditEarned varchar(20),
iLV_CompletionStatusID varchar(50),
nAbsences varchar(20),
nLates varchar(20),
nSchool varchar(50),
dModified varchar(50),
nYear varchar(10),
iGradesID varchar(20),
iLV_SessionID varchar(20),
iSchoolID varchar(50),
dExamDate varchar(50),
lWroteExam varchar(5),
iLV_LanguageID varchar(50),
dStartDate varchar(50),
dEndDate varchar(50),
iTotalPeriods int,
cDepartmentExamCode varchar(20),
cActionCode varchar(20),
dActionDate varchar(50),
lReported varchar(5),
cTeacher varchar(50),
cCourseCode varchar(20),
cCourseDesc varchar(50),
cAlphaMark varchar(20),
cLevel varchar(20),
i1_FieldsID varchar(50),
i2_FieldsID varchar(50),
i3_FieldsID varchar(50),
i4_FieldsID varchar(50),
i5_FieldsID varchar(50),
i6_FieldsID varchar(50),
i7_FieldsID varchar(50),
i8_FieldsID varchar(50),
i9_FieldsID varchar(50),
nGPA1 varchar(20),
nGPA2 varchar(20),
nGPA3 varchar(20),
lExcludeFromAverage varchar(5),
cTerm varchar(50),
iAchievement_GradesID varchar(20),
nCollegeCreditsEarned varchar(5),
i10_FieldsID varchar(50),
i11_FieldsID varchar(50),
i12_FieldsID varchar(50)
)
--POST
create table tmpPOSTMarksHistory (
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
clastname varchar(50),
cfirstname varchar(50),
cgovernmentnumber varchar(50),
iCourseID varchar(50),
nFinalMark varchar(20),
nSchoolMark varchar(20),
nExamMark varchar(20),
nCreditPossible varchar(20),
nCreditEarned varchar(20),
iLV_CompletionStatusID varchar(50),
nAbsences varchar(20),
nLates varchar(20),
nSchool varchar(50),
dModified varchar(50),
nYear varchar(10),
iGradesID varchar(20),
iLV_SessionID varchar(20),
iSchoolID varchar(50),
dExamDate varchar(50),
lWroteExam varchar(5),
iLV_LanguageID varchar(50),
dStartDate varchar(50),
dEndDate varchar(50),
iTotalPeriods int,
cDepartmentExamCode varchar(20),
cActionCode varchar(20),
dActionDate varchar(50),
lReported varchar(5),
cTeacher varchar(50),
cCourseCode varchar(20),
cCourseDesc varchar(50),
cAlphaMark varchar(20),
cLevel varchar(20),
i1_FieldsID varchar(50),
i2_FieldsID varchar(50),
i3_FieldsID varchar(50),
i4_FieldsID varchar(50),
i5_FieldsID varchar(50),
i6_FieldsID varchar(50),
i7_FieldsID varchar(50),
i8_FieldsID varchar(50),
i9_FieldsID varchar(50),
nGPA1 varchar(20),
nGPA2 varchar(20),
nGPA3 varchar(20),
lExcludeFromAverage varchar(5),
cTerm varchar(50),
iAchievement_GradesID varchar(20),
nCollegeCreditsEarned varchar(5),
i10_FieldsID varchar(50),
i11_FieldsID varchar(50),
i12_FieldsID varchar(50)
)
-----------------------------------------------------------------------------------------------------------------------------------------------
--enrollment
declare @v_EnrollmentIndex int
declare @v_EnrollmentCounter int
drop table tmpPREStudentEnrollment
drop table tmpPOSTStudentEnrollment
create table tmpPREStudentEnrollment(
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
clastname varchar(50),
cfirstname varchar(50),
cgovernmentnumber varchar(50),
iClassID varchar(50),
dInDate smalldatetime,
dOutDate smalldatetime,
iLV_CompletionStatusID varchar(50),
ischoolid varchar(50),
iLastLetterLevel int,
iRelated_CourseID varchar(50),
cLevel int,
iLV_WritingCenterID varchar(50),
iLV_UserField1ID varchar(50),
iLV_UserField2ID varchar(50),
iLV_UserField3ID varchar(50),
iLV_UserField4ID varchar(50),
iLV_UserField5ID varchar(50),
iLV_UserField6ID varchar(50),
cEndofYearMark varchar(50),
nEndofYearMark varchar(50),
cRegentMark varchar(50),
nRegentMark varchar(50),
iMarksHistoryID varchar(50),
lSkSubmitted_Out varchar(50),
iLV_UserField7ID varchar(50),
iLV_UserField8ID varchar(50),
iLV_UserField9ID varchar(50),
iLV_UserField10ID varchar(50),
iLV_UserField11ID varchar(50),
iLV_UserField12ID varchar(50),
iLV_UserField13ID varchar(50),
nPostSecondaryCredits varchar(50)
)
create table tmpPOSTStudentEnrollment(
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
clastname varchar(50),
cfirstname varchar(50),
cgovernmentnumber varchar(50),
iClassID varchar(50),
dInDate smalldatetime,
dOutDate smalldatetime,
iLV_CompletionStatusID varchar(50),
ischoolid varchar(50),
iLastLetterLevel int,
iRelated_CourseID varchar(50),
cLevel int,
iLV_WritingCenterID varchar(50),
iLV_UserField1ID varchar(50),
iLV_UserField2ID varchar(50),
iLV_UserField3ID varchar(50),
iLV_UserField4ID varchar(50),
iLV_UserField5ID varchar(50),
iLV_UserField6ID varchar(50),
cEndofYearMark varchar(50),
nEndofYearMark varchar(50),
cRegentMark varchar(50),
nRegentMark varchar(50),
iMarksHistoryID varchar(50),
lSkSubmitted_Out varchar(50),
iLV_UserField7ID varchar(50),
iLV_UserField8ID varchar(50),
iLV_UserField9ID varchar(50),
iLV_UserField10ID varchar(50),
iLV_UserField11ID varchar(50),
iLV_UserField12ID varchar(50),
iLV_UserField13ID varchar(50),
nPostSecondaryCredits varchar(50)
)
-----------------------------------------------------------------------------------------------------------------------------------------------
--marks
declare @v_MarksIndex int
declare @v_MarksCounter int
drop table tmpPREMarks
drop table tmpPOSTMarks
create table tmpPREMarks (
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
clastname varchar(50),
cfirstname varchar(50),
cgovernmentnumber varchar(50),
iReportPeriodID varchar(20),
iClassID varchar(50),
nMark varchar(10),
cMark varchar(50),
dDateAssigned varchar(20),
iEffortLegendDetailsID varchar(20),
iMarksHistoryID varchar(20),
iSchoolID varchar(50),
nCredit varchar(10),
nExamMark varchar(10),
cExamMark varchar(10),
nFinalMark varchar(10),
iMarkCommentID varchar(20), --maybe needs to be a lot longer?
mComment varchar(1000),
iAchievement_GradesID varchar(20),
nregentmark varchar(20),
iLV_AchievementID varchar(20),
iLV_FinalAchievementID varchar(20),
mAltLang_Comment varchar(20)
)
create table tmpPOSTMarks (
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
clastname varchar(50),
cfirstname varchar(50),
cgovernmentnumber varchar(50),
iReportPeriodID varchar(20),
iClassID varchar(50),
nMark varchar(10),
cMark varchar(10),
dDateAssigned varchar(20),
iEffortLegendDetailsID varchar(20),
iMarksHistoryID varchar(20),
iSchoolID varchar(50),
nCredit varchar(10),
nExamMark varchar(10),
cExamMark varchar(10),
nFinalMark varchar(10),
iMarkCommentID varchar(20), --maybe needs to be a lot longer?
mComment varchar(1000),
iAchievement_GradesID varchar(20),
nregentmark varchar(20),
iLV_AchievementID varchar(20),
iLV_FinalAchievementID varchar(20),
mAltLang_Comment varchar(20)
)
-----------------------------------------------------------------------------------------------------------------------------------------------
--attendance
declare @v_AttendanceIndex int
declare @v_AttendanceCounter int
--attendancehistory
declare @v_AttendanceHistoryIndex int
declare @v_AttendanceHistoryCounter int
drop table tmpPREAttendHistory
drop table tmpPOSTAttendHistory
create table tmpPREAttendHistory (
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
clastname varchar(50),
cfirstname varchar(50),
cgovernmentnumber varchar(50),
iAttendanceID varchar(50),
cBlockInformation varchar(50),
iStudentID varchar(50),
cAttendanceStatus varchar(50),
cAttendanceReason varchar(50),
dDate varchar(50),
cClassName varchar(50),
lExcusable varchar(50),
iSchoolID varchar(50),
mComment varchar(max)
)
create table tmpPOSTAttendHistory (
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
clastname varchar(50),
cfirstname varchar(50),
cgovernmentnumber varchar(50),
iAttendanceID varchar(50),
cBlockInformation varchar(50),
iStudentID varchar(50),
cAttendanceStatus varchar(50),
cAttendanceReason varchar(50),
dDate varchar(50),
cClassName varchar(50),
lExcusable varchar(50),
iSchoolID varchar(50),
mComment varchar(max)
)
-----------------------------------------------------------------------------------------------------------------------------------------------
set @v_MasterDBName = 'stalbert'
--make a table var with all the db's we are going to search
declare @v_allDBs table ( tableName varchar(100) )
insert into @v_allDBs (tableName) VALUES ('simpson') , ('bellrose'), ('paulkane'), ('stalbert2')
--insert into @v_allDBs (tableName) VALUES (''), ('3870') , ('3970'), ('4970'), ('eastcentral2')
--begin the non-ownership loop
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
--the is the main loop for looking through students
declare cStudent cursor for
select distinct cGovernmentNumber from Student
join studentfee on studentfee.istudentid = student.istudentid
join studentpaydetails on studentpaydetails.iStudentFeeID = studentfee.iStudentFeeID
where cGovernmentNumber != ''
and studentpaydetails.nAmount != ''
order by student.cGovernmentNumber DESC
--select [Column 7] from Ownership.dbo.StudentOwner
--where [Column 7] != ''
----and [Column 7] = '940920788'
--order by [Column 7] DESC
open cStudent
fetch cStudent into @v_govnumber
while @@FETCH_STATUS = 0
begin
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
---STUDENT------------------------------------------------------------
---------HISTORY------------------------------------------------------
----------------------------------------------------------------------
-- truncate table tmpPREStudentHistory
-- truncate table tmpPOSTStudentHistory
-- declare cSchool cursor for
-- select tableName from @v_allDBs
-- order by tableName -- school code ordered lowest to highest
-- open cSchool
-- fetch cSchool into @v_currentChildDBName
-- while @@FETCH_STATUS = 0
-- begin
-- --drop records associated with the school we are processing
-- --delete from tmpPREStudentHistory where iSchoolid like
-- --(select cname from School where cCode like @v_currentChildDBName) COLLATE DATABASE_DEFAULT
-- --switched ischoolid from --> isnull([' + @v_currentChildDBName + '].dbo.getSchoolName(h.iSchoolID),0) as ''iSchoolID'',
-- set @commandString =
-- 'insert into tmpPREStudentHistory (
-- iDB,
-- clastname,
-- cfirstname,
-- cgovernmentnumber,
-- cStudentNumber,
-- nSchoolYear,
-- iSchoolid,
-- iGradesid,
-- nAbsences,
-- nBlocks,
-- nAverage,
-- nCourseAverage,
-- nCreditAverage,
-- nCreditEarnedAverage,
-- nCourseBonusAverage,
-- nStraightGPA,
-- nCreditGPA,
-- nCreditEarnedGPA,
-- nCourseGPA,
-- nCourseBonusGPA,
-- nRank,
-- nClassSize )
-- select
-- ''pre'',
-- s.clastname,
-- s.cfirstname,
-- s.cgovernmentnumber,
-- h.cStudentNumber,
-- nSchoolYear,
-- isnull([' + @v_currentChildDBName + '].dbo.GetSchoolName(h.ischoolid),0) as ''iSchoolID'',
-- isnull([' + @v_currentChildDBName + '].dbo.getGradesName(h.iGradesID),0) as ''iGradesID'',
-- nAbsences,
-- nBlocks,
-- nAverage,
-- nCourseAverage,
-- nCreditAverage,
-- nCreditEarnedAverage,
-- nCourseBonusAverage,
-- nStraightGPA,
-- nCreditGPA,
-- nCreditEarnedGPA,
-- nCourseGPA,
-- nCourseBonusGPA,
-- nRank,
-- nClassSize
-- from [' + @v_currentChildDBName + '].dbo.History h
-- join [' + @v_currentChildDBName + '].dbo.Student s
-- on s.iStudentID = h.iStudentID
-- where s.cGovernmentNumber like ''' + @v_govnumber + ''''
-- exec(@CommandString)
-- fetch next from cSchool into @v_currentChildDBName
-- end
-- close cSchool
-- deallocate cSchool
-- ----initialize counter
-- --set @v_StudentHistoryCounter = 1
-- ----loop through all the records in tmpPREStudentHistory. sorted by nSchoolYear and School Name, smallest to largest
-- --declare cHistoryRenumber cursor for
-- -- select iIdentity from tmpPREStudentHistory
-- -- order by nSchoolYear, iSchoolid ASC
-- --open cHistoryRenumber
-- --fetch cHistoryRenumber into @v_StudentHistoryIndex
-- --while @@FETCH_STATUS = 0
-- --begin
-- -- --set the iIndex in the related History table to be the index of this counter
-- -- update tmpPREStudentHistory
-- -- set iIndex = @v_StudentHistoryCounter
-- -- where iIdentity = @v_StudentHistoryIndex
-- -- set @v_StudentHistoryCounter = @v_StudentHistoryCounter + 1
-- -- fetch next from cHistoryRenumber into @v_StudentHistoryIndex
-- --end
-- --close cHistoryRenumber
-- --deallocate cHistoryRenumber
-- -------
-- set @commandString =
-- 'insert into tmpPOSTStudentHistory (
-- iDB,
-- clastname,
-- cfirstname,
-- cgovernmentnumber,
-- cStudentNumber,
-- nSchoolYear,
-- iSchoolid,
-- iGradesid,
-- nAbsences,
-- nBlocks,
-- nAverage,
-- nCourseAverage,
-- nCreditAverage,
-- nCreditEarnedAverage,
-- nCourseBonusAverage,
-- nStraightGPA,
-- nCreditGPA,
-- nCreditEarnedGPA,
-- nCourseGPA,
-- nCourseBonusGPA,
-- nRank,
-- nClassSize )
-- select
-- ''post'',
-- s.clastname,
-- s.cfirstname,
-- s.cgovernmentnumber,
-- h.cStudentNumber,
-- nSchoolYear,
-- isnull(dbo.GetSchoolName(h.ischoolid),0) as ''iSchoolID'',
-- isnull(dbo.getGradesName(h.iGradesID),0) as ''iGradesID'',
-- nAbsences,
-- nBlocks,
-- nAverage,
-- nCourseAverage,
-- nCreditAverage,
-- nCreditEarnedAverage,
-- nCourseBonusAverage,
-- nStraightGPA,
-- nCreditGPA,
-- nCreditEarnedGPA,
-- nCourseGPA,
-- nCourseBonusGPA,
-- nRank,
-- nClassSize
-- from [' + @v_MasterDBName + '].dbo.History h
-- join [' + @v_MasterDBName + '].dbo.Student s
-- on s.iStudentID = h.iStudentID
-- where s.cGovernmentNumber like ''' + @v_govnumber + '''
-- order by nSchoolYear, iSchoolid ASC'
-- exec(@CommandString)
-- update tmpPOSTStudentHistory set iIndex = iIdentity
-- update tmpPREStudentHistory
-- set iIndex = RowNumber
-- from (
-- select
-- iIndex,
-- iIdentity,
-- Row_Number() over (Partition by cgovernmentnumber order by nSchoolYear, iSchoolid ASC) as 'RowNumber'
-- from tmpPREStudentHistory) RN
-- where tmpPREStudentHistory.iIdentity = RN.iIdentity
-- --select * from tmpPREStudentHistory
-- --select * from tmpPOSTStudentHistory
-- --at this point all the records from the various seperate db's are loaded into the table. now compare.
-- if( (select COUNT(*) from tmpPOSTStudentHistory) != 0 )
-- if( (select COUNT(*) from tmpPREStudentHistory) = (select COUNT(*) from tmpPOSTStudentHistory) )
-- begin
-- --select * from tmpPOSTStudentHistory
-- EXECUTE dbo.CompareLists N'tmpPREStudentHistory', N'tmpPOSTStudentHistory', @v_govnumber, 'Student History'
-- end
-- else
-- begin
-- select 'History record counts dont match'
-- select * from tmpPREStudentHistory order by nSchoolYear, iSchoolid
-- select * from tmpPOSTStudentHistory order by nSchoolYear, ischoolid
-- end
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ---STUDENT------------------------------------------------------------
-- ---------ENROLLMENT---------------------------------------------------
-- ----------------------------------------------------------------------
-- truncate table tmpPREStudentEnrollment
-- truncate table tmpPOSTStudentEnrollment
-- declare cSchool cursor for
-- select tableName from @v_allDBs
-- order by tableName -- school code ordered lowest to highest
-- open cSchool
-- fetch cSchool into @v_currentChildDBName
-- while @@FETCH_STATUS = 0
-- begin
-- set @commandString = '
-- insert into tmpPREStudentEnrollment (
-- iDb,
-- clastname,
-- cfirstname,
-- cgovernmentnumber,
-- iClassID,
-- dInDate,
-- dOutDate,
-- iLV_CompletionStatusID,
-- ischoolid,
-- iLastLetterLevel,
-- iRelated_CourseID,
-- cLevel,
-- iLV_WritingCenterID,
-- iLV_UserField1ID,
-- iLV_UserField2ID,
-- iLV_UserField3ID,
-- iLV_UserField4ID,
-- iLV_UserField5ID,
-- iLV_UserField6ID,
-- cEndofYearMark,
-- nEndofYearMark,
-- cRegentMark,
-- nRegentMark,
-- iMarksHistoryID,
-- lSkSubmitted_Out,
-- iLV_UserField7ID,
-- iLV_UserField8ID,
-- iLV_UserField9ID,
-- iLV_UserField10ID,
-- iLV_UserField11ID,
-- iLV_UserField12ID,
-- iLV_UserField13ID,
-- nPostSecondaryCredits )
-- select
-- ''pre'',
-- s.clastname,
-- s.cfirstname,
-- s.cgovernmentnumber,
-- isnull([' + @v_currentChildDBName + '].dbo.GetClassNameSection(e.iClassID),0),
-- dInDate,
-- dOutDate,
-- isnull([' + @v_currentChildDBName + '].dbo.getlookupvalue(e.iLV_CompletionStatusID),0),
-- isnull([' + @v_currentChildDBName + '].dbo.getSchoolName(e.ischoolid),0),
-- iLastLetterLevel,
-- iRelated_CourseID,
-- cLevel,
-- iLV_WritingCenterID,
-- iLV_UserField1ID,
-- iLV_UserField2ID,
-- iLV_UserField3ID,
-- iLV_UserField4ID,
-- iLV_UserField5ID,
-- iLV_UserField6ID,
-- cEndofYearMark,
-- nEndofYearMark,
-- cRegentMark,
-- nRegentMark,
-- iMarksHistoryID,
-- lSkSubmitted_Out,
-- iLV_UserField7ID,
-- iLV_UserField8ID,
-- iLV_UserField9ID,
-- iLV_UserField10ID,
-- iLV_UserField11ID,
-- iLV_UserField12ID,
-- iLV_UserField13ID,
-- nPostSecondaryCredits
-- from [' + @v_currentChildDBName + '].dbo.Enrollment e
-- join [' + @v_currentChildDBName + '].dbo.student s
-- on s.iStudentID = e.iStudentID
-- where s.cGovernmentNumber = ''' + @v_govnumber + ''''
-- exec(@commandString)
-- fetch next from cSchool into @v_currentChildDBName
-- end
-- close cSchool
-- deallocate cSchool
-- --debug to show kids with enrollment records in more than one school
-- --if( (select min(ischoolid) from tmpPREStudentEnrollment) != (select MAX(ischoolid) from tmpPREStudentEnrollment))
-- -- select * from tmpPREStudentEnrollment
-- --------reorder PRE
-- --set @v_EnrollmentCounter = 1
-- --declare cEnrollmentRenumber cursor for
-- -- select iIdentity from tmpPREStudentEnrollment
-- -- order by iSchoolID, iClassID
-- --open cEnrollmentRenumber
-- --fetch cEnrollmentRenumber into @v_EnrollmentIndex
-- --while @@FETCH_STATUS = 0
-- --begin
-- -- update tmpPREStudentEnrollment
-- -- set iIndex = @v_EnrollmentCounter
-- -- where iIdentity = @v_EnrollmentIndex
-- -- set @v_EnrollmentCounter = @v_EnrollmentCounter + 1
-- -- fetch next from cEnrollmentRenumber into @v_EnrollmentIndex
-- --end
-- --close cEnrollmentRenumber
-- --deallocate cEnrollmentRenumber
-- -----------
-- set @commandString = '
-- insert into tmpPOSTStudentEnrollment (
-- iDB,
-- clastname,
-- cfirstname,
-- cgovernmentnumber,
-- iClassID,
-- dInDate,
-- dOutDate,
-- iLV_CompletionStatusID,
-- ischoolid,
-- iLastLetterLevel,
-- iRelated_CourseID,
-- cLevel,
-- iLV_WritingCenterID,
-- iLV_UserField1ID,
-- iLV_UserField2ID,
-- iLV_UserField3ID,
-- iLV_UserField4ID,
-- iLV_UserField5ID,
-- iLV_UserField6ID,
-- cEndofYearMark,
-- nEndofYearMark,
-- cRegentMark,
-- nRegentMark,
-- iMarksHistoryID,
-- lSkSubmitted_Out,
-- iLV_UserField7ID,
-- iLV_UserField8ID,
-- iLV_UserField9ID,
-- iLV_UserField10ID,
-- iLV_UserField11ID,
-- iLV_UserField12ID,
-- iLV_UserField13ID,
-- nPostSecondaryCredits )
-- select
-- ''post'',
-- s.clastname,
-- s.cfirstname,
-- s.cgovernmentnumber,
-- isnull([' + @v_MasterDBName + '].dbo.GetClassNameSection(e.iClassID),0) as ''iClassID'',
-- dInDate,
-- dOutDate,
-- isnull([' + @v_MasterDBName + '].dbo.getlookupvalue(e.iLV_CompletionStatusID),0) as ''iLV_CompletionStatusID'',
-- isnull([' + @v_MasterDBName + '].dbo.getSchoolName(e.ischoolid),0) as ''iSchoolid'',
-- iLastLetterLevel,
-- iRelated_CourseID,
-- cLevel,
-- iLV_WritingCenterID,
-- iLV_UserField1ID,
-- iLV_UserField2ID,
-- iLV_UserField3ID,
-- iLV_UserField4ID,
-- iLV_UserField5ID,
-- iLV_UserField6ID,
-- cEndofYearMark,
-- nEndofYearMark,
-- cRegentMark,
-- nRegentMark,
-- iMarksHistoryID,
-- lSkSubmitted_Out,
-- iLV_UserField7ID,
-- iLV_UserField8ID,
-- iLV_UserField9ID,
-- iLV_UserField10ID,
-- iLV_UserField11ID,
-- iLV_UserField12ID,
-- iLV_UserField13ID,
-- nPostSecondaryCredits
-- from [' + @v_MasterDBName + '].dbo.Enrollment e
-- join [' + @v_MasterDBName + '].dbo.student s
-- on s.iStudentID = e.iStudentID
-- where s.cGovernmentNumber = ''' + @v_govnumber + '''
-- order by iSchoolID, iClassID '
-- exec(@commandString)
-- update tmpPOSTStudentEnrollment set iIndex = iIdentity
-- update tmpPREStudentEnrollment
-- set iIndex = RowNumber
-- from (
-- select
-- iIndex,
-- iIdentity,
-- Row_Number() over (Partition by cgovernmentnumber order by iSchoolID, iClassID ASC) as 'RowNumber'
-- from tmpPREStudentEnrollment) RN
-- where tmpPREStudentEnrollment.iIdentity = RN.iIdentity
-- --at this point all the records from the various seperate db's are loaded into the table. now compare.
-- if( (select COUNT(*) from tmpPOSTStudentEnrollment) != 0 )
-- if( (select COUNT(*) from tmpPREStudentEnrollment) = (select COUNT(*) from tmpPOSTStudentEnrollment) )
-- begin
-- --select * from tmpPOSTStudentHistory
-- EXECUTE dbo.CompareLists N'tmpPREStudentEnrollment', N'tmpPOSTStudentEnrollment', @v_govnumber, 'Student Enrollment'
-- end
-- else
-- begin
-- select 'Counts dont match for student: ' + @v_govnumber
-- select * from tmpPREStudentEnrollment
-- select * from tmpPOSTStudentEnrollment
-- end
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ---STUDENT------------------------------------------------------------
-- ---------MARKSHISTORY-------------------------------------------------
-- ----------------------------------------------------------------------
-- truncate table tmpPREMarksHistory
-- truncate table tmpPOSTMarksHistory
-- declare cSchool cursor for
-- select tableName from @v_allDBs
-- order by tableName -- school code ordered lowest to highest
-- open cSchool
-- fetch cSchool into @v_currentChildDBName
-- while @@FETCH_STATUS = 0
-- begin
-- set @commandString = '
-- insert into tmpPREMarksHistory (
-- iDB,
-- clastname,
-- cfirstname,
-- cgovernmentnumber,
-- iCourseID,
-- nFinalMark,
-- nSchoolMark,
-- nExamMark,
-- nCreditPossible,
-- nCreditEarned,
-- iLV_CompletionStatusID,
-- nAbsences,
-- nLates,
-- nSchool,
-- dModified,
-- nYear,
-- iGradesID,
-- iLV_SessionID,
-- iSchoolID,
-- dExamDate,
-- lWroteExam,
-- iLV_LanguageID,
-- dStartDate,
-- dEndDate,
-- iTotalPeriods,
-- cDepartmentExamCode,
-- cActionCode,
-- dActionDate,
-- lReported,
-- cTeacher,
-- cCourseCode,
-- cCourseDesc,
-- cAlphaMark,
-- cLevel,
-- i1_FieldsID,
-- i2_FieldsID,
-- i3_FieldsID,
-- i4_FieldsID,
-- i5_FieldsID,
-- i6_FieldsID,
-- i7_FieldsID,
-- i8_FieldsID,
-- i9_FieldsID,
-- nGPA1,
-- nGPA2,
-- nGPA3,
-- lExcludeFromAverage,
-- cTerm,
-- iAchievement_GradesID,
-- nCollegeCreditsEarned,
-- i10_FieldsID,
-- i11_FieldsID,
-- i12_FieldsID )
-- select
-- ''pre'',
-- s.clastname,
-- s.cfirstname,
-- s.cgovernmentnumber,
-- isnull([' + @v_currentChildDBName + '].dbo.GetCourseName(mh.iCourseID,0),0) as ''iCourseID'',
-- mh.nFinalMark,
-- mh.nSchoolMark,
-- mh.nExamMark,
-- mh.nCreditPossible,
-- mh.nCreditEarned,
-- isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(mh.iLV_CompletionStatusID),0) as ''iLV_CompletionStatusID'',
-- mh.nAbsences,
-- mh.nLates,
-- mh.nSchool,
-- mh.dModified,
-- mh.nYear,
-- isnull([' + @v_currentChildDBName + '].dbo.GetGradesName(mh.iGradesID),0) as ''iGradesID'',
-- isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(mh.iLV_SessionID),0) as ''mh.iLV_SessionID'',
-- isnull([' + @v_currentChildDBName + '].dbo.GetSchoolName(mh.iSchoolID),0) as ''iSchoolID'',
-- mh.dExamDate,
-- mh.lWroteExam,
-- isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(mh.iLV_LanguageID),0) as ''iLV_LanguageID'',
-- mh.dStartDate,
-- mh.dEndDate,
-- mh.iTotalPeriods,
-- mh.cDepartmentExamCode,
-- mh.cActionCode,
-- mh.dActionDate,
-- mh.lReported,
-- mh.cTeacher,
-- mh.cCourseCode,
-- mh.cCourseDesc,
-- mh.cAlphaMark,
-- mh.cLevel,
-- isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(mh.i1_FieldsID),0) as ''i1_FieldsID'',
-- isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(mh.i2_FieldsID),0) as ''i2_FieldsID'',
-- isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(mh.i3_FieldsID),0) as ''i3_FieldsID'',
-- isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(mh.i4_FieldsID),0) as ''i4_FieldsID'',
-- isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(mh.i5_FieldsID),0) as ''i5_FieldsID'',
-- isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(mh.i6_FieldsID),0) as ''i6_FieldsID'',
-- isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(mh.i7_FieldsID),0) as ''i7_FieldsID'',
-- isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(mh.i8_FieldsID),0) as ''i8_FieldsID'',
-- mh.i9_FieldsID,
-- mh.nGPA1,
-- mh.nGPA2,
-- mh.nGPA3,
-- mh.lExcludeFromAverage,
-- mh.cTerm,
-- mh.iAchievement_GradesID,
-- mh.nCollegeCreditsEarned,
-- mh.i10_FieldsID,
-- mh.i11_FieldsID,
-- mh.i12_FieldsID
-- from [' + @v_currentChildDBName + '].dbo.markshistory mh
-- join [' + @v_currentChildDBName + '].dbo.student s
-- on s.iStudentID = mh.iStudentID
-- where s.cGovernmentNumber like ''' + @v_govnumber + ''''
-- exec(@commandString)
-- fetch next from cSchool into @v_currentChildDBName
-- end
-- close cSchool
-- deallocate cSchool
-- --------reorder PRE
-- --set @v_MarksHistoryCounter = 1
-- --declare cMarksHistoryRenumber cursor for
-- -- select iIdentity from tmpPREMarksHistory
-- -- order by iSchoolID, cCourseCode, nYear, cTerm, iLV_SessionID
-- --open cMarksHistoryRenumber
-- --fetch cMarksHistoryRenumber into @v_MarksHistoryIndex
-- --while @@FETCH_STATUS = 0
-- --begin
-- -- update tmpPREMarksHistory
-- -- set iIndex = @v_MarksHistoryCounter
-- -- where iIdentity = @v_MarksHistoryIndex
-- -- set @v_MarksHistoryCounter = @v_MarksHistoryCounter + 1
-- -- fetch next from cMarksHistoryRenumber into @v_MarksHistoryIndex
-- --end
-- --close cMarksHistoryRenumber
-- --deallocate cMarksHistoryRenumber
-- -----------
-- set @commandString = '
-- insert into tmpPOSTMarksHistory (
-- iDB,
-- clastname,
-- cfirstname,
-- cgovernmentnumber,
-- iCourseID,
-- nFinalMark,
-- nSchoolMark,
-- nExamMark,
-- nCreditPossible,
-- nCreditEarned,
-- iLV_CompletionStatusID,
-- nAbsences,
-- nLates,
-- nSchool,
-- dModified,
-- nYear,
-- iGradesID,
-- iLV_SessionID,
-- iSchoolID,
-- dExamDate,
-- lWroteExam,
-- iLV_LanguageID,
-- dStartDate,
-- dEndDate,
-- iTotalPeriods,
-- cDepartmentExamCode,
-- cActionCode,
-- dActionDate,
-- lReported,
-- cTeacher,
-- cCourseCode,
-- cCourseDesc,
-- cAlphaMark,
-- cLevel,
-- i1_FieldsID,
-- i2_FieldsID,
-- i3_FieldsID,
-- i4_FieldsID,
-- i5_FieldsID,
-- i6_FieldsID,
-- i7_FieldsID,
-- i8_FieldsID,
-- i9_FieldsID,
-- nGPA1,
-- nGPA2,
-- nGPA3,
-- lExcludeFromAverage,
-- cTerm,
-- iAchievement_GradesID,
-- nCollegeCreditsEarned,
-- i10_FieldsID,
-- i11_FieldsID,
-- i12_FieldsID)
-- select
-- ''post'',
-- s.clastname,
-- s.cfirstname,
-- s.cgovernmentnumber,
-- isnull(dbo.GetCourseName(mh.iCourseID,0),0) as ''iCourseID'',
-- mh.nFinalMark,
-- mh.nSchoolMark,
-- mh.nExamMark,
-- mh.nCreditPossible,
-- mh.nCreditEarned,
-- isnull(dbo.GetLookupValue(mh.iLV_CompletionStatusID),0) as ''iLV_CompletionStatusID'',
-- mh.nAbsences,
-- mh.nLates,
-- mh.nSchool,
-- mh.dModified,
-- mh.nYear,
-- isnull(dbo.GetGradesName(mh.iGradesID),0) as ''iGradesID'',
-- isnull(dbo.GetLookupValue(mh.iLV_SessionID),0) as ''iLV_SessionID'',
-- isnull(dbo.GetSchoolName(mh.iSchoolID),0) as ''iSchoolID'',
-- mh.dExamDate,
-- mh.lWroteExam,
-- isnull(dbo.GetLookupValue(mh.iLV_LanguageID),0) as ''iLV_LanguageID'',
-- mh.dStartDate,
-- mh.dEndDate,
-- mh.iTotalPeriods,
-- mh.cDepartmentExamCode,
-- mh.cActionCode,
-- mh.dActionDate,
-- mh.lReported,
-- mh.cTeacher,
-- mh.cCourseCode,
-- mh.cCourseDesc,
-- mh.cAlphaMark,
-- mh.cLevel,
-- isnull(dbo.GetLookupValue(mh.i1_FieldsID),0) as ''i1_FieldsID'',
-- isnull(dbo.GetLookupValue(mh.i2_FieldsID),0) as ''i2_FieldsID'',
-- isnull(dbo.GetLookupValue(mh.i3_FieldsID),0) as ''i3_FieldsID'',
-- isnull(dbo.GetLookupValue(mh.i4_FieldsID),0) as ''i4_FieldsID'',
-- isnull(dbo.GetLookupValue(mh.i5_FieldsID),0) as ''i5_FieldsID'',
-- isnull(dbo.GetLookupValue(mh.i6_FieldsID),0) as ''i6_FieldsID'',
-- isnull(dbo.GetLookupValue(mh.i7_FieldsID),0) as ''i7_FieldsID'',
-- isnull(dbo.GetLookupValue(mh.i8_FieldsID),0) as ''i8_FieldsID'',
-- mh.i9_FieldsID,
-- mh.nGPA1,
-- mh.nGPA2,
-- mh.nGPA3,
-- mh.lExcludeFromAverage,
-- mh.cTerm,
-- mh.iAchievement_GradesID,
-- mh.nCollegeCreditsEarned,
-- mh.i10_FieldsID,
-- mh.i11_FieldsID,
-- mh.i12_FieldsID
-- from [' + @v_MasterDBName + '].dbo.markshistory mh
-- join [' + @v_MasterDBName + '].dbo.student s
-- on s.iStudentID = mh.iStudentID
-- where s.cGovernmentNumber like ''' + @v_govnumber + '''
-- order by iSchoolID, cCourseCode, nYear, cTerm, iLV_SessionID'
-- exec(@commandString)
-- update tmpPOSTMarksHistory set iIndex = iIdentity
-- update tmpPREMarksHistory
-- set iIndex = RowNumber
-- from (
-- select
-- iIndex,
-- iIdentity,
-- Row_Number() over (Partition by cgovernmentnumber order by iSchoolID, cCourseCode, nYear, cTerm, iLV_SessionID ASC) as 'RowNumber'
-- from tmpPREMarksHistory) RN
-- where tmpPREMarksHistory.iIdentity = RN.iIdentity
-- --at this point all the records from the various seperate db's are loaded into the table. now compare.
-- if( (select COUNT(*) from tmpPOSTMarksHistory) != 0 )
-- if( (select COUNT(*) from tmpPREMarksHistory) = (select COUNT(*) from tmpPOSTMarksHistory) )
-- begin
-- --select * from tmpPOSTStudentHistory
-- EXECUTE dbo.CompareLists N'tmpPREMarksHistory', N'tmpPOSTMarksHistory', @v_govnumber, 'Marks History'
-- end
-- else
-- begin
-- select 'Counts dont match for student: ' + @v_govnumber
-- select * from tmpPREMarksHistory
-- select * from tmpPOSTMarksHistory
-- end
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ---STUDENT------------------------------------------------------------
-- ---------MARKS--------------------------------------------------------
-- ----------------------------------------------------------------------
-- --drop table tmpPREMarks
-- --drop table tmpPOSTMarks
-- truncate table tmpPREMarks
-- truncate table tmpPOSTMarks
-- declare cSchool cursor for
-- select tableName from @v_allDBs
-- order by tableName -- school code ordered lowest to highest
-- open cSchool
-- fetch cSchool into @v_currentChildDBName
-- while @@FETCH_STATUS = 0
-- begin
-- set @commandString = '
-- insert into tmpPREMarks(
-- iDB,
-- clastname,
-- cfirstname,
-- cgovernmentnumber,
-- iReportPeriodID,
-- iClassID,
-- nMark,
-- cMark,
-- dDateAssigned,
-- iEffortLegendDetailsID,
-- iMarksHistoryID,
-- iSchoolID,
-- nCredit,
-- nExamMark,
-- cExamMark,
-- nFinalMark,
-- iMarkCommentID,
-- mComment,
-- iAchievement_GradesID,
-- nregentmark,
-- iLV_AchievementID,
-- iLV_FinalAchievementID,
-- mAltLang_Comment )
-- select
-- ''pre'',
-- s.clastname,
-- s.cfirstname,
-- ''' + @v_govnumber + ''',
-- rp.cName,
-- isnull([' + @v_currentChildDBName + '].dbo.GetClassNameSection(m.iClassID),0) as ''iClassID'',
-- m.nMark,
-- m.cMark,
-- m.dDateAssigned,
-- m.iEffortLegendDetailsID,
-- isnull((select 1 from [' + @v_currentChildDBName + '].dbo.markshistory where imarkshistoryid = m.iMarksHistoryID and istudentid = s.istudentid),0),
-- isnull([' + @v_currentChildDBName + '].dbo.GetSchoolName(m.iSchoolID),0) as ''iSchoolID'',
-- m.nCredit,
-- m.nExamMark,
-- m.cExamMark,
-- m.nFinalMark,
-- m.iMarkCommentID,
-- m.mComment,
-- m.iAchievement_GradesID,
-- m.nregentmark,
-- m.iLV_AchievementID,
-- m.iLV_FinalAchievementID,
-- m.mAltLang_Comment
-- from [' + @v_currentChildDBName + '].dbo.Marks m
-- join [' + @v_currentChildDBName + '].dbo.Student s
-- on m.iStudentID = s.iStudentID
-- join [' + @v_currentChildDBName + '].dbo.ReportPeriod rp
-- on rp.iReportPeriodID = m.iReportPeriodID
-- full join [' + @v_currentChildDBName + '].dbo.markshistory mh
-- on mh.imarkshistoryid = m.imarkshistoryid
-- where s.cGovernmentNumber like ''' + @v_govnumber + ''''
-- exec(@commandString)
-- --select @CommandString
-- fetch next from cSchool into @v_currentChildDBName
-- end
-- close cSchool
-- deallocate cSchool
-- ------reorder PRE
-- --set @v_MarksCounter = 1
-- --declare cMarksRenumber cursor for
-- -- select iIdentity from tmpPREMarks
-- -- order by tmpPREMarks.iSchoolID, iClassID, tmpPREMarks.iReportPeriodID
-- --open cMarksRenumber
-- --fetch cMarksRenumber into @v_MarksIndex
-- --while @@FETCH_STATUS = 0
-- --begin
-- -- update tmpPREMarks
-- -- set iIndex = @v_MarksCounter
-- -- where iIdentity = @v_MarksIndex
-- -- set @v_MarksCounter = @v_MarksCounter + 1
-- -- fetch next from cMarksRenumber into @v_MarksIndex
-- --end
-- --close cMarksRenumber
-- --deallocate cMarksRenumber
-- ---------
-- set @commandString = '
-- insert into tmpPOSTMarks(
-- iDB,
-- clastname,
-- cfirstname,
-- cgovernmentnumber,
-- iReportPeriodID,
-- iClassID,
-- nMark,
-- cMark,
-- dDateAssigned,
-- iEffortLegendDetailsID,
-- iMarksHistoryID,
-- iSchoolID,
-- nCredit,
-- nExamMark,
-- cExamMark,
-- nFinalMark,
-- iMarkCommentID,
-- mComment,
-- iAchievement_GradesID,
-- nregentmark,
-- iLV_AchievementID,
-- iLV_FinalAchievementID,
-- mAltLang_Comment )
-- select
-- ''post'',
-- s.clastname,
-- s.cfirstname,
-- ''' + @v_govnumber + ''',
-- rp.cName,
-- isnull([' + @v_MasterDBName + '].dbo.GetClassNameSection(m.iClassID),0) as ''iClassID'',
-- m.nMark,
-- m.cMark,
-- m.dDateAssigned,
-- m.iEffortLegendDetailsID,
-- isnull((select 1 from dbo.markshistory where imarkshistoryid = m.iMarksHistoryID and istudentid = s.istudentid),0),
-- isnull(dbo.GetSchoolName(m.iSchoolID),0) as ''iSchoolID'',
-- m.nCredit,
-- m.nExamMark,
-- m.cExamMark,
-- m.nFinalMark,
-- m.iMarkCommentID,
-- m.mComment,
-- m.iAchievement_GradesID,
-- m.nregentmark,
-- m.iLV_AchievementID,
-- m.iLV_FinalAchievementID,
-- m.mAltLang_Comment
-- from Marks m
-- join Student s
-- on m.iStudentID = s.iStudentID
-- join ReportPeriod rp
-- on rp.iReportPeriodID = m.iReportPeriodID
-- full join markshistory mh
-- on mh.imarkshistoryid = m.imarkshistoryid
-- where s.cGovernmentNumber like ''' + @v_govnumber + '''
-- order by iSchoolID, iClassID, rp.cName '
-- exec(@commandString)
-- update tmpPOSTMarks set iIndex = iIdentity
-- update tmpPREMarks
-- set iIndex = RowNumber
-- from (
-- select
-- iIndex,
-- iIdentity,
-- Row_Number() over (Partition by cgovernmentnumber order by tmpPREMarks.iSchoolID, iClassID, tmpPREMarks.iReportPeriodID ASC) as 'RowNumber'
-- from tmpPREMarks) RN
-- where tmpPREMarks.iIdentity = RN.iIdentity
-- --at this point all the records from the various seperate db's are loaded into the table. now compare.
-- if( (select COUNT(*) from tmpPOSTMarks) != 0 )
-- if( (select COUNT(*) from tmpPREMarks) = (select COUNT(*) from tmpPOSTMarks) )
-- begin
-- EXECUTE dbo.CompareLists N'tmpPREMarks', N'tmpPOSTMarks', @v_govnumber, 'Marks'
-- end
-- else
-- begin
-- select 'Counts on Marks table dont match for student: ' + @v_govnumber
-- select * from tmpPREMarks
-- select * from tmpPOSTMarks
-- end
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----STUDENT-----------------------------------------------------------
-- ---------ATTENDANCE---------------------------------------------------
-- ----------------------------------------------------------------------
-- drop table tmpPREattendance
-- drop table tmpPOSTattendance
-- create table tmpPREattendance (
-- iIdentity int identity NOT NULL,
-- iIndex int,
-- iDB varchar(50),
-- clastname varchar(50),
-- cfirstname varchar(50),
-- cgovernmentnumber varchar(50),
-- iBlockNumber varchar(20),
-- iAttendanceStatusID varchar(50),
-- iAttendanceReasonsID varchar(50),
-- dDate varchar(20),
-- iClassID varchar(50),
-- iMinutes varchar(5),
-- mComment varchar(1000),
-- iUsersID varchar(20),
-- iStaffID varchar(20),
-- iSchoolID varchar(20),
-- e_iStudentID varchar(20), --student number
-- e_iClassID varchar(50),
-- e_dInDate varchar(20),
-- e_dOutDate varchar(20),
-- e_iLV_CompletionStatus varchar(20),
-- e_iSchoolID varchar(50)
-- )
-- create table tmpPOSTattendance (
-- iIdentity int identity NOT NULL,
-- iIndex int,
-- iDB varchar(50),
-- clastname varchar(50),
-- cfirstname varchar(50),
-- cgovernmentnumber varchar(50),
-- iBlockNumber varchar(20),
-- iAttendanceStatusID varchar(50),
-- iAttendanceReasonsID varchar(50),
-- dDate varchar(20),
-- iClassID varchar(50),
-- iMinutes varchar(5),
-- mComment varchar(1000),
-- iUsersID varchar(20),
-- iStaffID varchar(20),
-- iSchoolID varchar(20),
-- e_iStudentID varchar(20), --student number
-- e_iClassID varchar(50),
-- e_dInDate varchar(20),
-- e_dOutDate varchar(20),
-- e_iLV_CompletionStatus varchar(20),
-- e_iSchoolID varchar(50)
-- )
-- declare cSchool cursor for
-- select tableName from @v_allDBs
-- order by tableName -- school code ordered lowest to highest
-- open cSchool
-- fetch cSchool into @v_currentChildDBName
-- while @@FETCH_STATUS = 0
-- begin
-- set @commandString = '
-- insert into tmpPREattendance (
-- iDB,
-- cLastName,
-- cFirstName,
-- cGovernmentNumber,
-- iBlockNumber,
-- iAttendanceStatusID,
-- iAttendanceReasonsID,
-- dDate,
-- iClassID,
-- iMinutes,
-- mComment,
-- iUsersID,
-- iStaffID,
-- iSchoolID,
-- e_iStudentID,
-- e_iClassID )
-- select
-- ''[' + @v_currentChildDBName + ']'',
-- s.cLastName,
-- s.cFirstName,
-- ''' + @v_govnumber + ''',
-- a.iBlockNumber,
-- a.iAttendanceStatusID,
-- isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(a.iAttendanceReasonsID),0) as ''iAttendanceReasonsID'',
-- a.dDate,
-- isnull([' + @v_currentChildDBName + '].dbo.GetClassNameSection(a.iClassID),0) as ''iClassID'',
-- a.iMinutes,
-- a.mComment,
-- a.iUsersID,
-- isnull([' + @v_currentChildDBName + '].dbo.GetStaffName(a.iStaffID),0) as ''iStaffID'',
-- isnull([' + @v_currentChildDBName + '].dbo.GetSchoolName(a.iSchoolID),0) as ''iSchoolID'',
-- isnull([' + @v_currentChildDBName + '].dbo.GetStudentName(e.iStudentID),0) as ''e_iStudentID'',
-- isnull([' + @v_currentChildDBName + '].dbo.GetClassName(e.iClassID),0) as ''e_iClassID''
-- from [' + @v_currentChildDBName + '].dbo.Student s
-- join [' + @v_currentChildDBName + '].dbo.Attendance a
-- on s.istudentid = a.istudentid
-- join [' + @v_currentChildDBName + '].dbo.Enrollment e
-- on e.iEnrollmentID = a.iEnrollmentID
-- where s.cgovernmentnumber like ''' + @v_govnumber + '''
-- '
-- exec (@commandString)
-- --select @commandString
-- --select * from tmpPREattendance
-- fetch next from cSchool into @v_currentChildDBName
-- end
-- close cSchool
-- deallocate cSchool
-- ------reorder PRE
-- --set @v_AttendanceCounter = 1
-- --declare cAttendanceRenumber cursor for
-- -- select iIdentity from tmpPREAttendance
-- -- order by tmpPREAttendance.iSchoolID, iClassID
-- --open cAttendanceRenumber
-- --fetch cAttendanceRenumber into @v_AttendanceIndex
-- --while @@FETCH_STATUS = 0
-- --begin
-- -- update tmpPREAttendance
-- -- set iIndex = @v_AttendanceCounter
-- -- where iIdentity = @v_AttendanceIndex
-- -- set @v_AttendanceCounter = @v_AttendanceCounter + 1
-- -- fetch next from cAttendanceRenumber into @v_AttendanceIndex
-- --end
-- --close cAttendanceRenumber
-- --deallocate cAttendanceRenumber
-- ---------
-- set @commandString = '
-- insert into tmpPOSTattendance (
-- iDB,
-- cLastName,
-- cFirstName,
-- cGovernmentNumber,
-- iBlockNumber,
-- iAttendanceStatusID,
-- iAttendanceReasonsID,
-- dDate,
-- iClassID,
-- iMinutes,
-- mComment,
-- iUsersID,
-- iStaffID,
-- iSchoolID,
-- e_iStudentID,
-- e_iClassID )
-- select
-- ''[' + @v_MasterDBName + ']'',
-- s.cLastName,
-- s.cFirstName,
-- ''' + @v_govnumber + ''',
-- a.iBlockNumber,
-- a.iAttendanceStatusID,
-- isnull([' + @v_MasterDBName + '].dbo.GetLookupValue(a.iAttendanceReasonsID),0) as ''iAttendanceReasonsID'',
-- a.dDate,
-- isnull([' + @v_MasterDBName + '].dbo.GetClassNameSection(a.iClassID),0) as ''iClassID'',
-- a.iMinutes,
-- a.mComment,
-- a.iUsersID,
-- isnull([' + @v_MasterDBName + '].dbo.GetStaffName(a.iStaffID),0) as ''iStaffID'',
-- isnull([' + @v_MasterDBName + '].dbo.GetSchoolName(a.iSchoolID),0) as ''iSchoolID'',
-- isnull([' + @v_MasterDBName + '].dbo.GetStudentName(e.iStudentID),0) as ''e_iStudentID'',
-- isnull([' + @v_MasterDBName + '].dbo.GetClassName(e.iClassID),0) as ''e_iClassID''
-- from [' + @v_MasterDBName + '].dbo.Student s
-- join [' + @v_MasterDBName + '].dbo.Attendance a
-- on s.istudentid = a.istudentid
-- join [' + @v_MasterDBName + '].dbo.Enrollment e
-- on e.iEnrollmentID = a.iEnrollmentID
-- where s.cgovernmentnumber like ''' + @v_govnumber + '''
-- order by a.iSchoolID, iClassID'
-- exec (@commandString)
-- update tmpPOSTattendance set iIndex = iIdentity
-- update tmpPREattendance
-- set iIndex = RowNumber
-- from (
-- select
-- iIndex,
-- iIdentity,
-- Row_Number() over (Partition by cgovernmentnumber order by tmpPREAttendance.iSchoolID, iClassID ASC) as 'RowNumber'
-- from tmpPREattendance) RN
-- where tmpPREattendance.iIdentity = RN.iIdentity
-- --at this point all the records from the various seperate db's are loaded into the table. now compare.
-- if( (select COUNT(*) from tmpPOSTAttendance) != 0 )
-- if( (select COUNT(*) from tmpPREAttendance) = (select COUNT(*) from tmpPOSTAttendance) )
-- begin
-- EXECUTE dbo.CompareLists N'tmpPREAttendance', N'tmpPOSTAttendance', @v_govnumber, 'Attendance'
-- end
-- else
-- begin
-- select 'Counts on Attendance table dont match for student: ' + @v_govnumber
-- select * from tmpPREAttendance
-- select * from tmpPOSTAttendance
-- end
--select '1'
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----------------------------------------------------------------------
-- ----STUDENT-----------------------------------------------------------
-- ---------ATTENDANCEHISTORY--------------------------------------------
-- ----------------------------------------------------------------------
-- truncate table tmpPREAttendHistory
-- truncate table tmpPOSTAttendHistory
-- declare cSchool cursor for
-- select tableName from @v_allDBs
-- order by tableName -- school code ordered lowest to highest
-- --select * from attendancehistory
-- open cSchool
-- fetch cSchool into @v_currentChildDBName
-- while @@FETCH_STATUS = 0
-- begin
-- set @commandString = '
-- insert into tmpPREAttendHistory (
-- iDB,
-- clastname,
-- cfirstname,
-- cgovernmentnumber,
-- iAttendanceID,
-- cBlockInformation,
-- cAttendanceStatus,
-- cAttendanceReason,
-- dDate,
-- cClassName,
-- lExcusable,
-- iSchoolID,
-- mComment )
-- select
-- ''[' + @v_currentChildDBName + ']'',
-- s.cLastName,
-- s.cFirstName,
-- ''' + @v_govnumber + ''',
-- isnull((select 1 from [' + @v_currentChildDBName + '].dbo.Attendance where iAttendanceID = ah.iAttendanceID and istudentid = s.istudentid),0),
-- ah.cBlockInformation,
-- ah.cAttendanceStatus,
-- ah.cAttendanceReason,
-- ah.dDate,
-- ah.cClassName,
-- ah.lExcusable,
-- isnull([' + @v_currentChildDBName + '].dbo.GetSchoolName(ah.iSchoolID),0) as ''iSchoolID'',
-- ah.mComment
-- from [' + @v_currentChildDBName + '].dbo.AttendanceHistory ah
-- join [' + @v_currentChildDBName + '].dbo.Student s
-- on s.iStudentID = ah.iStudentID
-- where s.cgovernmentnumber like ''' + @v_govnumber + '''
-- '
-- --select @commandString
-- exec (@commandString)
-- --select * from tmpPREattendHistory
-- fetch next from cSchool into @v_currentChildDBName
-- end
-- close cSchool
-- deallocate cSchool
-- set @commandString = '
-- insert into tmpPOSTAttendHistory (
-- iDB,
-- clastname,
-- cfirstname,
-- cgovernmentnumber,
-- iAttendanceID,
-- cBlockInformation,
-- cAttendanceStatus,
-- cAttendanceReason,
-- dDate,
-- cClassName,
-- lExcusable,
-- iSchoolID,
-- mComment )
-- select
-- ''[' + @v_MasterDBName + ']'',
-- s.cLastName,
-- s.cFirstName,
-- ''' + @v_govnumber + ''',
-- isnull((select 1 from [' + @v_MasterDBName + '].dbo.Attendance where iAttendanceID = ah.iAttendanceID and istudentid = s.istudentid),0),
-- ah.cBlockInformation,
-- ah.cAttendanceStatus,
-- ah.cAttendanceReason,
-- ah.dDate,
-- ah.cClassName,
-- ah.lExcusable,
-- isnull([' + @v_MasterDBName + '].dbo.GetSchoolName(ah.iSchoolID),0) as ''iSchoolID'',
-- ah.mComment
-- from [' + @v_MasterDBName + '].dbo.AttendanceHistory ah
-- join [' + @v_MasterDBName + '].dbo.Student s
-- on s.iStudentID = ah.iStudentID
-- where s.cgovernmentnumber like ''' + @v_govnumber + '''
-- '
-- --select @commandString
-- exec (@commandString)
-- update tmpPREattendhistory
-- set iIndex = RowNumber
-- from (
-- select
-- iIndex,
-- iIdentity,
-- Row_Number() over (Partition by cgovernmentnumber order by tmpPREattendhistory.iSchoolID, cast(tmpPREattendhistory.dDate as smalldatetime), tmpPREattendhistory.cBlockInformation, tmpPREattendhistory.cClassName ASC) as 'RowNumber'
-- from tmpPREattendhistory) RN
-- where tmpPREattendhistory.iIdentity = RN.iIdentity
-- update tmpPOSTAttendHistory
-- set iIndex = RowNumber
-- from (
-- select
-- iIndex,
-- iIdentity,
-- Row_Number() over (Partition by cgovernmentnumber order by tmpPOSTAttendHistory.iSchoolID, cast(tmpPOSTAttendHistory.dDate as smalldatetime), tmpPOSTAttendHistory.cBlockInformation, tmpPOSTAttendHistory.cClassName ASC) as 'RowNumber'
-- from tmpPOSTAttendHistory) RN
-- where tmpPOSTAttendHistory.iIdentity = RN.iIdentity
-- --at this point all the records from the various seperate db's are loaded into the table. now compare.
-- if( (select COUNT(*) from tmpPOSTattendHistory) != 0 )
-- if( (select COUNT(*) from tmpPREAttendHistory) = (select COUNT(*) from tmpPOSTattendHistory) )
-- begin
-- EXECUTE dbo.CompareLists N'tmpPREAttendHistory', N'tmpPOSTattendHistory', @v_govnumber, 'AttendanceHistory'
-- end
-- else
-- begin
-- select 'Counts on Attendance table dont match for student: ' + @v_govnumber
-- select * from tmpPREAttendHistory
-- select * from tmpPOSTattendHistory
-- end
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----STUDENT-----------------------------------------------------------
---------STUDENTFEE--------------------------------------------
----------------------------------------------------------------------
--select * from studentfee
drop table tmpPREStudentFee
drop table tmpPOSTStudentFee
create table tmpPREStudentFee (
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
clastname varchar(50),
cfirstname varchar(50),
cgovernmentnumber varchar(50),
iFeesID varchar(50),
iStudentID varchar(20),
nAmount varchar(20),
dDate varchar(50),
iSchoolYear varchar(20),
mComment varchar(max), ---this is probably not long enough
iSchoolID varchar(50),
--iStudentPaymentID int,
spd_nAmount varchar(20),
spd_dDate varchar(30),
spd_iSchoolID varchar(50)
)
create table tmpPOSTStudentFee (
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
clastname varchar(50),
cfirstname varchar(50),
cgovernmentnumber varchar(50),
iFeesID varchar(50),
iStudentID varchar(20),
nAmount varchar(20),
dDate varchar(50),
iSchoolYear varchar(20),
mComment varchar(max), ---this is probably not long enough
iSchoolID varchar(50),
spd_nAmount varchar(20),
spd_dDate varchar(30),
spd_iSchoolID varchar(50)
)
--select * from studentfee
declare cSchool cursor for
select tableName from @v_allDBs
order by tableName -- school code ordered lowest to highest
open cSchool
fetch cSchool into @v_currentChildDBName
while @@FETCH_STATUS = 0
begin
set @commandString = '
insert into tmpPREStudentFee (
iDB,
clastname,
cfirstname,
cgovernmentnumber,
iFeesID,
iStudentID,
nAmount,
dDate,
iSchoolYear,
mComment,
iSchoolID,
spd_nAmount,
spd_dDate,
spd_iSchoolID )
select
''[' + @v_currentChildDBName + ']'',
s.cLastName,
s.cFirstName,
s.cGovernmentNumber,
sf.iFeesID,
sf.iStudentID,
sf.nAmount,
sf.dDate,
sf.iSchoolYear,
sf.mComment,
sf.iSchoolID,
spd.nAmount,
spd.dDate,
spd.iSchoolID
from [' + @v_currentChildDBName + '].dbo.studentfee sf
left join [' + @v_currentChildDBName + '].dbo.student s
on sf.istudentid = s.istudentid
left join [' + @v_currentChildDBName + '].dbo.studentpaydetails spd
on sf.istudentfeeid = spd.istudentfeeid
where s.cgovernmentnumber like ''' + @v_govnumber + '''
'
exec(@commandString)
--select @commandString
--select * from StudentPayDetails
fetch next from cSchool into @v_currentChildDBName
end
close cSchool
deallocate cSchool
--select * from studentpaydetails
set @commandString = '
insert into tmpPOSTStudentFee (
iDB,
clastname,
cfirstname,
cgovernmentnumber,
iFeesID,
iStudentID,
nAmount,
dDate,
iSchoolYear,
mComment,
iSchoolID,
spd_nAmount,
spd_dDate,
spd_iSchoolID )
select
''[' + @v_MasterDBName + ']'',
s.cLastName,
s.cFirstName,
s.cGovernmentNumber,
sf.iFeesID,
sf.iStudentID,
sf.nAmount,
sf.dDate,
sf.iSchoolYear,
sf.mComment,
sf.iSchoolID,
spd.nAmount,
spd.dDate,
spd.iSchoolID
from [' + @v_MasterDBName + '].dbo.studentfee sf
left join [' + @v_MasterDBName + '].dbo.student s
on sf.istudentid = s.istudentid
left join [' + @v_MasterDBName + '].dbo.studentpaydetails spd
on sf.istudentfeeid = spd.istudentfeeid
where s.cgovernmentnumber like ''' + @v_govnumber + '''
'
exec(@commandString)
--select * from studentpaydetails
update tmpPREStudentFee
set iIndex = RowNumber
from (
select
iIndex,
iIdentity,
Row_Number() over (Partition by cgovernmentnumber order by iSchoolID, iSchoolYear, dDate, nAmount ASC) as 'RowNumber'
from tmpPREStudentFee) RN
where tmpPREStudentFee.iIdentity = RN.iIdentity
update tmpPOSTStudentFee
set iIndex = RowNumber
from (
select
iIndex,
iIdentity,
Row_Number() over (Partition by cgovernmentnumber order by iSchoolID, iSchoolYear, dDate, nAmount ASC) as 'RowNumber'
from tmpPOSTStudentFee) RN
where tmpPOSTStudentFee.iIdentity = RN.iIdentity
--select * from tmpPREStudentFee
--select * from tmpPOSTStudentFee
--at this point all the records from the various seperate db's are loaded into the table. now compare.
if( (select COUNT(*) from tmpPOSTStudentFee) != 0 )
if( (select COUNT(*) from tmpPREStudentFee) = (select COUNT(*) from tmpPOSTStudentFee) )
begin
EXECUTE dbo.CompareLists N'tmpPREStudentFee', N'tmpPOSTStudentFee', @v_govnumber, 'StudentFee'
end
else
begin
select 'Counts on StudentFee table dont match for student: ' + @v_govnumber
select * from tmpPREStudentFee
select * from tmpPOSTStudentFee
end
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
fetch next from cStudent into @v_govnumber
end
close cStudent
deallocate cStudent
Raw
mCompareNonStudentBasedTables.sql
use eastcentral
SET NOCOUNT ON
GO
--maybe create a generic stored procedure later to reorder
--fix the Marks section to join to markshistory if iMarksHistoryID is a valid id. right now im just comparing to see if they both exist, not to see if they contain the same info.
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID ( 'dbo.CompareLists', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.CompareLists;
GO
CREATE PROCEDURE dbo.CompareLists
@listPRE varchar(100),
@listPOST varchar(100),
@tmpGovNumber varchar(100),
@Area varchar(100) = ''
AS
-- some assumptions this procedure makes:
-- iIndex is a unique identifier that must exist for each column in the table. it will be used to sort and determine which table should be compared to which.
SET NOCOUNT ON
declare @v_Derp int -- used to loop through records in cDerp
declare @v_DerpColumns varchar(100) -- used to loop through columns in cDerpColumns
drop table #SuperTempFlagTable
create table #SuperTempFlagTable ( flag bit )
update #SuperTempFlagTable set flag = 0
declare @commandString varchar(5000)
set @commandString = '
declare cDerp cursor for
select iIndex from ' + @listPRE + '
order by iIndex'
exec(@commandstring)
open cDerp
fetch cDerp into @v_Derp
while @@FETCH_STATUS = 0
begin
--clean out the temp table
IF OBJECT_ID ( N'tmpDerp', 'U' ) IS NOT NULL
DROP table tmpDerp
BEGIN --Column processing
--grab the current record from both 'pre' and 'post' lists
set @commandString ='
select * INTO tmpDerp FROM (
select *
from [' + @listPRE + ']
where iIndex = (select MIN(iIndex) from [' + @listPRE + '])
UNION ALL
select *
from [' + @listPOST + ']
where iIndex = (select MIN(iIndex) from [' + @listPOST + '])
) un '
exec(@commandstring)
declare cDerpColumns cursor for
select column_name from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME like 'tmpDerp%'
and COLUMN_NAME not in ('iDB', 'iIndex', 'iIdentity')
---fields below i am specifically excluding due to known issues with 4.2.98 migrationtool build
AND COLUMN_NAME not in ('iLV_LanguageID') --'iLV_SessionID',
open cDerpColumns
fetch cDerpColumns into @v_DerpColumns
while @@FETCH_STATUS = 0
begin
set @commandString ='
if (select MIN(CAST([' + @v_DerpColumns + '] as varchar)) from tmpDerp) != (select MAX(cast([' + @v_DerpColumns + '] as varchar)) from tmpDerp)
begin
select
rtrim(iIndex) as ''iIndex'',
rtrim([' + @v_DerpColumns + ']) as ''' + @v_DerpColumns + ''',
rtrim(ischoolid) as ''ischoolid'',
rtrim(iDB) as ''db'',
''' + @Area + ''' as ''area'',
*
from tmpDerp
update #SuperTempFlagTable set flag = 1
end'
exec(@commandString)
if((select flag from #SuperTempFlagTable) = 1)
goto BREAK_OUT
fetch next from cDerpColumns into @v_DerpColumns
end
BREAK_OUT:
close cDerpColumns
deallocate cDerpColumns
END
set @commandString = '
delete from [' + @listPRE + ']
where iIndex = (select MIN(iIndex) from [' + @listPRE + '])
delete from [' + @listPOST + ']
where iIndex = (select MIN(iIndex) from [' + @listPOST + '])'
exec(@commandstring)
fetch next from cDerp into @v_Derp
end
close cDerp
deallocate cDerp
--exec(@commandString)
GO
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
--general
declare @v_MasterDBName varchar(100) --upsized sql db
declare @v_OwnershipDBName varchar(100) --ownership file converted to a db
declare @v_govnumber varchar(20) --used to loop through students
declare @v_columns varchar(100) --used to loop through the columns for averaging
declare @commandString varchar(max) --string for building sql commands
declare @v_currentChildDBName varchar(100) --used to loop through schools
declare @iIndex int; --used to order records
---class
declare @v_SchoolClassIndex int --used to store indexes for renumbering
declare @v_SchoolClassCounter int --incremental counter var
---course
declare @v_SchoolCourseIndex int --used to store indexes for renumbering
declare @v_SchoolCourseCounter int --incremental counter var
---attendancepresent
declare @v_SchoolAttendancePresentIndex int --used to store indexes for renumbering
declare @v_SchoolAttendancePresentCounter int --incremental counter var
---attendancereasons
declare @v_SchoolAttendanceReasonIndex int --used to store indexes for renumbering
declare @v_SchoolAttendanceReasonCounter int --incremental counter var
set @v_MasterDBName = 'eastcentral'
--make a table var with all the db's we are going to search
declare @v_allDBs table ( tableName varchar(100) )
insert into @v_allDBs (tableName) VALUES ('0433'), ('3870') , ('3970'), ('4970'), ('eastcentral2')
--begin the non-ownership loop
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
---SCHOOL-------------------------------------------------------------
---------CLASS--------------------------------------------------------
----------------------------------------------------------------------
drop table #tmpPREClass
drop table #tmpPOSTClass
create table #tmpPREClass(
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
iCourseID varchar(50),
cName varchar(50),
cSection varchar(50),
iTrackID varchar(50),
iIdealCapacity varchar(50),
nFee varchar(50),
nCredit varchar(50),
iLV_GenderID varchar(50),
iLow_GradesID varchar(50),
iHigh_GradesID varchar(50),
mComment varchar(50),
iLV_LanguageID varchar(50),
iDepartmentExamsID varchar(50),
lExamMark varchar(50),
lCalculatedMark varchar(50),
lFinalMark varchar(50),
iMarkType varchar(50),
lSchoolExam varchar(50),
iMarkLegendID varchar(50),
iSchoolID varchar(50),
iRequestID varchar(50),
iLV_SessionID varchar(50),
nExamMonth varchar(50),
nExamWeight varchar(50),
dStartDate varchar(50),
dEndDate varchar(50),
iMaster_ClassID varchar(50),
lMasterClass varchar(50),
dExamDate varchar(50),
iOrder varchar(50),
iUserField3 varchar(50),
iUserField2 varchar(50),
lScheduled varchar(50),
lEOYMark varchar(50),
lMoveEOYToHistory varchar(50),
lExcludeEOYGPA varchar(50),
lAssignCreditEOY varchar(50),
lCalcEOYMark varchar(50),
iLV_EOYSessionID varchar(50),
iSeatingRows varchar(50),
iSeatingColumns varchar(50),
lUseStaffMax varchar(50),
iDefault_StaffID varchar(50),
iDefault_RoomID varchar(50),
iMaxCapacity varchar(50),
lAcademic varchar(50),
iHomeRoomID varchar(50),
lRegentMark varchar(50),
iUserField4 varchar(50),
iUserField5 varchar(50),
iUserField6 varchar(50)
)
create table #tmpPOSTClass(
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
iCourseID varchar(50),
cName varchar(50),
cSection varchar(50),
iTrackID varchar(50),
iIdealCapacity varchar(50),
nFee varchar(50),
nCredit varchar(50),
iLV_GenderID varchar(50),
iLow_GradesID varchar(50),
iHigh_GradesID varchar(50),
mComment varchar(50),
iLV_LanguageID varchar(50),
iDepartmentExamsID varchar(50),
lExamMark varchar(50),
lCalculatedMark varchar(50),
lFinalMark varchar(50),
iMarkType varchar(50),
lSchoolExam varchar(50),
iMarkLegendID varchar(50),
iSchoolID varchar(50),
iRequestID varchar(50),
iLV_SessionID varchar(50),
nExamMonth varchar(50),
nExamWeight varchar(50),
dStartDate varchar(50),
dEndDate varchar(50),
iMaster_ClassID varchar(50),
lMasterClass varchar(50),
dExamDate varchar(50),
iOrder varchar(50),
iUserField3 varchar(50),
iUserField2 varchar(50),
lScheduled varchar(50),
lEOYMark varchar(50),
lMoveEOYToHistory varchar(50),
lExcludeEOYGPA varchar(50),
lAssignCreditEOY varchar(50),
lCalcEOYMark varchar(50),
iLV_EOYSessionID varchar(50),
iSeatingRows varchar(50),
iSeatingColumns varchar(50),
lUseStaffMax varchar(50),
iDefault_StaffID varchar(50),
iDefault_RoomID varchar(50),
iMaxCapacity varchar(50),
lAcademic varchar(50),
iHomeRoomID varchar(50),
lRegentMark varchar(50),
iUserField4 varchar(50),
iUserField5 varchar(50),
iUserField6 varchar(50)
)
declare cSchool cursor for
select tableName from @v_allDBs
order by tableName -- school code ordered lowest to highest
open cSchool
fetch cSchool into @v_currentChildDBName
while @@FETCH_STATUS = 0
begin
--NOTE: in the future should probably join this to Course and Request and Trackto get more accurate info about linked courses and requests.
set @commandString =
'insert into #tmpPREClass (
iDB,
iCourseID,
cName,
cSection,
iTrackID,
iIdealCapacity,
nFee,
nCredit,
iLV_GenderID,
iLow_GradesID,
iHigh_GradesID,
mComment,
iLV_LanguageID,
iDepartmentExamsID,
lExamMark,
lCalculatedMark,
lFinalMark,
iMarkType,
lSchoolExam,
iMarkLegendID,
iSchoolID,
iRequestID,
iLV_SessionID,
nExamMonth,
nExamWeight,
dStartDate,
dEndDate,
iMaster_ClassID,
lMasterClass,
dExamDate,
iOrder,
iUserField2,
iUserField3,
lScheduled,
lEOYMark,
lMoveEOYToHistory,
lExcludeEOYGPA,
lAssignCreditEOY,
lCalcEOYMark,
iLV_EOYSessionID,
iSeatingRows,
iSeatingColumns,
lUseStaffMax,
iDefault_StaffID,
iDefault_RoomID,
iMaxCapacity,
lAcademic,
iHomeRoomID,
lRegentMark,
iUserField4,
iUserField5,
iUserField6 )
select
''pre'',
isnull([' + @v_currentChildDBName + '].dbo.GetCourseName(c.iCourseID,0),0) as ''iCourseID'',
c.cname,
c.csection,
c.iTrackID,
c.iIdealCapacity,
c.nFee,
c.nCredit,
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(c.iLV_GenderID),0) as ''iLV_GenderID'',
isnull([' + @v_currentChildDBName + '].dbo.GetGradesName(c.iLow_GradesID),0) as ''iLow_GradesID'',
isnull([' + @v_currentChildDBName + '].dbo.GetGradesName(c.iHigh_GradesID),0) as ''iHigh_GradesID'',
c.mComment,
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(c.iLV_LanguageID),0) as ''iLV_LanguageID'',
c.iDepartmentExamsID,
c.lExamMark,
c.lCalculatedMark,
c.lFinalMark,
c.iMarkType,
c.lSchoolExam,
c.iMarkLegendID,
isnull([' + @v_currentChildDBName + '].dbo.GetSchoolName(c.iSchoolID),0) as ''iSchoolID'',
c.iRequestID,
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(c.iLV_SessionID),0) as ''iLV_SessionID'',
c.nExamMonth,
c.nExamWeight,
c.dStartDate,
c.dEndDate,
isnull([' + @v_currentChildDBName + '].dbo.GetClassNameSection(c.iMaster_ClassID),0) as ''iMaster_ClassID'',
c.lMasterClass,
c.dExamDate,
c.iOrder,
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(c.iUserField2),0) as ''iUserField2'',
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(c.iUserField3),0) as ''iUserField3'',
c.lScheduled,
c.lEOYMark,
c.lMoveEOYToHistory,
c.lExcludeEOYGPA,
c.lAssignCreditEOY,
c.lCalcEOYMark,
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(c.iLV_EOYSessionID),0) as ''iLV_EOYSessionID'',
c.iSeatingRows,
c.iSeatingColumns,
c.lUseStaffMax,
c.iDefault_StaffID,
c.iDefault_RoomID,
c.iMaxCapacity,
c.lAcademic,
c.iHomeRoomID,
c.lRegentMark,
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(c.iUserField4),0) as ''iUserField4'',
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(c.iUserField5),0) as ''iUserField5'',
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(c.iUserField6),0) as ''iUserField6''
from [' + @v_currentChildDBName + '].dbo.class c
'
exec(@CommandString)
fetch next from cSchool into @v_currentChildDBName
end
close cSchool
deallocate cSchool
--initialize counter
set @v_SchoolClassCounter = 1
--loop through all the records in #tmpPREStudentHistory. sorted by nSchoolYear and School Name, smallest to largest
declare cClassRenumber cursor for
select iIdentity from #tmpPREClass
order by ischoolid, iTrackID, iLV_SessionID, cname, csection, iMaster_ClassID
open cClassRenumber
fetch cClassRenumber into @v_SchoolClassIndex
while @@FETCH_STATUS = 0
begin
--set the iIndex in the related History table to be the index of this counter
update #tmpPREClass
set iIndex = @v_SchoolClassCounter
where iIdentity = @v_SchoolClassIndex
set @v_SchoolClassCounter = @v_SchoolClassCounter + 1
fetch next from cClassRenumber into @v_SchoolClassIndex
end
close cClassRenumber
deallocate cClassRenumber
-----
--NOTE: in the future should probably join this to Course and Request to get more accurate info about linked courses and requests
set @commandString =
'insert into #tmpPOSTClass (
iDB,
iCourseID,
cName,
cSection,
iTrackID,
iIdealCapacity,
nFee,
nCredit,
iLV_GenderID,
iLow_GradesID,
iHigh_GradesID,
mComment,
iLV_LanguageID,
iDepartmentExamsID,
lExamMark,
lCalculatedMark,
lFinalMark,
iMarkType,
lSchoolExam,
iMarkLegendID,
iSchoolID,
iRequestID,
iLV_SessionID,
nExamMonth,
nExamWeight,
dStartDate,
dEndDate,
iMaster_ClassID,
lMasterClass,
dExamDate,
iOrder,
iUserField2,
iUserField3,
lScheduled,
lEOYMark,
lMoveEOYToHistory,
lExcludeEOYGPA,
lAssignCreditEOY,
lCalcEOYMark,
iLV_EOYSessionID,
iSeatingRows,
iSeatingColumns,
lUseStaffMax,
iDefault_StaffID,
iDefault_RoomID,
iMaxCapacity,
lAcademic,
iHomeRoomID,
lRegentMark,
iUserField4,
iUserField5,
iUserField6 )
select
''post'',
isnull([' + @v_MasterDBName + '].dbo.GetCourseName(c.iCourseID,0),0) as ''iCourseID'',
c.cname,
c.csection,
c.iTrackID,
c.iIdealCapacity,
c.nFee,
c.nCredit,
isnull([' + @v_MasterDBName + '].dbo.GetLookupValue(c.iLV_GenderID),0) as ''iLV_GenderID'',
isnull([' + @v_MasterDBName + '].dbo.GetGradesName(c.iLow_GradesID),0) as ''iLow_GradesID'',
isnull([' + @v_MasterDBName + '].dbo.GetGradesName(c.iHigh_GradesID),0) as ''iHigh_GradesID'',
c.mComment,
isnull([' + @v_MasterDBName + '].dbo.GetLookupValue(c.iLV_LanguageID),0) as ''iLV_LanguageID'',
c.iDepartmentExamsID,
c.lExamMark,
c.lCalculatedMark,
c.lFinalMark,
c.iMarkType,
c.lSchoolExam,
c.iMarkLegendID,
isnull([' + @v_MasterDBName + '].dbo.GetSchoolName(c.iSchoolID),0) as ''iSchoolID'',
c.iRequestID,
isnull([' + @v_MasterDBName + '].dbo.GetLookupValue(c.iLV_SessionID),0) as ''iLV_SessionID'',
c.nExamMonth,
c.nExamWeight,
c.dStartDate,
c.dEndDate,
isnull([' + @v_MasterDBName + '].dbo.GetClassNameSection(c.iMaster_ClassID),0) as ''iMaster_ClassID'',
c.lMasterClass,
c.dExamDate,
c.iOrder,
isnull([' + @v_MasterDBName + '].dbo.GetLookupValue(c.iUserField2),0) as ''iUserField2'',
isnull([' + @v_MasterDBName + '].dbo.GetLookupValue(c.iUserField3),0) as ''iUserField3'',
c.lScheduled,
c.lEOYMark,
c.lMoveEOYToHistory,
c.lExcludeEOYGPA,
c.lAssignCreditEOY,
c.lCalcEOYMark,
isnull([' + @v_MasterDBName + '].dbo.GetLookupValue(c.iLV_EOYSessionID),0) as ''iLV_EOYSessionID'',
c.iSeatingRows,
c.iSeatingColumns,
c.lUseStaffMax,
c.iDefault_StaffID,
c.iDefault_RoomID,
c.iMaxCapacity,
c.lAcademic,
c.iHomeRoomID,
c.lRegentMark,
isnull([' + @v_MasterDBName + '].dbo.GetLookupValue(c.iUserField4),0) as ''iUserField4'',
isnull([' + @v_MasterDBName + '].dbo.GetLookupValue(c.iUserField5),0) as ''iUserField5'',
isnull([' + @v_MasterDBName + '].dbo.GetLookupValue(c.iUserField6),0) as ''iUserField6''
from [' + @v_MasterDBName + '].dbo.class c
'
exec(@CommandString)
--update #tmpPOSTClass set iIndex = iIdentity
--initialize counter
set @v_SchoolClassCounter = 1
--loop through all the records in #tmpPREStudentHistory. sorted by nSchoolYear and School Name, smallest to largest
declare cClassRenumber cursor for
select iIdentity from #tmpPOSTClass
order by ischoolid, iTrackID, iLV_SessionID, cname, csection, iMaster_ClassID
open cClassRenumber
fetch cClassRenumber into @v_SchoolClassIndex
while @@FETCH_STATUS = 0
begin
--set the iIndex in the related History table to be the index of this counter
update #tmpPOSTClass
set iIndex = @v_SchoolClassCounter
where iIdentity = @v_SchoolClassIndex
set @v_SchoolClassCounter = @v_SchoolClassCounter + 1
fetch next from cClassRenumber into @v_SchoolClassIndex
end
close cClassRenumber
deallocate cClassRenumber
-----
--select * from class where iCourseID not in (select iCourseID from Course)
--debug
select * from #tmpPREClass order by iIndex
select * from #tmpPOSTClass order by iIndex
select * from #tmpPREClass where iIndex in (122) order by iIndex
select * from #tmpPOSTClass where iIndex in (122)order by iIndex
--at this point all the records from the various seperate db's are loaded into the table. now compare.
if( (select COUNT(*) from #tmpPOSTClass) != 0 )
if( (select COUNT(*) from #tmpPREClass) = (select COUNT(*) from #tmpPOSTClass) )
begin
--select * from #tmpPOSTStudentHistory
EXECUTE dbo.CompareLists N'#tmpPREClass', N'#tmpPOSTClass', '1', 'Class'
end
else
begin
select 'Class record counts dont match' --will they ever? :S
select * from #tmpPREClass order by iIndex
select * from #tmpPOSTClass order by iIndex
end
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--SCHOOL-------------------------------------------------------------
-------COURSE-------------------------------------------------------
--------------------------------------------------------------------
drop table #tmpPRECourse
drop table #tmpPOSTCourse
create table #tmpPRECourse(
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
cName varchar(50),
cCourseCode varchar(50),
iGovCourseID varchar(50),
cGovernmentCode varchar(50),
cDepartment varchar(50),
nFee varchar(50),
iLow_GradesID varchar(50),
iHigh_GradesID varchar(50),
nLowCredit varchar(50),
nHighCredit varchar(50),
mComment varchar(50),
iDepartmentExamsID varchar(50),
iLV_LanguageID varchar(50),
nAverageWeight varchar(50),
lOfferedInSchool varchar(50),
lSchoolExam varchar(50),
cLevel varchar(50),
lCore varchar(50),
nMinMark varchar(50),
lMasterCourse varchar(50),
iMaster_CourseID varchar(50),
iOrder varchar(50),
iLV_ExternalCredentialID varchar(50),
lExcludeFromAve varchar(50),
iMarkLegendID varchar(50),
nGpaRuleNumber varchar(50),
iLV_SubjectID varchar(50),
iLV_DepartmentID varchar(50),
lWaivedCourse varchar(50),
lDuplicateCredit varchar(50),
lConflictMatrix varchar(50),
lTallyList varchar(50),
iLV_GovGradesID varchar(50),
iEffortLegendID varchar(50),
iObjectiveEffortLegendID varchar(50),
lStudyHall varchar(50),
lOffCampus varchar(50),
iSchoolID varchar(50) )
create table #tmpPOSTCourse(
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
cName varchar(50),
cCourseCode varchar(50),
iGovCourseID varchar(50),
cGovernmentCode varchar(50),
cDepartment varchar(50),
nFee varchar(50),
iLow_GradesID varchar(50),
iHigh_GradesID varchar(50),
nLowCredit varchar(50),
nHighCredit varchar(50),
mComment varchar(50),
iDepartmentExamsID varchar(50),
iLV_LanguageID varchar(50),
nAverageWeight varchar(50),
lOfferedInSchool varchar(50),
lSchoolExam varchar(50),
cLevel varchar(50),
lCore varchar(50),
nMinMark varchar(50),
lMasterCourse varchar(50),
iMaster_CourseID varchar(50),
iOrder varchar(50),
iLV_ExternalCredentialID varchar(50),
lExcludeFromAve varchar(50),
iMarkLegendID varchar(50),
nGpaRuleNumber varchar(50),
iLV_SubjectID varchar(50),
iLV_DepartmentID varchar(50),
lWaivedCourse varchar(50),
lDuplicateCredit varchar(50),
lConflictMatrix varchar(50),
lTallyList varchar(50),
iLV_GovGradesID varchar(50),
iEffortLegendID varchar(50),
iObjectiveEffortLegendID varchar(50),
lStudyHall varchar(50),
lOffCampus varchar(50),
iSchoolID varchar(50) )
declare cSchool cursor for
select tableName from @v_allDBs
order by tableName -- school code ordered lowest to highest
open cSchool
fetch cSchool into @v_currentChildDBName
while @@FETCH_STATUS = 0
begin
set @commandString =
'insert into #tmpPRECourse (
iDB,
cName,
cCourseCode,
iGovCourseID,
cGovernmentCode,
cDepartment,
nFee,
iLow_GradesID,
iHigh_GradesID,
nLowCredit,
nHighCredit,
mComment,
iDepartmentExamsID,
iLV_LanguageID,
nAverageWeight,
lOfferedInSchool,
lSchoolExam,
cLevel,
lCore,
nMinMark,
lMasterCourse,
iMaster_CourseID,
iOrder,
iLV_ExternalCredentialID,
lExcludeFromAve,
iMarkLegendID,
nGpaRuleNumber,
iLV_SubjectID,
iLV_DepartmentID,
lWaivedCourse,
lDuplicateCredit,
lConflictMatrix,
lTallyList,
iLV_GovGradesID,
iEffortLegendID,
iObjectiveEffortLegendID,
lStudyHall,
lOffCampus,
iSchoolID )
select
''pre'',
c.cName,
c.cCourseCode,
gc.cCode,
c.cGovernmentCode,
c.cDepartment,
c.nFee,
c.iLow_GradesID,
c.iHigh_GradesID,
c.nLowCredit,
c.nHighCredit,
c.mComment,
de.cName,
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(c.iLV_LanguageID),0) as ''iLV_LanguageID'',
c.nAverageWeight,
c.lOfferedInSchool,
c.lSchoolExam,
c.cLevel,
c.lCore,
c.nMinMark,
c.lMasterCourse,
c.iMaster_CourseID,
c.iOrder,
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(c.iLV_ExternalCredentialID),0) as ''iLV_ExternalCredentialID'',
c.lExcludeFromAve,
c.iMarkLegendID,
c.nGpaRuleNumber,
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(c.iLV_SubjectID), 0) as ''iLV_SubjectID'',
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(c.iLV_DepartmentID), 0) as ''iLV_DepartmentID'',
c.lWaivedCourse,
c.lDuplicateCredit,
c.lConflictMatrix,
c.lTallyList,
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(c.iLV_GovGradesID), 0) as ''iLV_GovGradesID'',
c.iEffortLegendID,
c.iObjectiveEffortLegendID,
c.lStudyHall,
c.lOffCampus,
isnull([' + @v_currentChildDBName + '].dbo.GetSchoolName(c.iSchoolID),0) as ''iSchoolID''
from [' + @v_currentChildDBName + '].dbo.course c
left join [' + @v_currentChildDBName + '].dbo.departmentexams de
on c.iDepartmentExamsID = de.iDepartmentExamsID
left join [' + @v_currentChildDBName + '].dbo.govcourse gc
on c.iGovCourseID = gc.iGovCourseID
'
exec(@commandString)
fetch next from cSchool into @v_currentChildDBName
end
close cSchool
deallocate cSchool
--initialize counter
set @v_SchoolCourseCounter = 1
declare cCourseRenumber cursor for
select iIdentity from #tmpPRECourse
order by iSchoolID, cName, cCourseCode
open cCourseRenumber
fetch cCourseRenumber into @v_SchoolCourseIndex
while @@FETCH_STATUS = 0
begin
--set the iIndex in the related History table to be the index of this counter
update #tmpPRECourse
set iIndex = @v_SchoolCourseCounter
where iIdentity = @v_SchoolCourseIndex
set @v_SchoolCourseCounter = @v_SchoolCourseCounter + 1
fetch next from cCourseRenumber into @v_SchoolCourseIndex
end
close cCourseRenumber
deallocate cCourseRenumber
-----
set @commandString =
'insert into #tmpPOSTCourse (
iDB,
cName,
cCourseCode,
iGovCourseID,
cGovernmentCode,
cDepartment,
nFee,
iLow_GradesID,
iHigh_GradesID,
nLowCredit,
nHighCredit,
mComment,
iDepartmentExamsID,
iLV_LanguageID,
nAverageWeight,
lOfferedInSchool,
lSchoolExam,
cLevel,
lCore,
nMinMark,
lMasterCourse,
iMaster_CourseID,
iOrder,
iLV_ExternalCredentialID,
lExcludeFromAve,
iMarkLegendID,
nGpaRuleNumber,
iLV_SubjectID,
iLV_DepartmentID,
lWaivedCourse,
lDuplicateCredit,
lConflictMatrix,
lTallyList,
iLV_GovGradesID,
iEffortLegendID,
iObjectiveEffortLegendID,
lStudyHall,
lOffCampus,
iSchoolID )
select
''post'',
c.cName,
c.cCourseCode,
gc.cCode,
c.cGovernmentCode,
c.cDepartment,
c.nFee,
c.iLow_GradesID,
c.iHigh_GradesID,
c.nLowCredit,
c.nHighCredit,
c.mComment,
de.cName,
isnull([' + @v_MasterDBName + '].dbo.GetLookupValue(c.iLV_LanguageID),0) as ''iLV_LanguageID'',
c.nAverageWeight,
c.lOfferedInSchool,
c.lSchoolExam,
c.cLevel,
c.lCore,
c.nMinMark,
c.lMasterCourse,
c.iMaster_CourseID,
c.iOrder,
isnull([' + @v_MasterDBName + '].dbo.GetLookupValue(c.iLV_ExternalCredentialID),0) as ''iLV_ExternalCredentialID'',
c.lExcludeFromAve,
c.iMarkLegendID,
c.nGpaRuleNumber,
isnull([' + @v_MasterDBName + '].dbo.GetLookupValue(c.iLV_SubjectID), 0) as ''iLV_SubjectID'',
isnull([' + @v_MasterDBName + '].dbo.GetLookupValue(c.iLV_DepartmentID), 0) as ''iLV_DepartmentID'',
c.lWaivedCourse,
c.lDuplicateCredit,
c.lConflictMatrix,
c.lTallyList,
isnull([' + @v_MasterDBName + '].dbo.GetLookupValue(c.iLV_GovGradesID), 0) as ''iLV_GovGradesID'',
c.iEffortLegendID,
c.iObjectiveEffortLegendID,
c.lStudyHall,
c.lOffCampus,
isnull([' + @v_MasterDBName + '].dbo.GetSchoolName(c.iSchoolID),0) as ''iSchoolID''
from [' + @v_MasterDBName + '].dbo.course c
left join [' + @v_MasterDBName + '].dbo.departmentexams de
on c.iDepartmentExamsID = de.iDepartmentExamsID
left join [' + @v_MasterDBName + '].dbo.govcourse gc
on c.iGovCourseID = gc.iGovCourseID
'
exec(@commandString)
set @v_SchoolCourseCounter = 1
declare cCourseRenumber cursor for
select iIdentity from #tmpPOSTCourse
order by iSchoolID, cName, cCourseCode
open cCourseRenumber
fetch cCourseRenumber into @v_SchoolCourseIndex
while @@FETCH_STATUS = 0
begin
--set the iIndex in the related History table to be the index of this counter
update #tmpPOSTCourse
set iIndex = @v_SchoolCourseCounter
where iIdentity = @v_SchoolCourseIndex
set @v_SchoolCourseCounter = @v_SchoolCourseCounter + 1
fetch next from cCourseRenumber into @v_SchoolCourseIndex
end
close cCourseRenumber
deallocate cCourseRenumber
-----
--EXECUTE dbo.CompareLists N'#tmpPRECourse', N'#tmpPOSTCourse', '1', 'Course'
--select * from #tmpPRECourse order by iIndex
--select * from #tmpPOSTCourse order by iindex
--at this point all the records from the various seperate db's are loaded into the table. now compare.
if( (select COUNT(*) from #tmpPOSTCourse) != 0 )
if( (select COUNT(*) from #tmpPRECourse) = (select COUNT(*) from #tmpPOSTCourse) )
begin
--select * from #tmpPOSTStudentHistory
EXECUTE dbo.CompareLists N'#tmpPRECourse', N'#tmpPOSTCourse', '1', 'Course'
end
else
begin
select 'Course record counts dont match' --will they ever? :S
select * from #tmpPRECourse order by iIndex
select * from #tmpPOSTCourse order by iIndex
end
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--------------------------------------------------------------------
--SCHOOL-------------------------------------------------------------
-------ATTENDANCEPRESENT--------------------------------------------
--------------------------------------------------------------------
--select * from attendancepresent
drop table #tmpPREAttendancePresent
drop table #tmpPOSTAttendancePresent
create table #tmpPREAttendancePresent(
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
dDate varchar(50),
iBlocksID varchar(50),
iClassID varchar(50),
iStaffID varchar(50),
lDaily varchar(50),
iSchoolID varchar(50)
)
create table #tmpPOSTAttendancePresent(
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
dDate varchar(50),
iBlocksID varchar(50),
iClassID varchar(50),
iStaffID varchar(50),
lDaily varchar(50),
iSchoolID varchar(50)
)
declare cSchool cursor for
select tableName from @v_allDBs
order by tableName -- school code ordered lowest to highest
open cSchool
fetch cSchool into @v_currentChildDBName
while @@FETCH_STATUS = 0
begin
set @commandString =
'insert into #tmpPREAttendancePresent (
iDB,
dDate,
iBlocksID,
iClassID,
iStaffID,
lDaily,
iSchoolID )
select
''pre'',
ap.dDate,
ap.iBlocksID,
isnull([' + @v_currentChildDBName + '].dbo.GetClassNameSection(ap.iClassID),ap.iClassID) as ''iClassID'',
isnull([' + @v_currentChildDBName + '].dbo.GetStaffName(ap.iStaffID),ap.iStaffID) as ''iStaffID'',
ap.lDaily,
isnull([' + @v_currentChildDBName + '].dbo.GetSchoolName(c.iSchoolID),c.iSchoolID) as ''iSchoolID''
from [' + @v_currentChildDBName + '].dbo.attendancepresent ap
left join [' + @v_currentChildDBName + '].dbo.class c
on ap.iClassID = c.iClassID
left join [' + @v_currentChildDBName + '].dbo.staff s
on ap.iStaffID = s.iStaffID
'
exec(@commandstring)
fetch next from cSchool into @v_currentChildDBName
end
close cSchool
deallocate cSchool
set @commandString =
'insert into #tmpPOSTAttendancePresent (
iDB,
dDate,
iBlocksID,
iClassID,
iStaffID,
lDaily,
iSchoolID )
select
''post'',
ap.dDate,
ap.iBlocksID,
isnull([' + @v_MasterDBName + '].dbo.GetClassNameSection(ap.iClassID),ap.iClassID) as ''iClassID'',
isnull([' + @v_MasterDBName + '].dbo.GetStaffName(ap.iStaffID),ap.iStaffID) as ''iStaffID'',
ap.lDaily,
isnull([' + @v_MasterDBName + '].dbo.GetSchoolName(c.iSchoolID),c.iSchoolID) as ''iSchoolID''
from [' + @v_MasterDBName + '].dbo.attendancepresent ap
left join [' + @v_MasterDBName + '].dbo.class c
on ap.iClassID = c.iClassID
left join [' + @v_MasterDBName + '].dbo.staff s
on ap.iStaffID = s.iStaffID
'
exec(@commandstring)
--PRE
set @v_SchoolAttendancePresentCounter = 1
declare cAttendancePresentRenumber cursor for
select iIdentity from #tmpPREAttendancePresent
order by iStaffID, iClassID, dDate, iBlocksID
open cAttendancePresentRenumber
fetch cAttendancePresentRenumber into @v_SchoolAttendancePresentIndex
while @@FETCH_STATUS = 0
begin
update #tmpPREAttendancePresent
set iIndex = @v_SchoolAttendancePresentCounter
where iIdentity = @v_SchoolAttendancePresentIndex
set @v_SchoolAttendancePresentCounter = @v_SchoolAttendancePresentCounter + 1
fetch next from cAttendancePresentRenumber into @v_SchoolAttendancePresentIndex
end
close cAttendancePresentRenumber
deallocate cAttendancePresentRenumber
-----
--POST
set @v_SchoolAttendancePresentCounter = 1
declare cAttendancePresentRenumber cursor for
select iIdentity from #tmpPOSTAttendancePresent
order by iStaffID, iClassID, dDate, iBlocksID
open cAttendancePresentRenumber
fetch cAttendancePresentRenumber into @v_SchoolAttendancePresentIndex
while @@FETCH_STATUS = 0
begin
update #tmpPOSTAttendancePresent
set iIndex = @v_SchoolAttendancePresentCounter
where iIdentity = @v_SchoolAttendancePresentIndex
set @v_SchoolAttendancePresentCounter = @v_SchoolAttendancePresentCounter + 1
fetch next from cAttendancePresentRenumber into @v_SchoolAttendancePresentIndex
end
close cAttendancePresentRenumber
deallocate cAttendancePresentRenumber
-----
--select * from attendancepresent
--select * from #tmpPREAttendancePresent order by iIndex
--select * from #tmpPOSTAttendancePresent order by iIndex
--at this point all the records from the various seperate db's are loaded into the table. now compare.
if( (select COUNT(*) from #tmpPOSTAttendancePresent) != 0 )
if( (select COUNT(*) from #tmpPREAttendancePresent) = (select COUNT(*) from #tmpPOSTAttendancePresent) )
begin
--select * from #tmpPOSTStudentHistory
EXECUTE dbo.CompareLists N'#tmpPREAttendancePresent', N'#tmpPOSTAttendancePresent', '1', 'AttendancePresent'
end
else
begin
select 'AttendancePresent record counts dont match' --will they ever? :S
select * from #tmpPREAttendancePresent order by iIndex
select * from #tmpPOSTAttendancePresent order by iIndex
end
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
---SCHOOL-------------------------------------------------------------
---------ATTENDANCEREASONS--------------------------------------------
----------------------------------------------------------------------
drop table #tmpPREAttendanceReasons
drop table #tmpPOSTAttendanceReasons
create table #tmpPREAttendanceReasons(
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
cName varchar(50),
cCode varchar(50),
lExcusable varchar(50),
iSchoolID varchar(50),
lTeacherLogicAccessible varchar(50),
CONSTRAINT PK_NAMESCHOOL UNIQUE NONCLUSTERED (cName, iSchoolID)
)
create table #tmpPOSTAttendanceReasons(
iIdentity int identity NOT NULL,
iIndex int,
iDB varchar(50),
cName varchar(50),
cCode varchar(50),
lExcusable varchar(50),
iSchoolID varchar(50),
lTeacherLogicAccessible varchar(50)
)
declare cSchool cursor for
select tableName from @v_allDBs
order by tableName -- school code ordered lowest to highest
open cSchool
fetch cSchool into @v_currentChildDBName
while @@FETCH_STATUS = 0
begin
set @commandString =
'insert into #tmpPREAttendanceReasons (
iDB,
cName,
cCode,
lExcusable,
iSchoolID,
lTeacherLogicAccessible )
select
''pre'',
ar.cName,
ar.cCode,
ar.lExcusable,
isnull([' + @v_currentChildDBName + '].dbo.GetSchoolName(ar.iSchoolID),0) as ''iSchoolID'',
ar.lTeacherLogicAccessible
from [' + @v_currentChildDBName + '].dbo.AttendanceReasons ar
where ar.cName COLLATE DATABASE_DEFAULT not in (
select cName
from #tmpPREAttendanceReasons
where iSchoolID = ar.iSchoolID )
'
exec(@commandstring)
fetch next from cSchool into @v_currentChildDBName
end
close cSchool
deallocate cSchool
set @commandString =
'insert into #tmpPOSTAttendanceReasons (
iDB,
cName,
cCode,
lExcusable,
iSchoolID,
lTeacherLogicAccessible )
select
''pre'',
ar.cName,
ar.cCode,
ar.lExcusable,
isnull([' + @v_MasterDBName + '].dbo.GetSchoolName(ar.iSchoolID),0) as ''iSchoolID'',
ar.lTeacherLogicAccessible
from [' + @v_MasterDBName + '].dbo.AttendanceReasons ar
'
exec(@commandstring)
--PRE
set @v_SchoolAttendanceReasonCounter = 1
declare cAttendanceReasonRenumber cursor for
select iIdentity from #tmpPREAttendanceReasons
order by iSchoolID, cName
open cAttendanceReasonRenumber
fetch cAttendanceReasonRenumber into @v_SchoolAttendanceReasonIndex
while @@FETCH_STATUS = 0
begin
update #tmpPREAttendanceReasons
set iIndex = @v_SchoolAttendanceReasonCounter
where iIdentity = @v_SchoolAttendanceReasonIndex
set @v_SchoolAttendanceReasonCounter = @v_SchoolAttendanceReasonCounter + 1
fetch next from cAttendanceReasonRenumber into @v_SchoolAttendanceReasonIndex
end
close cAttendanceReasonRenumber
deallocate cAttendanceReasonRenumber
-----
--POST
set @v_SchoolAttendanceReasonCounter = 1
declare cAttendanceReasonRenumber cursor for
select iIdentity from #tmpPOSTAttendanceReasons
order by iSchoolID, cName
open cAttendanceReasonRenumber
fetch cAttendanceReasonRenumber into @v_SchoolAttendanceReasonIndex
while @@FETCH_STATUS = 0
begin
update #tmpPOSTAttendanceReasons
set iIndex = @v_SchoolAttendanceReasonCounter
where iIdentity = @v_SchoolAttendanceReasonIndex
set @v_SchoolAttendanceReasonCounter = @v_SchoolAttendanceReasonCounter + 1
fetch next from cAttendanceReasonRenumber into @v_SchoolAttendanceReasonIndex
end
close cAttendanceReasonRenumber
deallocate cAttendanceReasonRenumber
-----
--at this point all the records from the various seperate db's are loaded into the table. now compare.
if( (select COUNT(*) from #tmpPOSTAttendanceReasons) != 0 )
if( (select COUNT(*) from #tmpPREAttendanceReasons) = (select COUNT(*) from #tmpPOSTAttendanceReasons) )
begin
--select * from #tmpPOSTStudentHistory
EXECUTE dbo.CompareLists N'#tmpPREAttendanceReasons', N'#tmpPOSTAttendanceReasons', '1', 'AttendanceReasons'
end
else
begin
select 'AttendanceReason record counts dont match' --will they ever? :S
select * from #tmpPREAttendanceReasons order by iIndex
select * from #tmpPOSTAttendanceReasons order by iIndex
end
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
--------------
Raw
mCompareOwnershipFields.sql
--make this variable later?
use eastcentral
GO
SET NOCOUNT ON
GO
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID ( 'dbo.CompareLists', 'P' ) IS NOT NULL
DROP PROCEDURE dbo.CompareLists;
GO
CREATE PROCEDURE dbo.CompareLists
@listPRE varchar(100),
@listPOST varchar(100),
@tmpGovNumber varchar(100)
AS
-- some assumptions this procedure makes:
-- iIndex is a unique identifier that must exist for each column in the table. it will be used to sort and determine which table should be compared to which.
-- tables compared must have the following columns: cgovernmentnumber, clastname, cfirstname, idb, ischoolid
SET NOCOUNT ON
declare @v_Derp int -- used to loop through records in cDerp
declare @v_DerpColumns varchar(100) -- used to loop through columns in cDerpColumns
declare @commandString varchar(5000)
set @commandString = '
declare cDerp cursor for
select COUNT(*) from [' + @listPRE + ']'
exec(@commandstring)
open cDerp
fetch cDerp into @v_Derp
while @@FETCH_STATUS = 0
begin
IF OBJECT_ID ( N'tmpDerp', 'U' ) IS NOT NULL
DROP table tmpDerp
set @commandString ='
select * INTO tmpDerp FROM (
select *
from [' + @listPRE + ']
where iIndex = (select MAX(iIndex) from [' + @listPRE + '])
UNION ALL
select *
from [' + @listPOST + ']
where iIndex = (select MAX(iIndex) from [' + @listPOST + '])
) un'
exec(@commandstring)
declare cDerpColumns cursor for
select column_name from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME like 'tmpDerp%'
and COLUMN_NAME not like 'iDB'
-----------fields removed due to known errors
and COLUMN_NAME not in ('iLV_TitleID')
open cDerpColumns
fetch cDerpColumns into @v_DerpColumns
while @@FETCH_STATUS = 0
begin
set @commandString ='
if (select isnull(MIN(CAST([' + @v_DerpColumns + '] as varchar)),0) from tmpDerp) != (select isnull(MAX(cast([' + @v_DerpColumns + '] as varchar)),0) from tmpDerp)
select
rtrim(cgovernmentnumber) as ''cgovernmentnumber'',
rtrim(clastname) as ''clastname'',
rtrim(cfirstname) as ''cfirstname'',
[' + @v_DerpColumns + '] as ''' + @v_DerpColumns + ''',
rtrim(ischoolid) as ''ischoolid'',
rtrim(iDB) as ''db''
from tmpDerp'
exec(@commandString)
fetch next from cDerpColumns into @v_DerpColumns
end
close cDerpColumns
deallocate cDerpColumns
set @commandString = '
delete from [' + @listPRE + ']
where iIndex = (select MAX(iIndex) from [' + @listPRE + '])
delete from [' + @listPOST + ']
where iIndex = (select MAX(iIndex) from [' + @listPOST + '])'
exec(@commandstring)
fetch next from cDerp into @v_Derp
end
close cDerp
deallocate cDerp
exec(@commandString)
GO
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------
--general
declare @v_commandString varchar(8000)
--student
declare @v_MasterDBName varchar(100) --upsized sql db
declare @v_OwnershipDBName varchar(100) --ownership file converted to a db
declare @v_currentChildDBName varchar(100) --used to loop through schools
declare @v_govnumber varchar(20) --used to loop through students
declare @v_columns varchar(100) --used to loop through the columns for averaging
--contact
declare @v_Contact int --used to loop through contacts (index)
declare @v_ContactColumns varchar(100) --used to loop through columns in the Contacts
--userstudent
declare @v_UserStudentField varchar(100) --used to loop through userstudent fields for building the temp table
declare @v_UserStudentPREFields_commandString varchar(8000) --command string for first half of a union statement getting built later
declare @v_UserStudentPOSTFields_commandString varchar(8000) -- command string for a second half of above union
declare @v_UserStudentColumns varchar(100) --used to loop through userstudent fields when comparing
--status
declare @v_StudentStatus int --used to loop through statuses (index)
declare @v_StudentStatusColumns varchar(100) --used to loop through columns for comparing
set @v_currentChildDBName = ''
set @v_govnumber = ''
set @v_MasterDBName = 'eastcentral'
set @v_OwnershipDBName = 'Ownership'
--need to add in some checks to make sure where are no duplicate govt numbers in Ownership file
-------------------
declare cSchool cursor for
select '3970' --this is just one single school. later could loop through all schools
open cSchool
fetch cSchool into @v_currentChildDBName
while @@FETCH_STATUS = 0
begin
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
--STUDENT-------------------------------------------------------------
----------------------------------------------------------------------
--the is the main loop for looking through students
declare cStudent cursor for
select [Column 7] from Ownership.dbo.StudentOwner
where [Column 2] = @v_currentChildDBName
and [Column 7] != ''
--order by [Column 7] DESC
open cStudent
fetch cStudent into @v_govnumber
while @@FETCH_STATUS = 0
begin
IF EXISTS(SELECT name FROM master..sysdatabases WHERE name = N'#tmpStudentTable')
drop table #tmpStudentTable
set @v_commandString = '
select * INTO #tmpStudentTable FROM
(
select
so.[Column 2],
so.[Column 7],
so.[Column 8],
so.[Column 9],
so.[Column 10],
s.cStudentNumber,
s.cFirstName,
s.cLastName,
s.cLegalFirstName,
s.cLegalLastName,
s.cLegalMiddleName,
isnull(dbo.GetLookupValue(s.iLV_GenderID),0) as ''iLV_GenderID'',
isnull(dbo.GetGradesName(s.iGradesID),0) as ''iGradesID'',
s.dBirthdate,
s.iTrackID,
isnull(dbo.getSchoolName(s.ischoolid),0) as ''iSchoolID'',
s.mMedical,
s.cPicturePath,
s.cGovernmentNumber,
s.cLockerNumber,
s.cLockerCombination,
s.cHealthCareNumber,
isnull(dbo.GetHomeroomName(s.iHomeroomID),0) as ''iHomeroomID'',
s.iAlt_HomeroomID,
s.lCurrent,
isnull(dbo.getlookupvalue(s.iLV_NextStatusID),0) as ''iLV_NextStatusID'',
isnull(dbo.GetGradesName(s.iNext_GradesID),0) as ''iNext_GradesID'',
isnull(dbo.GetHomeroomName(s.iNext_HomeroomID),0) as ''iNext_HomeroomID'',
s.iNext_Counselor_StaffID,
s.iNext_Advisor_StaffID,
s.iNext_SchoolID,
s.iNext_TrackID,
s.iAdvisor_StaffID,
s.iCounselor_StaffID,
s.cBarcodeNumber,
s.nGradYear,
s.nFeeCredit,
s.mFeeNotes,
isnull(dbo.GetLookupValue(s.iLV_ResidentID),0) as ''iLV_ResidentID'',
s.lStudentProtect,
s.mProtectComment,
s.iPrevious_SchoolID,
s.lRetainedGrade,
s.iStatusType,
s.lSped,
s.nSeatWidth,
s.cAcademicRecognition,
s.dAcademicRecDate,
s.mAcademicResult,
s.nMealPayType,
isnull(dbo.getlookupvalue(s.iLV_WaiverID),0) as ''iLV_WaiverID'',
isnull(dbo.getlookupvalue(s.iLV_CocurricularStatusID),0) as ''iLV_CocurricularStatusID'',
isnull(dbo.getlookupvalue(s.iLV_CocurricularPermitID),0) as ''iLV_CocurricularPermitID'',
s.dNotEligibleUntil,
s.dCocurricularAsOf,
isnull(dbo.getlookupvalue(s.iLV_CocurricularRegistrarID),0) as ''iLV_CocurricularRegistrar'',
s.mCocurricularProblems,
s.lPriorStatus,
s.gSpedID,
s.mSpedAccessList,
s.mEmail,
s.iCurrent_StudentID,
s.iSend_SchoolID,
s.iLastSpedForm,
s.iTransferID,
s.cUserName,
s.cPassword,
s.lAllowLogin,
s.lChangePassword,
s.cLDAPName,
s.mCellPhone,
isnull(dbo.getlookupvalue(s.iLV_NextOutStatusID),0) as ''iLV_NextOutStatusID'',
l.cApartment,
l.cHouseNo,
l.cStreet,
ISNULL(dbo.getlookupvalue(l.ilv_quadrantID),0) as ''ilv_quadrantID'',
ISNULL(dbo.getlookupvalue(l.iLV_CityID),0) as ''iLV_CityID'',
ISNULL(dbo.getlookupvalue(l.ilv_RegionID),0) as ''ilv_RegionID'',
l.iCountryID,
l.cPostalCode,
l.cPhone,
l.lUnlistedPhone,
ss.dInDate,
ss.dOutDate,
isnull(dbo.getlookupvalue(ss.iLV_VerificationID),0) as ''iLV_VerificationID'',
ss.lOutsideStatus,
isnull(dbo.getlookupvalue(ss.iLV_InStatusValueID),0) as ''iLV_InStatusValueID'',
isnull(dbo.getlookupvalue(ss.iLV_OutStatusValueID),0) as ''iLV_OutStatusValueID'',
ss.iOutside_TrackID,
DB_NAME() as ''iDB''
from dbo.Student s
full join Ownership.dbo.studentowner so
on s.cGovernmentNumber COLLATE DATABASE_DEFAULT = rtrim(so.[Column 7]) COLLATE DATABASE_DEFAULT
full join dbo.Location l
on s.iLocationID = l.iLocationID
full join dbo.studentstatus ss
on ss.istudentstatusid = s.iActive_StudentStatusID
where s.cLegalLastName COLLATE DATABASE_DEFAULT = rtrim(so.[Column 8]) COLLATE DATABASE_DEFAULT
and s.cLegalFirstName COLLATE DATABASE_DEFAULT = rtrim(so.[Column 9]) COLLATE DATABASE_DEFAULT
and s.dBirthdate = CAST(so.[Column 10] as datetime)
and so.[Column 7] is not null
and cGovernmentNumber = ''' + @v_govnumber + '''
UNION ALL
select
so.[Column 2],
so.[Column 7],
so.[Column 8],
so.[Column 9],
so.[Column 10],
s.cStudentNumber,
s.cFirstName,
s.cLastName,
s.cLegalFirstName,
s.cLegalLastName,
s.cLegalMiddleName,
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(s.iLV_GenderID),0) as ''iLV_GenderID'',
isnull([' + @v_currentChildDBName + '].dbo.GetGradesName(s.iGradesID),0) as ''iGradesID'',
s.dBirthdate,
s.iTrackID,
isnull([' + @v_currentChildDBName + '].dbo.getSchoolName(s.ischoolid),0) as ''iSchoolID'',
s.mMedical,
s.cPicturePath,
s.cGovernmentNumber,
s.cLockerNumber,
s.cLockerCombination,
s.cHealthCareNumber,
isnull([' + @v_currentChildDBName + '].dbo.GetHomeroomName(s.iHomeroomID),0) as ''iHomeroomID'',
s.iAlt_HomeroomID,
s.lCurrent,
isnull([' + @v_currentChildDBName + '].dbo.getlookupvalue(s.iLV_NextStatusID),0) as ''iLV_NextStatusID'',
isnull([' + @v_currentChildDBName + '].dbo.GetGradesName(s.iNext_GradesID),0) as ''iNext_GradesID'',
isnull([' + @v_currentChildDBName + '].dbo.GetHomeroomName(s.iNext_HomeroomID),0) as ''iNext_HomeroomID'',
s.iNext_Counselor_StaffID,
s.iNext_Advisor_StaffID,
s.iNext_SchoolID,
s.iNext_TrackID,
s.iAdvisor_StaffID,
s.iCounselor_StaffID,
s.cBarcodeNumber,
s.nGradYear,
s.nFeeCredit,
s.mFeeNotes,
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(s.iLV_ResidentID),0) as ''iLV_ResidentID'',
s.lStudentProtect,
s.mProtectComment,
s.iPrevious_SchoolID,
s.lRetainedGrade,
s.iStatusType,
s.lSped,
s.nSeatWidth,
s.cAcademicRecognition,
s.dAcademicRecDate,
s.mAcademicResult,
s.nMealPayType,
isnull([' + @v_currentChildDBName + '].dbo.getlookupvalue(s.iLV_WaiverID),0) as ''iLV_WaiverID'',
isnull([' + @v_currentChildDBName + '].dbo.getlookupvalue(s.iLV_CocurricularStatusID),0) as ''iLV_CocurricularStatusID'',
isnull([' + @v_currentChildDBName + '].dbo.getlookupvalue(s.iLV_CocurricularPermitID),0) as ''iLV_CocurricularPermitID'',
s.dNotEligibleUntil,
s.dCocurricularAsOf,
isnull([' + @v_currentChildDBName + '].dbo.getlookupvalue(s.iLV_CocurricularRegistrarID),0) as ''iLV_CocurricularRegistrarID'',
s.mCocurricularProblems,
s.lPriorStatus,
s.gSpedID,
s.mSpedAccessList,
s.mEmail,
s.iCurrent_StudentID,
s.iSend_SchoolID,
s.iLastSpedForm,
s.iTransferID,
s.cUserName,
s.cPassword,
s.lAllowLogin,
s.lChangePassword,
s.cLDAPName,
s.mCellPhone,
isnull([' + @v_currentChildDBName + '].dbo.getlookupvalue(s.iLV_NextOutStatusID),0) as ''iLV_NextOutStatusID'',
l.cApartment,
l.cHouseNo,
l.cStreet,
ISNULL([' + @v_currentChildDBName + '].dbo.getlookupvalue(l.ilv_quadrantID),0) as ''ilv_quadrantID'',
ISNULL([' + @v_currentChildDBName + '].dbo.getlookupvalue(l.iLV_CityID),0) as ''iLV_CityID'',
ISNULL([' + @v_currentChildDBName + '].dbo.getlookupvalue(l.ilv_RegionID),0) as ''ilv_RegionID'',
l.iCountryID,
l.cPostalCode,
l.cPhone,
l.lUnlistedPhone,
ss.dInDate,
ss.dOutDate,
isnull([' + @v_currentChildDBName + '].dbo.getlookupvalue(ss.iLV_VerificationID),0) as ''iLV_VerificationID'',
ss.lOutsideStatus,
isnull([' + @v_currentChildDBName + '].dbo.getlookupvalue(ss.iLV_InStatusValueID),0) as ''iLV_InStatusValueID'',
isnull([' + @v_currentChildDBName + '].dbo.getlookupvalue(ss.iLV_OutStatusValueID),0) as ''iLV_OutStatusValueID'',
ss.iOutside_TrackID,
''' + @v_currentChildDBName + ''' as ''iDB''
from [' + @v_currentChildDBName + '].dbo.Student s
full join Ownership.dbo.studentowner so
on s.cGovernmentNumber COLLATE DATABASE_DEFAULT = rtrim(so.[Column 7]) COLLATE DATABASE_DEFAULT
full join [' + @v_currentChildDBName + '].dbo.Location l
on s.iLocationID = l.iLocationID
full join [' + @v_currentChildDBName + '].dbo.studentstatus ss
on ss.istudentstatusid = s.iActive_StudentStatusID
where s.cLegalLastName COLLATE DATABASE_DEFAULT = rtrim(so.[Column 8]) COLLATE DATABASE_DEFAULT
and s.cLegalFirstName COLLATE DATABASE_DEFAULT = rtrim(so.[Column 9]) COLLATE DATABASE_DEFAULT
and s.dBirthdate = CAST(so.[Column 10] as datetime)
and cGovernmentNumber = ''' + @v_govnumber + '''
and so.[Column 7] is not null
) un'
exec(@v_CommandString)
----------------------------------------------------------------------------
--next average the columns in the temp table we just made, and poop out errors
declare cColumns cursor for
select column_name from tempdb.INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME like '#tmpStudentTable%'
and COLUMN_NAME not like 'iDB'
open cColumns
fetch cColumns into @v_columns
while @@FETCH_STATUS = 0
begin
set @v_commandString = '
if (select MIN(CAST([' + @v_columns + '] as varchar)) from #tmpStudentTable) != (select MAX(cast([' + @v_columns + '] as varchar)) from #tmpStudentTable)
select
rtrim([Column 7]) as ''Column 7'',
rtrim([Column 2]) as ''Column 2'',
rtrim([Column 8]) as ''Column 8'',
rtrim([Column 9]) as ''Column 9'',
rtrim(cast([' + @v_columns + '] as varchar)) as ''' + @v_columns + ''',
ischoolid,
iDB
from #tmpStudentTable'
exec(@v_commandstring)
fetch next from cColumns into @v_columns
end
close cColumns
deallocate cColumns
----------------------------------------------------------------------
----------------------------------------------------------------------
--STUDENT-------------------------------------------------------------
------CONTACT---------------------------------------------------------
----------------------------------------------------------------------
--/*
--select *
--from dbo.Contact c
--full join dbo.ContactRelation cr
-- on c.iContactID = cr.iContactID
--full join dbo.Student s
-- on s.iStudentID = cr.iStudentID
--*/
--cleanup
drop table #tempPOSTContactTable
drop table #tempPREContactTable
--create our temp tables
create table #tempPOSTContactTable (
iIndex int identity NOT NULL,
iLV_TitleID varchar(100),
cLastName varchar(100),
cFirstName varchar(100),
cBusPhone varchar(100),
ischoolid int,
mEmail varchar(100),
cUserName varchar(100),
cPassword varchar(100),
lChangePassword bit,
lAllowLogin bit,
mCellPhone varchar(100),
ilv_relationid varchar(100),
iContactPriority int,
lLivesWithStudent bit,
lMail bit,
mComment varchar(1000),
cgovernmentnumber varchar(50),
iDB varchar(50) )
create table #tempPREContactTable (
iIndex int identity NOT NULL,
iLV_TitleID varchar(100),
cLastName varchar(100),
cFirstName varchar(100),
cBusPhone varchar(100),
ischoolid int,
mEmail varchar(100),
cUserName varchar(100),
cPassword varchar(100),
lChangePassword bit,
lAllowLogin bit,
mCellPhone varchar(100),
ilv_relationid varchar(100),
iContactPriority int,
lLivesWithStudent bit,
lMail bit,
mComment varchar(1000),
cgovernmentnumber varchar(50),
iDB varchar(50) )
--select the POST data contact info into a temp table
insert into #tempPOSTContactTable (
iLV_TitleID,
cLastName,
cFirstName,
cBusPhone,
--ischoolid,
mEmail,
cUserName,
cPassword,
lChangePassword,
lAllowLogin,
mCellPhone,
ilv_relationid,
iContactPriority,
lLivesWithStudent,
lMail,
mComment,
cgovernmentnumber,
idb )
select
isnull(dbo.GetLookupValue(iLV_TitleID),0) as 'iLV_TitleID',
c.cLastName,
c.cFirstName,
--ilocationid, <--we will join this and compare individual fields in the location record by value
c.cBusPhone,
--c.iSchoolID, -- <-- evaluate this by value?
c.mEmail,
c.cUserName,
c.cPassword, -- <--- decrypt before comparison?
c.lChangePassword,
c.lAllowLogin,
c.mcellphone,
ISNULL(dbo.getlookupvalue(ilv_relationid),0) as 'ilv_relationid',
cr.iContactPriority,
cr.lLivesWithStudent,
cr.lMail,
cr.mComment,
@v_govnumber as 'cgovernmentnumber',
DB_NAME() as 'iDB'
from dbo.Contact c
full join dbo.ContactRelation cr
on c.iContactID = cr.iContactID
full join dbo.Student s
on s.iStudentID = cr.iStudentID
full join dbo.Location l
on l.iLocationID = c.iLocationID
where s.cGovernmentNumber = @v_govnumber
order by cLastName, cfirstname, icontactpriority --this is how we assume things are in the right order
set @v_commandString = '
insert into #tempPREContactTable (
iLV_TitleID,
cLastName,
cFirstName,
cBusPhone,
mEmail,
cUserName,
cPassword,
lChangePassword,
lAllowLogin,
mCellPhone,
ilv_relationid,
iContactPriority,
lLivesWithStudent,
lMail,
mComment,
cgovernmentnumber,
idb )
select
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(iLV_TitleID),0) as ''iLV_TitleID'',
c.cLastName,
c.cFirstName,
c.cBusPhone,
c.mEmail,
c.cUserName,
c.cPassword,
c.lChangePassword,
c.lAllowLogin,
c.mcellphone,
ISNULL([' + @v_currentChildDBName + '].dbo.getlookupvalue(ilv_relationid),0) as ''ilv_relationid'',
cr.iContactPriority,
cr.lLivesWithStudent,
cr.lMail,
cr.mComment,
''' + @v_govnumber + '''as ''cgovernmentnumber'',
''' + @v_currentChildDBName + ''' as ''iDB''
from [' + @v_currentChildDBName + '].dbo.Contact c
full join [' + @v_currentChildDBName + '].dbo.ContactRelation cr
on c.iContactID = cr.iContactID
full join [' + @v_currentChildDBName + '].dbo.Student s
on s.iStudentID = cr.iStudentID
full join [' + @v_currentChildDBName + '].dbo.Location l
on l.iLocationID = c.iLocationID
where s.cGovernmentNumber = ''' + @v_govnumber + '''
order by cLastName, cfirstname, icontactpriority'
exec(@v_commandString)
--at this point all the records from the various seperate db's are loaded into the table. now compare.
if( ((select COUNT(*) from #tempPOSTContactTable) != 0 ) )
begin
if( (select COUNT(*) from #tempPREContactTable) = (select COUNT(*) from #tempPOSTContactTable) )
begin
--select * from #tmpPOSTStudentHistory
EXECUTE dbo.CompareLists N'#tempPREContactTable', N'#tempPOSTContactTable', @v_govnumber
end
else
begin
select 'Pre and Post contact counts do not match!', @v_govnumber
select 'PRE', * from #tempPREContactTable
select 'POST', * from #tempPOSTContactTable
end
end
else
begin
select 'Post conversion resultset is empty!', @v_govnumber
select 'PRE', * from #tempPREContactTable
select 'POST', * from #tempPOSTContactTable
end
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
--STUDENT-------------------------------------------------------------
--------USERSTUDENT---------------------------------------------------
----------------------------------------------------------------------
--we dont know which fields will exist in both pre and post. so this table has to be generated dynamically
--grab all the userstudent fields that exist in both the pre and post DB.
--we can't compare fields that weren't in the Pre db.
drop table #t_fields
create table #t_fields ( cFieldName varchar(100) )
set @v_commandString = '
insert into #t_fields (cFieldName)
select fThis.cFieldName from dbo.Fields fThis
join [' + @v_currentChildDBName + '].dbo.Fields fThat on fThis.cFieldName = fThat.cFieldName
where (fThis.cAlias = ''UserStudent'' and fThat.cAlias = ''UserStudent'')
and (fthis.cFieldName not like ''istudentid'' and fthis.cFieldName not like ''ischoolid'')'
exec(@v_commandString)
--trim
update #t_Fields set cFieldName = RTRIM(cFieldName)
declare cUserstudentField cursor for
select cFieldName from #t_Fields
set @v_UserStudentPREFields_commandString = 'select * INTO tmpPREUserStudentTable FROM ( SELECT '
set @v_UserStudentPOSTFields_commandString = 'select * INTO tmpPOSTUserStudentTable FROM ( SELECT '
open cUserstudentField
fetch cUserstudentField into @v_UserStudentField
while @@FETCH_STATUS = 0
begin
--this needs to be cleaned up, its terrible
--check if this is a lookup value. if so, we need to get the actual value. checks for XLV in the display expr.
if( (select cLookupAlias from Datatypes where iDatatypesID = (select iDatatypesID from fields where cFieldName = @v_UserStudentField)) != 'LookupValues' )
begin
if( (select cLookupAlias from Datatypes where iDatatypesID = (select iDatatypesID from fields where cFieldName = @v_UserStudentField)) like 'School' ) --school stuff
begin
set @v_UserStudentPREFields_commandString = @v_UserStudentPREFields_commandString + ' isnull([' + @v_currentChildDBName + '].dbo.GetSchoolName(' + rtrim(@v_UserStudentField) + '),0) as ''' + rtrim(@v_UserStudentField) + ''', '
set @v_UserStudentPOSTFields_commandString = @v_UserStudentPOSTFields_commandString + ' isnull(dbo.GetSchoolName(' + rtrim(@v_UserStudentField) + '),0) as ''' + rtrim(@v_UserStudentField) + ''', '
end
else
begin
--check if something is a numeric value or char value
if( (select sys.columns.name from sys.tables
join sys.columns
on sys.tables.object_id = sys.columns.object_id
join sys.types
on sys.columns.system_type_id = sys.types.system_type_id
and sys.tables.name = 'UserStudent'
and sys.columns.name = @v_UserStudentField
and sys.types.name in ('tinyint', 'int', 'smallint', 'real', 'float', 'bit', 'decimal', 'numeric', 'bigint')) = @v_UserStudentField )
begin
--add the current column to both the PRE and POST commandstrings
set @v_UserStudentPREFields_commandString = @v_UserStudentPREFields_commandString + ' CAST(' + rtrim(@v_UserStudentField) + ' AS int) as ''' + @v_UserStudentField + ''', '
set @v_UserStudentPOSTFields_commandString = @v_UserStudentPOSTFields_commandString + ' CAST(' + rtrim(@v_UserStudentField) + ' AS int) as ''' + @v_UserStudentField + ''', '
end
else
begin
--add the current column to both the PRE and POST commandstrings
set @v_UserStudentPREFields_commandString = @v_UserStudentPREFields_commandString + rtrim(@v_UserStudentField) + ' as ''' + @v_UserStudentField + ''', '
set @v_UserStudentPOSTFields_commandString = @v_UserStudentPOSTFields_commandString + rtrim(@v_UserStudentField) + ' as ''' + @v_UserStudentField + ''', '
end
end
end
else
begin
--add the current column to both the PRE and POST commandstrings, but lookups
set @v_UserStudentPREFields_commandString = @v_UserStudentPREFields_commandString + ' isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(' + rtrim(@v_UserStudentField) + '),0) as ''' + @v_UserStudentField + ''', '
set @v_UserStudentPOSTFields_commandString = @v_UserStudentPOSTFields_commandString + ' isnull(dbo.GetLookupValue(' + rtrim(@v_UserStudentField) + '),0) as ''' + @v_UserStudentField + ''', '
end
fetch next from cUserstudentField into @v_UserStudentField
end
close cUserstudentField
deallocate cUserstudentField
----chop off the trailing spaces
SET @v_UserStudentPREFields_commandString = LEFT(@v_UserStudentPREFields_commandString, LEN(@v_UserStudentPREFields_commandString) - 1)
SET @v_UserStudentPOSTFields_commandString = LEFT(@v_UserStudentPOSTFields_commandString, LEN(@v_UserStudentPOSTFields_commandString) - 1)
set @v_UserStudentPREFields_commandString = @v_UserStudentPREFields_commandString +
', student.cgovernmentnumber, student.clastname, student.cfirstname, 1 as ''iIndex'', ''[' + @v_currentChildDBName + ']'' as ''iDB'', isnull([' + @v_currentChildDBName + '].dbo.GetSchoolName(student.ischoolid),0) as ''ischoolid'' FROM [' + @v_currentChildDBName + '].dbo.Userstudent
full join [' + @v_currentChildDBName + '].dbo.Student on userstudent.istudentid = student.istudentid
where student.cgovernmentnumber = ''' + @v_govnumber + ''') un'
set @v_UserStudentPOSTFields_commandString = @v_UserStudentPOSTFields_commandString +
', student.cgovernmentnumber, student.clastname, student.cfirstname, 1 as ''iIndex'', ''' + DB_NAME() + ''' as ''iDB'', isnull(dbo.GetSchoolName(student.ischoolid),0) as ''ischoolid'' FROM dbo.Userstudent
Join dbo.Student on userstudent.istudentid = student.istudentid
where student.cgovernmentnumber = ''' + @v_govnumber + ''') un'
--select @v_UserStudentPOSTFields_commandString
drop table tmpPREUserStudentTable
drop table tmpPOSTUserStudentTable
--select @v_UserStudentPREFields_commandString
--select @v_UserStudentPOSTFields_commandString
exec(@v_UserStudentPREFields_commandString)
exec(@v_UserStudentPOSTFields_commandString)
----at this point all the records from the various seperate db's are loaded into the table. now compare.
--if( (select COUNT(*) from tmpPOSTUserStudentTable) != 0 )
-- if( (select COUNT(*) from tmpPREUserStudentTable) = (select COUNT(*) from tmpPOSTUserStudentTable) )
-- begin
-- --select * from #tmpPOSTStudentHistory
-- EXECUTE dbo.CompareLists N'tmpPREUserStudentTable', N'tmpPOSTUserStudentTable', @v_govnumber
-- end
-- else
-- select 'Userstudent Counts dont match for student ' + @v_govnumber
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
---STUDENT------------------------------------------------------------
---------STATUS-------------------------------------------------------
----------------------------------------------------------------------
/*
going to process the studentstatus lines in the same way the status lines were processed.
read PRE and POST into seperate tables.
then loop through tables and compare each record with its corresponding record from the other table (determined by identity).
assumption is made that migrationtool is going to create status records in the same order (which is what it does as of 4.2.86- this may have to be changed to sort by date later).
*/
--create a temp table to hold both the pre and post statuses for this student
drop table #tmpPREStudentStatus
drop table #tmpPOSTStudentStatus
create table #tmpPREStudentStatus(
iIndex int identity NOT NULL,
dIndate smalldatetime,
dOutDate smalldatetime,
ilv_VerificationID varchar(100),
lOutsideStatus varchar(100),
iSchoolid varchar(100),
iLV_InStatusValueID varchar(100),
iLV_OutStatusValueID varchar(100),
iOutside_TrackID varchar(100),
cLastname varchar(100),
cFirstname varchar(100),
cGovernmentNumber varchar(100),
iDB varchar(100)
)
create table #tmpPOSTStudentStatus(
iIndex int identity NOT NULL,
dIndate smalldatetime,
dOutDate smalldatetime,
ilv_VerificationID varchar(100),
lOutsideStatus varchar(100),
iSchoolid varchar(100),
iLV_InStatusValueID varchar(100),
iLV_OutStatusValueID varchar(100),
iOutside_TrackID varchar(100),
cLastname varchar(100),
cFirstname varchar(100),
cGovernmentNumber varchar(100),
iDB varchar(100)
)
set @v_commandString ='
insert into #tmpPREStudentStatus (
dInDate,
dOutDate,
iLV_VerificationID,
lOutsideStatus,
iSchoolID,
iLV_InStatusValueID,
iLV_OutStatusValueID,
iOutside_TrackID,
cLastname,
cFirstname,
cGovernmentNumber,
iDB
)
select
ss.dInDate,
ss.dOutDate,
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(ss.iLV_VerificationID),0) as ''iLV_VerificationID'',
ss.lOutsideStatus,
isnull([' + @v_currentChildDBName + '].dbo.GetSchoolName(ss.iSchoolID),0) as ''iSchoolID'',
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(ss.iLV_InStatusValueID),0) as ''ss.iLV_InStatusValueID'',
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(ss.iLV_OutStatusValueID),0) as ''ss.iLV_OutStatusValueID'',
ss.iOutside_TrackID,
s.clastname,
s.cfirstname,
s.cgovernmentNumber,
''[' + @v_currentChildDBName + ']''
from [' + @v_currentChildDBName + '].dbo.StudentStatus ss
join [' + @v_currentChildDBName + '].dbo.Student s
on s.iStudentID = ss.iStudentID
where s.cGovernmentNumber = ''' + @v_govnumber + ''''
exec(@v_commandString)
insert into #tmpPOSTStudentStatus (
dInDate,
dOutDate,
iLV_VerificationID,
lOutsideStatus,
iSchoolID,
iLV_InStatusValueID,
iLV_OutStatusValueID,
iOutside_TrackID,
cLastname,
cFirstname,
cGovernmentNumber,
iDB
)
select
ss.dInDate,
ss.dOutDate,
isnull(dbo.GetLookupValue(ss.iLV_VerificationID),0) as 'iLV_VerificationID',
ss.lOutsideStatus,
isnull(dbo.GetSchoolName(ss.iSchoolID),0) as 'iSchoolID',
isnull(dbo.GetLookupValue(ss.iLV_InStatusValueID),0) as 'ss.iLV_InStatusValueID',
isnull(dbo.GetLookupValue(ss.iLV_OutStatusValueID),0) as 'ss.iLV_OutStatusValueID',
ss.iOutside_TrackID,
s.clastname,
s.cfirstname,
s.cgovernmentNumber,
DB_NAME()
from dbo.StudentStatus ss
join dbo.Student s
on s.iStudentID = ss.iStudentID
where s.cGovernmentNumber = @v_govnumber
--and ss.iSchoolID =
-- (select ischoolid from School where ccode COLLATE DATABASE_DEFAULT in
-- (select [Column 2] from Ownership.dbo.StudentOwner where [Column 7] COLLATE DATABASE_DEFAULT = s.cGovernmentNumber COLLATE DATABASE_DEFAULT) )
--check if the count of the status lines in pre and post is even the same.
--if not throw an error right now
if( (select COUNT(*) from #tmpPREStudentStatus) != (select COUNT(*) from #tmpPOSTStudentStatus) ) --dont loop unless the counts are the same
begin
select 'Status record counts are off for student ' + @v_govnumber --this isn't neccessarily bad. it could be that the student existed in SQL and new status lines were brought in from vfp. not always an error.
select * from #tmpPREStudentStatus
select * from #tmpPOSTStudentStatus
end
else --if the status record counts are NOT off, then we loop through them to verify values
begin
EXECUTE dbo.CompareLists N'#tmpPREStudentStatus', N'#tmpPOSTStudentStatus', @v_govnumber
end
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
---STUDENT------------------------------------------------------------
---------MULTICOMM----------------------------------------------------
----------------------------------------------------------------------
drop table #tmpPREStudentCommunication
drop table #tmpPOSTStudentCommunication
create table #tmpPREStudentCommunication (
iIndex int identity NOT NULL,
iDB varchar(50),
clastname varchar(50),
cfirstname varchar(50),
cgovernmentnumber varchar(50),
iStudentID int,
iLV_CommunicationTypeID varchar(20),
mInfo varchar(100),
lPrimary varchar(5),
lUnListed varchar(5),
iSchoolID varchar(50)
)
create table #tmpPOSTStudentCommunication (
iIndex int identity NOT NULL,
iDB varchar(50),
clastname varchar(50),
cfirstname varchar(50),
cgovernmentnumber varchar(50),
iStudentID int,
iLV_CommunicationTypeID varchar(20),
mInfo varchar(100),
lPrimary varchar(5),
lUnListed varchar(5),
iSchoolID varchar(50)
)
set @v_commandString = '
insert into #tmpPREStudentCommunication (
iDB,
clastname,
cfirstname,
cgovernmentnumber,
iStudentID,
iLV_CommunicationTypeID,
mInfo,
lPrimary,
lUnListed,
iSchoolid )
select
''pre'',
s.clastname,
s.cfirstname,
''' + @v_govnumber + ''',
s.iStudentID,
isnull([' + @v_currentChildDBName + '].dbo.GetLookupValue(iLV_CommunicationTypeID),0) as ''iLV_CommunicationTypeID'',
mInfo,
lPrimary,
lUnListed,
isnull(dbo.GetSchoolName(s.iSchoolID),0) as ''iSchoolID''
from [' + @v_currentChildDBName + '].dbo.Student s
join [' + @v_currentChildDBName + '].dbo.studentCommunication sc
on s.istudentid = sc.istudentid
where s.cgovernmentnumber = ''' + @v_govnumber + '''
order by iLV_CommunicationTypeID, mInfo, iSchoolID'
exec(@v_commandString)
set @v_commandString = '
insert into #tmpPOSTStudentCommunication (
iDB,
clastname,
cfirstname,
cgovernmentnumber,
iStudentID,
iLV_CommunicationTypeID,
mInfo,
lPrimary,
lUnListed,
iSchoolid )
select
''pre'',
s.clastname,
s.cfirstname,
''' + @v_govnumber + ''',
s.iStudentID,
isnull([' + @v_MasterDBName + '].dbo.GetLookupValue(iLV_CommunicationTypeID),0) as ''iLV_CommunicationTypeID'',
mInfo,
lPrimary,
lUnListed,
isnull(dbo.GetSchoolName(s.iSchoolID),0) as ''iSchoolID''
from [' + @v_MasterDBName + '].dbo.Student s
join [' + @v_MasterDBName + '].dbo.studentCommunication sc
on s.istudentid = sc.istudentid
where s.cgovernmentnumber = ''' + @v_govnumber + '''
order by iLV_CommunicationTypeID, mInfo, iSchoolID'
exec(@v_commandString)
--at this point all the records from the various seperate db's are loaded into the table. now compare.
if( (select COUNT(*) from #tmpPOSTStudentCommunication) != 0 )
if( (select COUNT(*) from #tmpPREStudentCommunication) = (select COUNT(*) from #tmpPOSTStudentCommunication) )
begin
--select * from #tmpPOSTStudentHistory
EXECUTE dbo.CompareLists N'#tmpPREStudentCommunication', N'#tmpPOSTStudentCommunication', @v_govnumber
end
else
begin
select 'Counts dont match for student: ' + @v_govnumber
select * from #tmpPREStudentCommunication
select * from #tmpPOSTStudentCommunication
end
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
----------------------------------------------------------------------
---STUDENT------------------------------------------------------------
---------MULTIADD----------------------------------------------------
----------------------------------------------------------------------
--cant really do this yet, need to find a dataset where its in it
fetch next from cStudent into @v_govnumber
end
close cStudent
deallocate cStudent
fetch next from cSchool into @v_currentChildDBName
end
close cSchool
deallocate cSchool
@othtim

othtim commented Mar 31, 2014

Copy link
Copy Markdown
Author

this will probably never be used again, for reference only

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment