Finalize view for Form B report

The view should contain all information needed for a comprehensive JasperReport Form B report.

The views created by Helical are:

‌

/****** Object:  View [warehouse].[TempSchoolAccreditationAssessment]    Script Date: 11/27/2019 1:36:07 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE VIEW [warehouse].[TempSchoolAccreditationAssessment]
AS

-- totals by subcriteria
Select inspID
, v.value('(../../../../version)[1]', 'int') Version
, v.value('(../../../name)[1]', 'nvarchar(50)') Category
, v.value('(../../../type)[1]', 'nvarchar(50)') CategoryType
, v.value('(../../id)[1]', 'nvarchar(50)') Standard
, v.value('(../../name)[1]', 'nvarchar(max)') StandardName
, v.value('(../id)[1]', 'nvarchar(50)') Criteria
, v.value('(../name)[1]', 'nvarchar(max)') CriteriaName
, v.value('id[1]', 'nvarchar(50)') SubCriteria
, v.value('name[1]', 'nvarchar(max)') SubCriteriaName
, v.value('interviewQuestions[1]', 'nvarchar(max)') SubCriteriaQuestion
, v.value('(answer/state)[1]', 'nvarchar(50)') Answer
, convert(decimal(5,1)
    , case v.value('(answer/state)[1]', 'nvarchar(50)')
        when 'POSITIVE' then 1
        when 'NEGATIVE' then 0
        else null
        end
    ) Score
, 1 MaxScore
from dbo.SchoolInspection SI
CROSS APPLY SI.inspXml.nodes('/survey/category/standard/criteria/subcriteria') as V(v)

UNION ALL
-- totals by criteria
Select inspID
, v.value('(../../../../version)[1]', 'int') Version
, v.value('(../../../name)[1]', 'nvarchar(50)') Category
, v.value('(../../../type)[1]', 'nvarchar(50)') CategoryType
, v.value('(../../id)[1]', 'nvarchar(50)') Standard
, v.value('(../../name)[1]', 'nvarchar(max)') StandardName
, v.value('(../id)[1]', 'nvarchar(50)') Criteria
, v.value('(../name)[1]', 'nvarchar(max)') CriteriaName
, null SubCriteria
, null SubCriteriaName
, null SubCriteriaQuestion
, null Answer
, v.value('.', 'decimal(4,1)') Score
, v.value('(../maxscore)[1]', 'int') MaxScore
from dbo.SchoolInspection SI
CROSS APPLY SI.inspXml.nodes('/survey/category/standard/criteria/score') as V(v)

UNION ALL
-- totals by standard
Select inspID
, v.value('(../../../version)[1]', 'int') Version
, v.value('(../../name)[1]', 'nvarchar(50)') Category
, v.value('(../../type)[1]', 'nvarchar(50)') CategoryType
, v.value('(../id)[1]', 'nvarchar(50)') Standard
, v.value('(../name)[1]', 'nvarchar(max)') StandardName
, null Criteria
, null CriteriaName
, null SubCriteria
, null SubCriteriaName
, null SubCriteriaQuestion
, null Answer
, v.value('(.)', 'decimal(5,1)') Score
, v.value('(../maxscore)[1]', 'int') MaxScore
from dbo.SchoolInspection SI
CROSS APPLY SI.inspXml.nodes('/survey/category/standard/score') as V(v)

UNION ALL
-- totals by category
Select inspID
, v.value('(../../version)[1]', 'int') Version
, v.value('(../name)[1]', 'nvarchar(50)') Category
, v.value('(../type)[1]', 'nvarchar(50)') CategoryType
, null Standard
, null StandardName
, null Criteria
, null CriteriaName
, null SubCriteria
, null SubCriteriaName
, null SubCriteriaQuestion
, null Answer
, v.value('(.)[1]', 'decimal(4,1)') Score
, v.value('(../maxscore)[1]', 'int') MaxScore
from dbo.SchoolInspection SI
CROSS APPLY SI.inspXml.nodes('/survey/category/score') as V(v)



GO

and

‌

/****** Object:  View [warehouse].[TempSchoolAccreditationDetails]    Script Date: 11/27/2019 1:36:23 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





Create View [warehouse].[TempSchoolAccreditationDetails]
As
        SELECT
        SI.schNo
        , SI.inspID
        , InspectionYear
        , BSS.surveyDimensionID
        , CategoryType
        , Category
        , Standard
        , StandardName
        , Criteria
        , CriteriaName
        , SubCriteria
        , SubCriteriaName
        , SubCriteriaQuestion
        , Answer
        , Score
        , MaxScore
    from [warehouse].[TempSchoolAccreditationAssessment] SSA
    INNER JOIN pInspectionRead.SchoolInspections SI
        ON SSA.inspID = SI.inspID
    LEFT JOIN warehouse.bestSurvey BSS
        ON BSS.schNo = SI.schNo
        AND BSS.SurveyYear = SI.InspectionYear
GO

The former based on the pInspectionRead.SchoolStandardAssessment which does not contain everything out of the box.

Comments (5)