Add view [warehouse].[EnrolSchoolDimension] and [warehouse].[EnrolSchoolDimensionR]

USE [FEDEMIS-TEST]
GO

/****** Object:  View [warehouse].[EnrolSchoolDimension]    Script Date: 7/30/2019 3:58:13 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 consoilidation 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
-- =============================================
CREATE 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

‌

USE [FEDEMIS-TEST]
GO

/****** Object:  View [warehouse].[EnrolSchoolDimensionR]    Script Date: 7/30/2019 3:58:13 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 in report format
--
-- This is a simple consoilidation 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.EnrolSchoolType
--      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.EnrolSchoolTypeR
--      warehouse.EnrolLocalElectorateR
--      warehouse.EnrolDistrictR
--      warehouse.EnrolNationR
--      warehouse.enrolPopDistrictR
--      warehouse.EnrolPopNationR
-- =============================================
CREATE VIEW
[warehouse].[EnrolSchoolDimensionR]
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

, sum(case when GenderCode = 'M' then Enrol end) EnrolM
, sum(case when GenderCode = 'F' then Enrol end) EnrolF
, sum(Enrol) Enrol

, sum(case when GenderCode = 'M' then Rep end) RepM
, sum(case when GenderCode = 'F' then Rep end) RepF
, sum(Rep) Rep

, sum(case when GenderCode = 'M' then Trin end) TrinM
, sum(case when GenderCode = 'F' then Trin end) TrinF
, sum(Trin) Trin

, sum(case when GenderCode = 'M' then Trout end) TroutM
, sum(case when GenderCode = 'F' then Trout end) TroutF
, sum(Trout) Trout

, sum(case when GenderCode = 'M' then Boarders end) BoardersM
, sum(case when GenderCode = 'F' then Boarders end) BoardersF
, sum(Boarders) Boarders

, sum(case when GenderCode = 'M' then Disab end) DisabM
, sum(case when GenderCode = 'F' then Disab end) DisabF
, sum(Disab) Disab

, sum(case when GenderCode = 'M' then Dropout end) DropoutM
, sum(case when GenderCode = 'F' then Dropout end) DropoutF
, sum(Dropout) Dropout

, sum(case when GenderCode = 'M' then PSA end) PSAM
, sum(case when GenderCode = 'F' then PSA end) PSAF
, 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



GO

Comments (3)