Further enhancements to warehouse.GridMaker

[Brian Lewis]

Further enhancements to warehouse.gridMaker: 19 4 20

-- support more shortcuts for aggregations, and custom aggregations

-- support specifying years by absolute year, or relative to most recent year

-- list years ascending or descending

-- include or omit disaggregation by Age

-- include or omit column totals by grade

[Brian Lewis]

/****** Object:  StoredProcedure [warehouse].[gridMaker]    Script Date: 19/04/2019 9:25:11 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Brian Lewis
-- Create date: 18 4 2019
-- Description: Generate a 'grid' style dataset that dynamically names columns according to class level names
-- Examples:
-- exec warehouse.GridMaker                                             all years, totalled by school
-- exec warehouse.GridMaker 'D'                                         all years, totalled by district
-- exec warehouse.GridMaker 'A', 2014                                   year 2014, totalled by authority
-- exec warehouse.GridMaker 'A+', 2014                                  year 2014, totalled by authority, authority type, authority group
-- exec warehouse.GridMaker 'T', 2015, 2017                             years 2015-17, totalled by school type
-- exec warehouse.GridMaker 'D', null, null, null, 'Rep'                all years, repeaters totalled by district
-- exec warehouse.GridMaker 'D', null, null, 'SchoolTypeCode=''PS'''    all years, totalled by district, filtered for Primary schools

-- filter
-- exec warehouse.GridMaker 'D', 0, @filter='AuthorityGroup=''Government'''     totals by District for current year, govt schools only
-- exec warehouse.GridMaker 'S', 0, @filter='Age is null'               find schools with Null age records in current year

--- relative year
-- exec warehouse.GridMaker 'N', 0                                      national totals, for current year
-- exec warehouse.GridMaker 'N', -4, 0                                  national totals, last 5 years ascending
-- exec warehouse.GridMaker 'N', 0, -4                                  national totals, last 5 years descending

-- custom contents
-- exec warehouse.GridMaker 'S', 0, @ignoreAge = 1                      totals by school for current year (no age breakdown)
-- exec warehouse.GridMaker 'S', 0, @ignoreAge = 1, @rowTotalsOnly=1    totals by school for current year (no age breakdown, no class totals columns)
-- exec warehouse.GridMaker 'S', 0, null, null, null, 1 , 1             same as above, positional arguments

-- exec warehouse.GridMaker 'N', 0, @ignoreAge = 1, @rowTotalsOnly=1    
-- exec warehouse.GridMaker 'N', 0, null, null, null, 1 , 1             smallest possible dataset!

-- custom grouping
-- exec warehouse.GridMaker 'Island, District', 0                                       totals by Island & District for current year
-- exec warehouse.GridMaker 'Region', 0 ,null, null, null, 1                            totals by region for current year (no age breakdown)

-- =============================================
ALTER PROCEDURE [warehouse].[gridMaker]
    -- Add the parameters for the stored procedure here
    @groupBy nvarchar(200) = null       -- parameter to group by - use D for district, A for authority, T for school type,
                                        -- EN : electorate National EL: Electorate Local
                                        -- N = national totals (ie no disaggrgation other than year and age
                                        -- AT - authority type
                                        -- AG - authority group (ie Govt / Nongovt)
                                        -- A+ authority, authoty type, authority group - allows a report writer to insert subheadings and totals
                                        -- S or null = school
                                        -- any other string value is a collection of fields from warehouse.DimensionSchoolSurvey
                                        -- to ue for groupinh
    , @startYear int = null             -- if specified earliest year to report on
                                        -- use 0 or negative value for year relative to most recent year in warehouse 
    , @endYear int = null               -- if not null, last year to report on; defaults to startYear if null
                                        -- use 0 or negative value for year relative to most recent year in warehouse 
    , @filter nvarchar(max) = null      -- a where clause to execute against warehouse.enrol (EE) DimensionSchoolSurvey (DSS) before aggregating
    , @dataItem nvarchar(20) = 'Enrol'  -- the field to accumulate - can be any of the data values on warehouse.Enrol
                                        -- Enrol, Rep, Trin, Trout, Boarders, Disab, PSA
    , @ignoreAge int = 0                -- if 1, no aggregation by age
    , @rowTotalsOnly int = 0            -- if 1, do not include crosstab columns by level, show only totals

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

Select @groupBy = case
        when @groupBy in ('D', 'District', 'DistrictCode', 'District Code') then ', [District Code], [District]'
        when @groupBy in ('A', 'Authority', 'AuthorityCode', 'Authority Code', 'Auth Code') then ', [AuthorityCode], [Authority]'
        when @groupBy in ('A+', 'Authority+' ) then ', [AuthorityGroupCode], [AuthorityGroup], [AuthorityTypeCode], [AuthorityType], [AuthorityCode], [Authority]'
        when @groupBy in ('AT', 'AuthorityTypeCode', 'AuthorityType' ) then ', [AuthorityTypeCode], [AuthorityType]'
        when @groupBy in ('AG', 'AuthorityGroupCode', 'AuthorityGroup' ) then ', [AuthorityGroupCode], [AuthorityGroup]'
        when @groupBy in ('T', 'SchoolType', 'School Type Code', 'stCode', 'schType') then ', [SchoolTypeCode], [SchoolType]'
        when @groupBy in ('S', 'School', 'SchNo') or @groupBy is null then ', EE.SchNo, [School Name]' 
        when @groupBy in ('EN', 'Electorate National', 'National Electorate', 'Constituency') then ', [National Electorate No], [National Electorate]'
        when @groupBy in ('EL', 'Electorate Local', 'Local Electorate','Ward') then ', [Local Electorate No], [Local Electorate]'
        when @groupBy in ('N', 'Nation', 'National', 'None') then ''        -- no grouping
        else --- a custom expression
            ', ' + @groupBy
    end

-- data item defaults to Enrol, even if null is supplied
Select @dataItem = isnull(@dataItem, 'Enrol')

declare @minYear int, @maxYear int
declare @YearOrder nvarchar(20) = 'ASC'
declare @AgeTotals nvarchar(10) = ', Age'
declare @startYearS nvarchar(4)
declare @endYearS nvarchar(4)

if @ignoreAge = 1 begin
    Select @AgeTotals = ''
end

Select @minYear = min(SurveyYear)
    , @maxYear = max(SurveyYear)
    from warehouse.enrol

-- handle relative year
if @StartYear <= 0 begin
    Select @startYear = @maxYear + @startYear
end
if @endYear <= 0 begin
    Select @endYear = @maxYear + @endYear
end
Select @endYear = isnull(@endYear, @startYear)          -- if a start year is specified, but no end year, assume a single year
Select @StartYear = isnull(@startYear, @minYear)
, @endYear = isnull(@endYear, @maxYear)


if (@endYear < @startYear) begin
    declare @swap int
    Select @yearOrder = 'DESC'
    , @swap = @startYear
    , @StartYear = @endYear
    , @endYear = @swap
end

-- get string versions for convenience in building sql
Select @startYearS = convert(nvarchar(4), @StartYear)
,  @endYearS = convert(nvarchar(4), @endYear)

declare @s nvarchar(max) =
    'SELECT SurveyYear' + @AgeTotals + @groupBy + char(13) + char(10)

if (@rowTotalsOnly = 0) begin
    select @s = @s + ', sum(case when GenderCode = ''F'' AND ClassLevel =  ''' + codeCode + ''' then %VALUE% end) [' + codeCode + '_F] ' + char(13) + char(10)
     + ', sum(case when GenderCode = ''M'' AND ClassLevel =  ''' + codeCode + ''' then %VALUE% end) [' + codeCode + '_M] ' + char(13) + char(10)
     + ', sum(case when ClassLevel =  ''' + codeCode + ''' then %VALUE% end) [' + codeCode + '] ' + char(13) + char(10)
    From lkpLevels
    ORDER BY lvlYear
end

select @s = @s + ', sum(case when GenderCode = ''F'' then %VALUE% end) [Tot_F] ' + char(13) + char(10)
 + ', sum(case when GenderCode = ''M'' then %VALUE% end) [Tot_M] ' + char(13) + char(10)
 + ', sum(%VALUE%) [Tot] ' + char(13) + char(10)
 + ', sum(case when Estimate =  1 then %VALUE% end) [Est] ' + char(13) + char(10)

Select @s = @s + ' FROM warehouse.Enrol EE' + char(13) + char(10)
    + 'INNER JOIN warehouse.DimensionSchoolSurvey DSS ON EE.SurveyDimensionID = DSS.[Survey Id] ' + char(13) + char(10)
    + 'WHERE nullif(%VALUE%,0) is not null ' + char(13) + char(10)


    Select @s = @s + 'AND SurveyYear between ' + @StartYearS + ' AND ' + @endYearS + char(13) + char(10)

if (@filter is not null) begin
    Select @s = @s + 'AND ' + @filter + char(13) + char(10)
end

Select @s = @s + 'GROUP BY SurveyYear ' + @groupBy  + @AgeTotals + char(13) + char(10)
Select @s = @s + 'ORDER BY SurveyYear ' + @yearOrder +  @groupBy + @AgeTotals + char(13) + char(10)

Select @s = replace(@s, '%VALUE%', @dataItem)

print @s

exec sp_sqlexec @s

END
GO

[Brian Lewis]

@shraddha_tambe @ghachey

Pull Request #308 contains the stored proc; sql source code also shown above. Also this is now deployed in pineapples_develop, miemis production and fedemis production.

Probably the best way to get a feel for it is to execute the examples in the source code comments.

[Brian Lewis]

pull request #308 resolves