FSM School Accreditation Foundation Work

[Ghislain Hachey]

So for quarterlyreport I included all the quarters in InspectionSet (2016/Q1, etc.). But for FSM they have school accreditation which is a form of school inspection I believe. But they don't seem to have a regular interval. I have some data for with an attached year. Seems like that they as much as possible in a given year and simply continue next year without necessarily having done inspections on all schools every year. My understanding is that I can simply use the year in inspectionset and school may or may not have a year inspection for any given year. I think it should all work out the same in the end. Any thoughts here?

[Ghislain Hachey]

Here the basic SQL needed to continue.

--- New table to hold school accreditation data
CREATE TABLE SchoolAccreditation
    (  
        saID INT PRIMARY KEY, --- Key set to same as inspID
        saL1 INT,
        saL2 INT,
        saL3 INT,
        saL4 INT,
        saT1 INT,
        saT2 INT,
        saT3 INT,
        saT4 INT,
        saD1 INT,
        saD2 INT,
        saD3 INT,
        saD4 INT,
        saN1 INT,
        saN2 INT,
        saN3 INT,
        saN4 INT,
        saF1 INT,
        saF2 INT,
        saF3 INT,
        saF4 INT,
        saS1 INT,
        saS2 INT,
        saS3 INT,
        saS4 INT,
        saCO1 INT,
        saCO2 INT,
        saLT1 INT,
        saLT2 INT,
        saLT3 INT,
        saLT4 INT,
        saT INT,
        saSchLevel VARCHAR(10),
    pCreateUser NVARCHAR(50),
    pCreateDateTime DATETIME,
    pEditUser NVARCHAR(50),
    pEditDateTime DATETIME,
    pRowversion TIMESTAMP NOT NULL,
    pCreateTag uniqueidentifier
        )
    GO

ALTER TABLE SchoolAccreditation ADD FOREIGN KEY (saID) REFERENCES SchoolInspection(inspID) ON DELETE NO ACTION ON UPDATE NO ACTION
GO

--- View pInspectionRead.SchoolAccreditation of school accreditation
CREATE VIEW pInspectionRead.SchoolAccreditations
WITH VIEW_METADATA
AS
SELECT SI.inspID, SI.schNo, SI.inspStart AS StartDate, SI.inspEnd AS EndDate, SI.inspNote AS Note, SI.inspBy AS InspectedBy, SA.saID AS saID, SA.saL1 AS L1, SA.saL2 AS L2, SA.saL3 AS L3, SA.saL4 AS L4, SA.saT1 AS T1, SA.saT2 AS T2, SA.saT3 AS T3, SA.saT4 AS T4, SA.saD1 AS D1, SA.saD2 AS D2, SA.saD3 AS D3, SA.saD4 AS D4, SA.saN1 AS N1, SA.saN2 AS N2, SA.saN3 AS N3, SA.saN4 AS N4, SA.saF1 AS F1, SA.saF2 AS F2, SA.saF3 AS F3, SA.saF4 AS F4, SA.saS1 AS S1, SA.saS2 AS S2, SA.saS3 AS S3, SA.saS4 AS S4, SA.saCO1 AS CO1, SA.saCO2 AS CO2, SA.saLT1 AS LT1, SA.saLT2 AS LT2, SA.saLT3 AS LT3, SA.saLT4 AS LT4, SA.saT AS T, SA.saSchLevel AS SchLevel, ISET.inspsetName AS InspYear, ISET.inspsetType, pEditUser, pEditDateTime, pCreateUser, pCreateDateTime, pRowversion, pCreateTag
FROM dbo.SchoolInspection AS SI
    LEFT OUTER JOIN
    dbo.InspectionSet AS ISET ON SI.inspsetID = ISET.inspsetID
    LEFT OUTER JOIN
    dbo.SchoolAccreditation AS SA ON SI.inspID = SA.saID
    LEFT OUTER JOIN
    dbo.lkpInspectionTypes AS IT ON ISET.inspsetType = IT.intyCode
WHERE ISET.inspsetType = 'SCHACCR'

--- Small change to StoredProcedure [pSchoolRead].[SchoolReadEx] (see last SELECT statement)

/****** Object:  StoredProcedure [pSchoolRead].[SchoolReadEx]    Script Date: 7/19/2016 2:16:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Brian Lewis
-- Create date: 13 08 2015
-- Description: Read multiple reordsets about school
-- =============================================
ALTER PROCEDURE [pSchoolRead].[SchoolReadEx]
 -- Add the parameters for the stored procedure here
 @SchoolNo nvarchar(50)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 SELECT * from Schools WHERE schNo = @schoolNo
 -- survey summary data
 exec pEnrolmentRead.schoolAnnualSummary  @schoolNo
 Select * from pExamRead.SchoolExams WHERE schNo = @schoolNo
 Select * from pInspectionRead.SchoolInspections WHERE schNo = @schoolNo
 Select * from pInspectionRead.QuarterlyReports WHERE schNo = @schoolNo
 Select * from pInspectionRead.SchoolAccreditations WHERE schNo = @schoolNo
END

[Ghislain Hachey]

I've added the following stored procedures also:

• [pSurveyRead].[SchoolAccreditationFilterIDs]
• [pSurveyRead].[SchoolAccreditationFilterPaged]
• [pSurveyRead].[SchoolAccreditationRead]

But thinking about how to better share SQL changed. Maybe we could have a secondary DB schema along with where the master schema at VCNSQL90.webhost4life.com. That one developers could sync their changes to using SQLDelta for review. Maybe call it pineapples_develop

[Ghislain Hachey]

This is complete. Need to port to RMI and have Brian review. Further refinements and fixes will come as their own issues.