Change in school accreditation policy issue

[Ghislain Hachey]

During a meeting with Delihda that took place on 16 November 2021 it was discovered that there was a change in policy of how some school are accredited. Before 2019 ECE school especially in Yap were accredited individually. After 2019 they are accredited as part of their Primary counterpart. An example of this is:

• Elato ECE Center (accredited individually before 2019; accredited as part of Elato Elementary School since 2019)
• Elato Elementary School (accredited individually before 2019; accredited including the Elato ECE Centersince 2019)

This causes an over count in the total school accredited cumulatively after 2019 since the system includes all the ECE schools' latest accreditation status (23 additional of them) which are no longer part of the reporting after 2019.

There are a number of ways to address this sort of policy changes over the years all of which have advantages and disadvantages and require substantial amount of work. The quickest way to get the FedEMIS to report accreditation schools without over counting all ECE schools carried over in the cumulative analysis is to remove those records completely and save them in a separate location for posterity if they are ever needed.

FSM will have to decide whether they think it is worth the cost to add additional work into the FedEMIS to keep these records in place while omitting them in the analysis when the policy change took place. This is a substantial amount of work for little benefit (higher past data accuracy).

This issue is documented here for posterity and the reason behind the approach taken to deliver desired results under time constraint.

[Ghislain Hachey]

The approach I have taken here is to create additional tables to old the backed old data for those ECE schools. I save them then remove them from the official tables. Then I will do an SQL backup with a clear name (FEDEMIS-with-ece-school-accreditation-records-backed-up.sql) before removing those tables from the official database.

Here is the script.

‌

BEGIN TRANSACTION

-- Delete some old school accreditation records no longer "needed". 
-- Refer to https://bitbucket.org/softwords/pineapples/issues/1170/change-in-school-accreditation-policy
-- Those old records did not have any linked documents (photos) so no need to worry about that.

-- Create the backup tables
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SchoolAccreditationECESchoolsRecordBackup](
    [saID] [int] NOT NULL,
    [saL1] [int] NULL,
    [saL2] [int] NULL,
    [saL3] [int] NULL,
    [saL4] [int] NULL,
    [saT1] [int] NULL,
    [saT2] [int] NULL,
    [saT3] [int] NULL,
    [saT4] [int] NULL,
    [saD1] [int] NULL,
    [saD2] [int] NULL,
    [saD3] [int] NULL,
    [saD4] [int] NULL,
    [saN1] [int] NULL,
    [saN2] [int] NULL,
    [saN3] [int] NULL,
    [saN4] [int] NULL,
    [saF1] [int] NULL,
    [saF2] [int] NULL,
    [saF3] [int] NULL,
    [saF4] [int] NULL,
    [saS1] [int] NULL,
    [saS2] [int] NULL,
    [saS3] [int] NULL,
    [saS4] [int] NULL,
    [saCO1] [int] NULL,
    [saCO2] [int] NULL,
    [saLT1] [int] NULL,
    [saLT2] [int] NULL,
    [saLT3] [int] NULL,
    [saLT4] [int] NULL,
    [saT] [int] NULL,
    [saSchLevel] [varchar](10) NULL,
    [pCreateUser] [nvarchar](50) NULL,
    [pCreateDateTime] [datetime] NULL,
    [pEditUser] [nvarchar](50) NULL,
    [pEditDateTime] [datetime] NULL,
    [pRowversion] [timestamp] NOT NULL,
    [pCreateTag] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SchoolInspectionECESchoolsRecordBackup](
    [inspID] [int] IDENTITY(1,1) NOT NULL,
    [schNo] [nvarchar](50) NULL,
    [inspPlanned] [datetime] NULL,
    [inspStart] [datetime] NULL,
    [inspEnd] [datetime] NULL,
    [inspNote] [ntext] NULL,
    [inspBy] [nvarchar](500) NULL,
    [inspsetID] [int] NULL,
    [inspXml] [xml] NULL,
    [pCreateUser] [nvarchar](50) NULL,
    [pCreateDateTime] [datetime] NULL,
    [pEditUser] [nvarchar](50) NULL,
    [pEditDateTime] [datetime] NULL,
    [pRowversion] [timestamp] NULL,
    [inspResult] [nvarchar](50) NULL,
    [inspSource] [nvarchar](100) NULL,
    [pCreateTag] [uniqueidentifier] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SELECT COUNT(*) SchoolInspection FROM [SchoolInspection];
SELECT * FROM [SchoolInspection];

SELECT COUNT(*) SchoolAccreditation FROM [SchoolAccreditation];
SELECT * FROM [SchoolAccreditation];

-- The records to delete
SELECT * FROM [pInspectionRead].[SchoolInspections] WHERE schName LIKE '%ECE%' AND InspTypeCode = 'SCHOOL_ACCREDITATION' ORDER BY inspID;

-- Back them up first
SET IDENTITY_INSERT [SchoolInspectionECESchoolsRecordBackup] ON;
INSERT INTO [SchoolInspectionECESchoolsRecordBackup] ([inspID],[schNo],[inspPlanned],[inspStart],[inspEnd],[inspNote],[inspBy],[inspsetID],[inspXml],[pCreateUser],[pCreateDateTime],[pEditUser],[pEditDateTime],[inspResult],[inspSource],[pCreateTag])
SELECT [inspID],[schNo],[inspPlanned],[inspStart],[inspEnd],[inspNote],[inspBy],[inspsetID],[inspXml],[pCreateUser],[pCreateDateTime],[pEditUser],[pEditDateTime],[inspResult],[inspSource],[pCreateTag] FROM [SchoolInspection] WHERE inspID IN (SELECT inspID FROM [pInspectionRead].[SchoolInspections] WHERE schName LIKE '%ECE%' AND InspTypeCode = 'SCHOOL_ACCREDITATION');

SELECT * FROM [SchoolInspectionECESchoolsRecordBackup];

-- Delete the records from [SchoolInspections] using the updatable view. Those are the actual records that are in used by the EMIS
DELETE FROM [pInspectionRead].[SchoolInspections] WHERE schName LIKE '%ECE%' AND InspTypeCode = 'SCHOOL_ACCREDITATION';

-- Older school inspections were kept in their own respective tables dbo.SchoolAccreditation, dbo.QuarterlyReport, etc.
-- The records to delete actually came from there so delete there are well in case the procedure to migrate them again from the old
-- specific table to the new generic polymorphic SchoolInspection table is run again.

-- Back them up first
--SET IDENTITY_INSERT [SchoolAccreditationECESchoolsRecordBackup] ON;
INSERT INTO [SchoolAccreditationECESchoolsRecordBackup] ([saID],[saL1],[saL2],[saL3],[saL4],[saT1],[saT2],[saT3],[saT4],[saD1],[saD2],[saD3],[saD4],[saN1],[saN2],[saN3],[saN4],[saF1],[saF2],[saF3],[saF4],[saS1],[saS2],[saS3],[saS4],[saCO1],[saCO2],[saLT1],[saLT2],[saLT3],[saLT4],[saT],[saSchLevel],[pCreateUser],[pCreateDateTime],[pEditUser],[pEditDateTime],[pCreateTag])
SELECT [saID],[saL1],[saL2],[saL3],[saL4],[saT1],[saT2],[saT3],[saT4],[saD1],[saD2],[saD3],[saD4],[saN1],[saN2],[saN3],[saN4],[saF1],[saF2],[saF3],[saF4],[saS1],[saS2],[saS3],[saS4],[saCO1],[saCO2],[saLT1],[saLT2],[saLT3],[saLT4],[saT],[saSchLevel],[pCreateUser],[pCreateDateTime],[pEditUser],[pEditDateTime],[pCreateTag] FROM SchoolAccreditation WHERE saID IN (SELECT inspID FROM [pInspectionRead].[SchoolInspections] WHERE schName LIKE '%ECE%' AND InspTypeCode = 'SCHOOL_ACCREDITATION');

DELETE FROM SchoolAccreditation WHERE saID IN (SELECT inspID FROM [pInspectionRead].[SchoolInspections] WHERE schName LIKE '%ECE%' AND InspTypeCode = 'SCHOOL_ACCREDITATION');

SELECT COUNT(*) FROM [SchoolInspection];
SELECT * FROM [SchoolInspection];

SELECT COUNT(*) SchoolAccreditation FROM [SchoolAccreditation];
SELECT * FROM [SchoolAccreditation];

SELECT * FROM [pInspectionRead].[SchoolInspections] WHERE schName LIKE '%ECE%' AND InspTypeCode = 'SCHOOL_ACCREDITATION' ORDER BY inspID;

SELECT * FROM [SchoolInspectionECESchoolsRecordBackup];

ROLLBACK

‌

[Ghislain Hachey]

Instead, I’ve decided to simply keep those DB tables containing the ECE schools data which is no longer needed in reporting.