Warehouse school accreditation add view

[Ghislain Hachey]

Review and include in the warehouse as it is a really important view to have in a user friendly (no query required). There was a small error in my view. Updated below.

USE [MIEMIS-TEST]
GO

/****** Object:  View [warehouse].[AccreditationClassic]    Script Date: 07/04/2020 3:46:56 pm ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ==================================================
-- Author:      Ghislain Hachey
-- Create date: 7 April 2020
-- Description: A view providing a data in classic format
-- ==================================================
-- This view is designed to provide a familiar data format for FSM/RMI. It is similar
-- to the table ware.Accreditations but already links with bestSurvey and dimensionSchoolSurvey
-- to provide users everything in a single "table". With this view users can pull 
-- all data for school accreditation like it was in the classic school accreditation
-- analysis spreadsheet, but with the added benefit of more consistent data from the
-- EMIS, historical analysis (not before possible), and more powerful aggregations
-- ==================================================
CREATE VIEW [warehouse].[AccreditationClassic]
AS
SELECT [School Name]
      ,[District]
      ,[Island]
      ,[Region]
      ,[Local Electorate]
      ,[National Electorate]
      ,[Authority]
      ,[AuthorityType]
      ,[AuthorityGroup]
      ,[AuthorityGovt]
      ,[SchoolType]
      ,[Year Established]
      ,[Year Closed]
      ,BI.[inspID]
      ,BI.[schNo]
      ,A.[StartDate]
      ,[EndDate]
      ,A.[InspectionYear]
      ,[SurveyYear]
      ,A.[InspectionResult]   
      ,[SE.1.1]
      ,[SE.1.2]
      ,[SE.1.3]
      ,[SE.1.4]
      ,[SE.1]
      ,[SE.2.1]
      ,[SE.2.2]
      ,[SE.2.3]
      ,[SE.2.4]
      ,[SE.2]
      ,[SE.3.1]
      ,[SE.3.2]
      ,[SE.3.3]
      ,[SE.3.4]
      ,[SE.3]
      ,[SE.4.1]
      ,[SE.4.2]
      ,[SE.4.3]
      ,[SE.4.4]
      ,[SE.4]
      ,[SE.5.1]
      ,[SE.5.2]
      ,[SE.5.3]
      ,[SE.5.4]
      ,[SE.5]
      ,[SE.6.1]
      ,[SE.6.2]
      ,[SE.6.3]
      ,[SE.6.4]
      ,[SE.6]
      ,[CO.1]
      ,[CO.2]
      ,[Level1]
      ,[Level2]
      ,[Level3]
      ,[Level4]
  FROM [warehouse].[Accreditations] AS A
  INNER JOIN [warehouse].[BestInspection] AS BI ON A.inspID = BI.inspID
  INNER JOIN [warehouse].[dimensionSchoolSurvey] AS DSS ON DSS.[Survey ID] = BI.SurveyDimensionID
  WHERE BI.InspectionTypeCode = 'SCHOOL_ACCREDITATION'
GO

‌

[Ghislain Hachey]

Another important view we need to make things more user friendly for data managers is the below. In fact, we should always have this version that already contains all the possible school based disaggregations. I’m open to better naming conventions for those views. (e.g. EnrolSchoolAllDisaggregations?)

‌

USE [MIEMIS-TEST]
GO

/****** Object:  View [warehouse].[EnrolSchoolDimension]    Script Date: 09/04/2020 2:39:25 pm ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Ghislain Hachey
-- Create date: 2019
-- Description: Warehouse - Enrolment data by school with the whole set of possible disaggregations
--
-- This is a simple consolidation of warehouse.enrol to group by school.
--
-- The family of related objects:
-- Base data:
--      warehouse.Enrol
-- Consolidations:
--      warehouse.tableEnrol
--      warehouse.EnrolDistrict
--      warehouse.EnrolNation
--      warehouse.EnrolIsland
--      warehouse.EnrolRegion
--      warehouse.EnrolLocalElectorate

-- Consolitations including population: (these do not break down by class level)
--      warehouse.enrolPopDistrict
--      warehouse.EnrolPopNation

-- 'Report' versions ie denormalised by Gender
--      warehouse.EnrolR
--      warehouse.EnrolIslandR
--      warehouse.EnrolRegionR
--      warehouse.EnrolLocalElectorateR
--      warehouse.EnrolDistrictR
--      warehouse.EnrolNationR
--      warehouse.enrolPopDistrictR
--      warehouse.EnrolPopNationR
-- =============================================
ALTER VIEW
[warehouse].[EnrolSchoolDimension]
AS
Select SurveyYear
, [School No]
, [School Name]
, [Local Electorate No] LocalElectorateNo
, [Local Electorate]
, [National Electorate No] NationalElectorateNo
, [National Electorate]
, [District Code] DistrictCode
, District
, [Island Code] IslandCode
, Island
, [Region Code] RegionCode
, Region
, AuthorityCode
, Authority
, AuthorityTypeCode
, AuthorityType
, AuthorityGroupCode
, AuthorityGroup
, SchoolTypeCode
, SchoolType

, ClassLevel
, Age
, GenderCode

, sum(Enrol) Enrol
, sum(Rep) Rep
, sum(Trin) Trin
, sum(Trout) Trout
, sum(Boarders) Boarders
, sum(Disab) Disab
, sum(Dropout) Dropout
, sum(PSA) PSA

from warehouse.Enrol E
    LEFT JOIN warehouse.DimensionSchoolSurvey DSS
        ON E.SurveyDimensionID = DSS.[Survey ID]
GROUP BY
SurveyYear
, [School No]
, [School Name]
, [Local Electorate No]
, [Local Electorate]
, [National Electorate No]
, [National Electorate]
, [District Code]
, District
, [Island Code]
, Island
, [Region Code]
, Region
, AuthorityCode
, Authority
, AuthorityCode
, Authority
, AuthorityTypeCode
, AuthorityType
, AuthorityGroupCode
, AuthorityGroup
, SchoolTypeCode
, SchoolType
, ClassLevel
, Age
, GenderCode



GO

‌

‌