Data warehouse new views

The following two views were added in production for FSM reporting needs. Please verify them and integrate them into the repository and master schema.

USE [FEDEMIS]
GO

/****** Object:  View [warehouse].[EnrolLocalElectorate]    Script Date: 6/24/2019 10:26:45 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:      Brian Lewis
-- Create date: 2019
-- Description: Warehouse - Enrolment data by local electorate
--
-- This is a simple consoilidation of warehouse.enrol to group by local electorate
-- which is an attribute of schools.
--
-- The family of related objects:
-- Base data:
--      warehouse.Enrol
-- Consolidations:
--      warehouse.tableEnrol
--      warehouse.EnrolDistrict
--      warehouse.EnrolNation
--      warehouse.EnrolIsland
--      warehouse.EnrolRegion

-- 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].[EnrolLocalElectorate]
AS
Select SurveyYear
, [Local Electorate No] LocalElectorateNo
, [Local Electorate]
, [District Code] DistrictCode
, District

, 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
, [Local Electorate No]
, [Local Electorate]
, [District Code]
, District
, ClassLevel
, Age
, GenderCode


GO

USE [FEDEMIS]
GO

/****** Object:  View [warehouse].[EnrolLocalElectorateR]    Script Date: 6/24/2019 10:27:17 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:      Ghislain Hachey
-- Create date: 2019
-- Description: Warehouse - Enrolment data by local electorate
--
-- This is a simple consolidation of warehouse.enrol to group by Local Electorate
-- which is an attribute of Schools.
-- in report format
--
-- The family of related objects:
-- Base data:
--      warehouse.Enrol
-- Consolidations:
--      warehouse.tableEnrol
--      warehouse.EnrolDistrict
--      warehouse.EnrolNation
--      warehouse.EnrolIsland
--      warehouse.EnrolRegion

-- 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].[EnrolLocalElectorateR]
AS
Select SurveyYear
, [Local Electorate No] LocalElectorateNo
, [Local Electorate]
, [District Code] DistrictCode
, District

, 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
, [Local Electorate No]
, [Local Electorate]
, [District Code]
, District
, ClassLevel
, Age
, GenderCode


GO

Comments (1)