- changed status to closed
Synchronize chema changes to master database , including issue 153
Issue #176
closed
This is the delta script applied 21 8 2016:
--
-- Script to Update dbo.lkpLevels in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Updating dbo.lkpLevels Table'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'dbo.lkpLevels Table Updated Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Update dbo.lkpLevels Table'
END
GO
--
-- Script to Create dbo.QuarterlyReport in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Creating dbo.QuarterlyReport Table'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
CREATE TABLE [dbo].[QuarterlyReport] (
[qrID] [int] NOT NULL,
[qrNumDaysOpen] [real] NULL,
[qrAggDaysAtt] [real] NULL,
[qrAggDaysAbs] [real] NULL,
[qrAggDaysMem] as ([qrAggDaysAtt]+[qrAggDaysAbs]),
[qrAvgDailyAtt] as ([qrAggDaysAtt]/[qrNumDaysOpen]),
[qrAvgDailyMem] [real] NULL,
[qrTotEnrolToDate] [int] NULL,
[qrPupilEnrolLastDay] [int] NULL
)
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
ALTER TABLE [dbo].[QuarterlyReport] ADD CONSTRAINT [PK__Quarterl__FF4B98F4B72E5C26] PRIMARY KEY CLUSTERED ([qrID])
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'dbo.QuarterlyReport Table Added Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Add dbo.QuarterlyReport Table'
END
GO
--
-- Script to Update dbo.SurveyYearRank in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Updating dbo.SurveyYearRank Table'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
ALTER TABLE [dbo].[SurveyYearRank]
ADD [rankEnrolF] [smallint] NULL
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
ALTER TABLE [dbo].[SurveyYearRank]
ADD [rankEnrolM] [smallint] NULL
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'dbo.SurveyYearRank Table Updated Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Update dbo.SurveyYearRank Table'
END
GO
--
-- Script to Update dbo.TeacherUnidentified in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Updating dbo.TeacherUnidentified Table'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
GRANT DELETE ON OBJECT::[dbo].[TeacherUnidentified] TO [pSurveyWrite]
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
GRANT INSERT ON OBJECT::[dbo].[TeacherUnidentified] TO [pSurveyWrite]
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
GRANT SELECT ON OBJECT::[dbo].[TeacherUnidentified] TO [pTeacherRead]
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'dbo.TeacherUnidentified Table Updated Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Update dbo.TeacherUnidentified Table'
END
GO
--
-- Script to Create pInspectionRead.QuarterlyReports in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Creating pInspectionRead.QuarterlyReports View'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
exec('CREATE VIEW pInspectionRead.QuarterlyReports
WITH VIEW_METADATA
AS
SELECT SI.inspID, SI.schNo, SI.inspStart AS StartDate, SI.inspEnd AS EndDate, SI.inspNote AS Note, SI.inspBy AS InspectedBy, QR.qrNumDaysOpen AS NumDaysOpen, QR.qrAggDaysAtt AS AggDaysAtt, QR.qrAggDaysAbs AS AggDaysAbs, QR.qrAggDaysMem As AggDaysMem, QR.qrAvgDailyAtt AS AvgDailyAtt, QR.qrAvgDailyMem AS AvgDailyMem, QR.qrTotEnrolToDate AS TotEnrolToDate, QR.qrPupilEnrolLastDay AS PupilEnrolLastDay, ISET.inspsetName AS InspQuarterlyReport, ISET.inspsetType
FROM dbo.SchoolInspection AS SI
LEFT OUTER JOIN
dbo.InspectionSet AS ISET ON SI.inspsetID = ISET.inspsetID
LEFT OUTER JOIN
dbo.QuarterlyReport AS QR ON SI.inspID = QR.qrID
LEFT OUTER JOIN
dbo.lkpInspectionTypes AS IT ON ISET.inspsetType = IT.intyCode
WHERE ISET.inspsetType = ''QUARTER''
')
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'pInspectionRead.QuarterlyReports View Added Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Add pInspectionRead.QuarterlyReports View'
END
GO
--
-- Script to Update pInspectionRead.SchoolInspections in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Updating pInspectionRead.SchoolInspections View'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
DROP VIEW [pInspectionRead].[SchoolInspections]
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
exec('CREATE VIEW pInspectionRead.SchoolInspections
WITH VIEW_METADATA
AS
Select inspID
, schNo
, inspPlanned PlannedStartDate
, inspStart StartDate
, inspEnd EndDate
, inspNote Note
, inspBy InspectedBy
, inspsetName InspProgram
, inspsetType InspTypeCode
, intyDesc InspType
FROM SchoolInspection SI
LEFT JOIN InspectionSet ISET
ON SI.inspsetID = ISET.inspsetID
LEFT JOIN lkpInspectionTypes IT
ON ISET.inspsetType = IT.intyCode
WHERE ISET.inspsetType <> ''QUARTER''
')
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'pInspectionRead.SchoolInspections View Updated Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Update pInspectionRead.SchoolInspections View'
END
GO
--
-- Script to Update dbo.BuildRank in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Updating dbo.BuildRank Procedure'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
DROP PROCEDURE [dbo].[BuildRank]
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
exec('CREATE PROCEDURE [dbo].[BuildRank]
(@SurveyYear int = 0)
AS
BEGIN
/*
This procedure rebuilds the ranking table showing the rank, dectile and quartile for
each school in each year, within its district and school type, and nationally within school type
*/
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @ranktmp TABLE
(
svyYear int not null,
schNo nvarchar(50) not null,
District nvarchar(10) null,
SchoolType nvarchar(10) not null,
Enrol int,
EnrolM int,
EnrolF int,
Estimate bit
, rankD int
, rankN int
)
declare @ds TABLE
(
svyYear int,
District nvarchar(10),
SchoolType nvarchar(10),
numSchools int
)
declare @dsn TABLE
(
svyYear int,
SchoolType nvarchar(10),
numSchools int
)
select *
into #ebse
from dbo.tfnESTIMATE_BestSurveyEnrolments()
WHERE LifeYear in (Select svyYear from Survey)
-- this temp table is the enrolment for each school in each life year
-- it includee the enroloment value, (now staged on Schoolsurvey)
-- and the estimate flag
insert into @ranktmp(svyYear, schNo, District, SchoolType, Enrol, EnrolM, EnrolF, Estimate, rankD, rankN)
select E.[LifeYear],
E.schNo,
I.iGroup,
ss2.ssSchType,
SS.ssEnrol,
SS.ssEnrolM,
SS.ssEnrolF,
E.Estimate
, ROW_NUMBER() OVER (PARTITION BY [LifeYear], ss2.ssSchType, iGRoup ORDER BY ss.ssEnrol DESC)
, ROW_NUMBER() OVER (PARTITION BY [LifeYear], ss2.ssSchType ORDER BY ss.ssEnrol DESC)
from
#ebse E
INNER JOIN
SchoolSurvey SS
on E.bestssID = ss.ssID
inner join SchoolSurvey SS2
on E.surveydimensionssID = SS2.ssID
INNER JOIN Schools S
on SS.schNo = s.schNo
inner join Islands I on S.iCode = i.iCode
WHERE [LifeYear] = @SurveyYear or @SurveyYear = 0
INSERT INTO @ds
Select svyYear, District, SchoolType
, count(rankD) numSchools
from @rankTmp
group by svyYear, District, SchoolType
INSERT INTO @dsn
Select svyYear, SchoolType
, sum(numSchools)
from @ds
group by svyYear, SchoolType
delete from SurveyYEarRank
WHERE svyYear = @SurveyYear or @SurveyYear = 0
insert into SurveyYearRank(svyYear, schNo,
rankDistrict, rankSchType,
rankEnrol, rankEnrolM, rankEnrolF, rankEstimate,
rankDistrictSchoolType, rankDistrictSchoolTypeDec, rankDistrictSchoolTypeQtl,
rankSchoolType, rankSchoolTypeDec, rankSchoolTypeQtl
)
select E.svyYear, E.schNo, E.District, E.SchoolType,
E.Enrol, E.enrolM, E.enrolF, E. Estimate
, E.rankD
, right(''0'' + convert(nvarchar(2),case
when DS.numSchools < 10 then rankD
else floor((rankD-1) * 10 / DS.numSchools)+1
end),2)
, ''Q'' + convert(nchar(1),case
when DS.numSchools < 4 then rankD
else floor((rankD-1) * 4 / DS.numSchools)+1
end)
, E.rankN
, right(''0'' + convert(nvarchar(2),case
when DSN.numSchools < 10 then rankN
else floor((rankN-1) * 10 / DSN.numSchools)+1
end),2)
, ''Q'' + convert(nchar(1),case
when DSN.numSchools < 4 then rankN
else floor((rankN-1) * 4 / DSN.numSchools)+1
end)
from
@rankTmp E
inner join @ds DS on
E.svyYear = DS.svyyear and
E.District = DS.District and
E.SchoolType = DS.SchoolType
inner join @dsn DSN on
E.svyYear = DSN.svyyear and
E.SchoolType = DSN.SchoolType
-- this writes the ranke, dectile and quartile values
-- the rank we can calculate from the id in the rank table, and the first id for the group
--- which is now in #DS
END
')
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'dbo.BuildRank Procedure Updated Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Update dbo.BuildRank Procedure'
END
GO
--
-- Script to Update dbo.EnrolmentRatios in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Updating dbo.EnrolmentRatios Procedure'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
REVOKE EXECUTE ON OBJECT::[dbo].[EnrolmentRatios] TO [public]
GO
DROP PROCEDURE [dbo].[EnrolmentRatios]
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
exec('-- =============================================
-- Author: Brian Lewis
-- Create date: 23 2 2010
-- Description: Lite version of EFA5 query
-- =============================================
CREATE PROCEDURE dbo.EnrolmentRatios
-- Add the parameters for the stored procedure here
@SendASXML int = 0,
@xmlout xml = null OUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
CREATE TABLE #erData
(
[Survey Year] int
, Estimate int
, Age int
, LevelCode nvarchar(10)
, enrolM int
, enrolF int
, repM int
, repF int
, popM int
, popF int
, intakeM int
, intakeF int
, popModCode nvarchar(10)
, popModName nvarchar(50)
, popModDefault int
, atAgeLevel int
, atEdLevelAge int
, atEdLevelAltAge int
, atEdLevelAlt2Age int
, edLevelCode nvarchar(10)
, edLevelAltCode nvarchar(10)
, edLevelAlt2Code nvarchar(10)
)
declare @FirstYear int
SELECT @FirstYear = min(popYear)
From dbo.Population P
INNER JOIN PopulationModel PM
ON P.popmodCode = PM.popModCode
WHERE PM.popmodEFA = 1
select @FirstYear = case when @FirstYEar < year(getdate()) - 5 then year(getdate()) - 5
else @FirstYear end
INSERT INTO #erData
EXEC dbo.sp_EFA5Data 1, @FirstYear
Select *
, case
when isnull(popM , 0 ) = 0 then null
else cast(enrolM as float)/popM
end gerM
, case
when isnull(popF , 0 ) = 0 then null
else cast(enrolF as float)/popF
end gerF
, case
when isnull(pop , 0 ) = 0 then null
else cast(enrol as float)/pop
end ger
, case
when isnull(popM , 0 ) = 0 then null
else cast(nEnrolM as float)/popM
end nerM
, case
when isnull(popF , 0 ) = 0 then null
else cast(nEnrolF as float)/popF
end nerF
, case
when isnull(pop , 0 ) = 0 then null
else cast(nEnrol as float)/pop
end ner
, case
when isnull(nEnrolM,0) = 0 then null
when isnull(popM,0) = 0 then null
else (cast(nEnrolf as float)/popF)/(cast(nEnrolM as float)/popM)
end nerGPI
, case
when isnull(nEnrolM,0) = 0 then null
else (cast(Enrolf as float)/popF)/(cast(EnrolM as float)/popM)
end gerGPI
, case
when isnull(intakePopM, 0) = 0 then null
else cast(intakeM as float)/IntakepopM
end girM
, case
when isnull(intakePopF, 0) = 0 then null
else cast(IntakeF as float)/IntakePopF
end girF
, case
when isnull(intakePop, 0) = 0 then null
else cast(Intake as float)/IntakePop
end gir
, case
when isnull(intakePopM, 0) = 0 then null
else cast(nintakeM as float)/IntakepopM
end nirM
, case
when isnull(intakePopF, 0) = 0 then null
else cast(nIntakeF as float)/IntakePopF
end nirF
, case
when isnull(intakePop, 0) = 0 then null
else cast(nIntake as float)/IntakePop
end nir
, case
when isnull(popLastLevelM, 0) = 0 then null
else cast(intakeLastLevelM as float)/popLastLevelM
end girLastLevelM
, case
when isnull(popLastLevelF, 0) = 0 then null
else cast(intakeLastLevelF as float)/popLastLevelF
end girLastLevelF
, case
when isnull(popLastLevel, 0) = 0 then null
else cast(intakeLastLevel as float)/popLastLevel
end girLastLevel
, case
when isnull(Enrol,0) = 0 then null
else cast(EstimateEnrol as float)/Enrol
end EstimatePerc
INTO #erData2
from
(
Select [Survey Year]
, edLevelCode
, edlMinYear MinYoE
, edlMaxYear MaxYoE
, edlMaxYear - edlMinYear + 1 edlevelYears
, svyPSAge + edlMinYear - 1 startAge
, sum(enrolM) enrolM
, sum(enrolF) enrolF
, sum(isnull(enrolM,0)+isnull(enrolF,0)) Enrol
, sum(repM) repM
, sum(repF) repF
, sum(isnull(repM,0) + isnull(repF,0)) rep
, sum(popM) popM
, sum(popF) popF
, sum(isnull(popM,0) + isnull(popF,0)) pop
, sum(case when L.lvlYear = EL.edlMinYear then popM else null end) intakePopM
, sum(case when L.lvlYear = EL.edlMinYear then popF else null end) intakePopF
, sum(case when L.lvlYear = EL.edlMinYear then isnull(popM,0) + isnull(popF,0) else null end) intakePop
, sum(case when L.lvlYear = EL.edlMaxYear then popM else null end) PopLastLevelM
, sum(case when L.lvlYear = EL.edlMaxYear then popF else null end) PopLastLevelF
, sum(case when L.lvlYear = EL.edlMaxYear then isnull(popM,0) + isnull(popF,0) else null end) PopLastLevel
, sum(case when L.lvlYear = EL.edlMinYear then intakeM else null end) intakeM
, sum(case when L.lvlYear = EL.edlMinYear then intakeF else null end) intakeF
, sum(case when L.lvlYear = EL.edlMinYear then isnull(intakeM,0) + isnull(intakeF,0) else null end) intake
, sum(case when L.lvlYear = EL.edlMinYear and atAgeLevel = 1 then intakeM else null end) nintakeM
, sum(case when L.lvlYear = EL.edlMinYear and atAgeLevel = 1 then intakeF else null end) nintakeF
, sum(case when L.lvlYear = EL.edlMinYear and atAgeLevel = 1 then isnull(intakeM,0) + isnull(intakeF,0) else null end) nIntake
, sum(case when L.lvlYear = EL.edlMaxYear then intakeM else null end) intakeLastLevelM
, sum(case when L.lvlYear = EL.edlMaxYear then intakeF else null end) intakeLastLevelF
, sum(case when L.lvlYear = EL.edlMaxYear then isnull(intakeM,0) + isnull(intakeF,0) else null end) intakeLastLevel
, sum(case when atEdLevelAge = 1 then enrolM else null end) nEnrolM
, sum(case when atEdLevelAge = 1 then enrolF else null end) nEnrolF
, sum(case when atEdLevelAge = 1 then isnull(enrolM,0) + isnull(enrolF,0) else null end) nEnrol
, sum(case when Estimate = 1 then isnull(enrolM,0)+isnull(enrolF,0) else null end) EstimateEnrol
from #erData
INNER JOIN lkpLevels L
ON #erData.levelCode = L.codeCode
INNER JOIN lkpEducationLevels EL
ON #erData.edLevelCode = EL.codeCode
INNER JOIN Survey S
ON #erData.[Survey Year] = S.svyYear
GROUP BY [Survey YEar], edLevelCode
, edlMinYear, edlMaxYear, svyPSAge
UNION
Select [Survey Year]
, edLevelAltCode
, edlMinYear MinYoE
, edlMaxYear MaxYoE
, edlMaxYear - edlMinYear + 1 edlevelYears
, svyPSAge + edlMinYear - 1 startAge
, sum(enrolM) enrolM
, sum(enrolF) enrolF
, sum(isnull(enrolM,0)+isnull(enrolF,0)) Enrol
, sum(repM) repM
, sum(repF) repF
, sum(isnull(repM,0) + isnull(repF,0)) rep
, sum(popM) popM
, sum(popF) popF
, sum(isnull(popM,0) + isnull(popF,0)) pop
, sum(case when L.lvlYear = EL.edlMinYear then popM else null end) intakePopM
, sum(case when L.lvlYear = EL.edlMinYear then popF else null end) intakePopF
, sum(case when L.lvlYear = EL.edlMinYear then isnull(popM,0) + isnull(popF,0) else null end) intakePop
, sum(case when L.lvlYear = EL.edlMaxYear then popM else null end) PopLastLevelM
, sum(case when L.lvlYear = EL.edlMaxYear then popF else null end) PopLastLevelF
, sum(case when L.lvlYear = EL.edlMaxYear then isnull(popM,0) + isnull(popF,0) else null end) PopLastLevel
, sum(case when L.lvlYear = EL.edlMinYear then intakeM else null end) intakeM
, sum(case when L.lvlYear = EL.edlMinYear then intakeF else null end) intakeF
, sum(case when L.lvlYear = EL.edlMinYear then isnull(intakeM,0) + isnull(intakeF,0) else null end) intake
, sum(case when L.lvlYear = EL.edlMinYear and atAgeLevel = 1 then intakeM else null end) nintakeM
, sum(case when L.lvlYear = EL.edlMinYear and atAgeLevel = 1 then intakeF else null end) nintakeF
, sum(case when L.lvlYear = EL.edlMinYear and atAgeLevel = 1 then isnull(intakeM,0) + isnull(intakeF,0) else null end) nIntake
, sum(case when L.lvlYear = EL.edlMaxYear then intakeM else null end) intakeLastLevelM
, sum(case when L.lvlYear = EL.edlMaxYear then intakeF else null end) intakeLastLevelF
, sum(case when L.lvlYear = EL.edlMaxYear then isnull(intakeM,0) + isnull(intakeF,0) else null end) intakeLastLevel
, sum(case when atEdLevelAltAge = 1 then enrolM else null end) nEnrolM
, sum(case when atEdLevelAltAge = 1 then enrolF else null end) nEnrolF
, sum(case when atEdLevelAltAge = 1 then isnull(enrolM,0) + isnull(enrolF,0) else null end) nEnrol
, sum(case when Estimate = 1 then isnull(enrolM,0)+isnull(enrolF,0) else null end) EstimateEnrol
from #erData
INNER JOIN lkpLevels L
ON #erData.levelCode = L.codeCode
INNER JOIN lkpEducationLevelsAlt EL
ON #erData.edLevelAltCode = EL.codeCode
INNER JOIN Survey S
ON #erData.[Survey Year] = S.svyYear
GROUP BY [Survey YEar], edLevelAltCode
, edlMinYear, edlMaxYear, svyPSAge
UNION
Select [Survey Year]
, edLevelAlt2Code
, edlMinYear MinYoE
, edlMaxYear MaxYoE
, edlMaxYear - edlMinYear + 1 edlevelYears
, svyPSAge + edlMinYear - 1 startAge
, sum(enrolM) enrolM
, sum(enrolF) enrolF
, sum(isnull(enrolM,0)+isnull(enrolF,0)) Enrol
, sum(repM) repM
, sum(repF) repF
, sum(isnull(repM,0) + isnull(repF,0)) rep
, sum(popM) popM
, sum(popF) popF
, sum(isnull(popM,0) + isnull(popF,0)) pop
, sum(case when L.lvlYear = EL.edlMinYear then popM else null end) intakePopM
, sum(case when L.lvlYear = EL.edlMinYear then popF else null end) intakePopF
, sum(case when L.lvlYear = EL.edlMinYear then isnull(popM,0) + isnull(popF,0) else null end) intakePop
, sum(case when L.lvlYear = EL.edlMaxYear then popM else null end) PopLastLevelM
, sum(case when L.lvlYear = EL.edlMaxYear then popF else null end) PopLastLevelF
, sum(case when L.lvlYear = EL.edlMaxYear then isnull(popM,0) + isnull(popF,0) else null end) PopLastLevel
, sum(case when L.lvlYear = EL.edlMinYear then intakeM else null end) intakeM
, sum(case when L.lvlYear = EL.edlMinYear then intakeF else null end) intakeF
, sum(case when L.lvlYear = EL.edlMinYear then isnull(intakeM,0) + isnull(intakeF,0) else null end) intake
, sum(case when L.lvlYear = EL.edlMinYear and atAgeLevel = 1 then intakeM else null end) nintakeM
, sum(case when L.lvlYear = EL.edlMinYear and atAgeLevel = 1 then intakeF else null end) nintakeF
, sum(case when L.lvlYear = EL.edlMinYear and atAgeLevel = 1 then isnull(intakeM,0) + isnull(intakeF,0) else null end) nIntake
, sum(case when L.lvlYear = EL.edlMaxYear then intakeM else null end) intakeLastLevelM
, sum(case when L.lvlYear = EL.edlMaxYear then intakeF else null end) intakeLastLevelF
, sum(case when L.lvlYear = EL.edlMaxYear then isnull(intakeM,0) + isnull(intakeF,0) else null end) intakeLastLevel
, sum(case when atEdLevelAlt2Age = 1 then enrolM else null end) nEnrolM
, sum(case when atEdLevelAlt2Age = 1 then enrolF else null end) nEnrolF
, sum(case when atEdLevelAlt2Age = 1 then isnull(enrolM,0) + isnull(enrolF,0) else null end) nEnrol
, sum(case when Estimate = 1 then isnull(enrolM,0)+isnull(enrolF,0) else null end) EstimateEnrol
from #erData
INNER JOIN lkpLevels L
ON #erData.levelCode = L.codeCode
INNER JOIN lkpEducationLevelsAlt2 EL
ON #erData.edLevelAlt2Code = EL.codeCode
INNER JOIN Survey S
ON #erData.[Survey Year] = S.svyYear
GROUP BY [Survey YEar], edLevelAlt2Code
, edlMinYear, edlMaxYear, svyPSAge
) sub
WHERE edLevelCode is not null
ORDER BY [Survey Year],edLevelCode
--SELECT * from #erData
IF @SENDASXML = 0 begin
SELECT * from #erData2
end
if @SendAsXML <> 0 begin
declare @xml xml
declare @xml2 xml
SELECT @xml =
(
SELECT [Survey Year] [@year]
, edLevelCode [@edLevelCode]
, MinYoE [@firstYear]
, MaxYoE [@lastYear]
, edlevelYears [@numYears]
, startAge [@startAge]
, popM
, popF
, pop
, enrolM
, enrolF
, enrol
, nEnrolM
, nEnrolF
, nEnrol
, intakeM
, intakeF
, intake
, nIntakeM
, nIntakeF
, nIntake
, repM
, repF
, rep
, cast(gerM as decimal(8,5)) gerM
, cast(gerF as decimal(8,5)) gerF
, cast(ger as decimal(8,5)) ger
, cast(nerM as decimal(8,5)) nerM
, cast(nerF as decimal(8,5)) nerF
, cast(ner as decimal(8,5)) ner
, cast(girM as decimal(8,5)) girM
, cast(girF as decimal(8,5)) girF
, cast(gir as decimal(8,5)) gir
, cast(nirM as decimal(8,5)) nirM
, cast(nirF as decimal(8,5)) nirF
, cast(nir as decimal(8,5)) nir
,cast(EstimatePerc as decimal(7,4)) est
FROM #erData2
FOR XML PATH(''ER'')
)
SELECT @xmlout
=
(SELECT @Xml
FOR XML PATH(''ERs'')
)
if @SendAsXML = 1
SELECT @xmlout -- don''t return a confusing resultset if we only want the output param
end
END
')
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
GRANT EXECUTE ON OBJECT::[dbo].[EnrolmentRatios] TO [public]
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'dbo.EnrolmentRatios Procedure Updated Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Update dbo.EnrolmentRatios Procedure'
END
GO
--
-- Script to Update pPARead.PerfAssessFilterIDs in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Updating pPARead.PerfAssessFilterIDs Procedure'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
DROP PROCEDURE [pPARead].[PerfAssessFilterIDs]
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
exec('-- =============================================
-- Author: Brian Lewis
-- Create date: 08/11/2015
-- Description: Perf Assessment
-- =============================================
CREATE PROCEDURE [pPARead].[PerfAssessFilterIDs]
-- Add the parameters for the stored procedure here
@NumMatches int OUTPUT,
@PageSize int = 0,
@PageNo int = 0,
@SortColumn nvarchar(30) = null,
@SortDir int = 0,
@PerfAssessID int = null,
@TeacherID int = null,
@ConductedBy nvarchar(50) = null,
@SchoolNo nvarchar(50) = null,
@District nvarchar(10) = null,
@Island nvarchar(10) = null,
@StartDate datetime = null,
@EndDate datetime = null,
@subScoreCode nvarchar(15) = null,
@subScoreMin float = null,
@subScoreMax float = null,
@xmlFilter xml = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @keysAll TABLE
(
selectedID int
, recNo int
)
DECLARE @s TABLE
(
paID int
)
declare @Framework nvarchar(10)
declare @subScoreLevel nvarchar(1)
declare @subScoreID int
-- if processing for a subscore, determine the level
if @subScoreCode is not null begin
select @subScoreLevel = Level
, @subScoreID = ID
from paHierarchy
WHERE FullId = @subScoreCode
end
print @subScoreLevel
print @subScoreID
if @subScoreLevel = ''I'' begin
-- need to translate the
INSERT INTO @s
Select paID
from paAssessmentLine_ PAL
INNER JOIN paIndicatorLevels_ IL
ON PAL.paIndID = IL.paIndID
WHERE PAL.paindID = @subScoreID
AND ( @subScoreMin is null OR paplValue >= @subScoreMin)
AND ( @subScoreMax is null OR paplValue <= @subScoreMax)
end
if @subScoreLevel = ''E'' begin
INSERT INTO @s
Select paID
from paElementAvg
WHERE elmFullId = @subScoreCode
AND ( @subScoreMin is null OR RawElmAvg >= @subScoreMin)
AND ( @subScoreMax is null OR RawElmAvg <= @subScoreMax)
end
if @subScoreLevel = ''C'' begin
INSERT INTO @s
Select paID
from paCompetencyAvg
WHERE ComFullId = @subScoreCode
AND ( @subScoreMin is null OR RawComAvg >= @subScoreMin)
AND ( @subScoreMax is null OR RawComAvg <= @subScoreMax)
end
if @subScoreLevel = ''F''
select @Framework = @subScoreCode
INSERT INTO @KeysAll
SELECT PA.paID
, row_number() OVER ( ORDER BY
case @SortColumn
when ''framework'' then PA.pafrmCode
end
, paID
) RecNo
FROM PAAssessmentRpt PA
WHERE
(paID = @PerfAssessID or @PerfAssessID is null)
AND (tID = @TeacherID or @TeacherID is null)
AND (pafrmCode = @Framework or @Framework is null)
AND (paConductedBy like @ConductedBy + ''%'' or @ConductedBy is null)
AND (
(@StartDate is null and @EndDate is null)
OR
(paDate between isnull(@StartDate, ''1901-01-01'') and isnull(@EndDate, getdate()))
)
AND (@District is null or dID = @District)
AND (@Island is null or iCode = @Island)
AND (@schoolNo is null or paSchNo = @schoolNo)
AND ( @subScoreLevel is null
OR
( @subScoreLevel = ''F'' AND paScore between isnull(@subScoreMin,0) and isnull(@subScoreMax,9999))
OR
paId in (Select paID FROM @s)
)
SELECT @NumMatches = @@ROWCOUNT -- this returns the total matches
If @SortDir = 1 begin
SELECT selectedID
, RecNo
FROM
(
Select selectedID
, @NumMatches - RecNo + 1 RecNo
FROM @KeysAll
) S
WHERE (@PageSize = 0
or
@PageSize >= @NumMatches
or
RecNo between @PageSize * (@PageNo - 1) + 1
and @PageSize * (@PageNo)
)
ORDER BY RecNo
end
else begin
SELECT selectedID
, RecNo
FROM @KeysAll
WHERE (@PageSize = 0
or
@PageSize >= @NumMatches
or
RecNo between @PageSize * (@PageNo - 1) + 1
and @PageSize * (@PageNo)
)
end END
')
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'pPARead.PerfAssessFilterIDs Procedure Updated Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Update pPARead.PerfAssessFilterIDs Procedure'
END
GO
--
-- Script to Update pEnrolmentRead.SchoolAnnualSummary in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Updating pEnrolmentRead.SchoolAnnualSummary Procedure'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
DROP PROCEDURE [pEnrolmentRead].[SchoolAnnualSummary]
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
exec('-- =============================================
-- Author: Brian Lewis
-- Create date: 1 10 2014
-- Description: summary of key survey statistics by year
-- =============================================
CREATE PROCEDURE [pEnrolmentRead].[SchoolAnnualSummary]
@SchoolNo nvarchar(50) = null
, @SurveyYear int = null
WITH EXECUTE AS ''Pineapples''
-- we''ll let you read the teacher counts here, even if you do not have teacher read
-- hence the need for the execute as
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- strategy is temp table with each required field, records are added in sets
-- then summed to bring all the fields together
DECLARE @tmpAS TABLE
(
schNo nvarchar(50)
, svyYear int
, minClass nvarchar(10)
, maxClass nvarchar(10)
, TeacherSurveyCount int
, ClassroomCount int
)
-- to begin make sure we have every life year for the school in the table
INSERT INTO @TmpAS
(schNo, svyYear)
Select schNo, svyYear
FROM SchoolLifeYears
WHERE
(
(@SchoolNo is null or @SchoolNo = SchNo)
AND (@SurveyYear is null or @SurveyYEar = svyYEar)
)
-- add the teacher survey count records
INSERT INTO @tmpAS
(SchNo, svyYear, TeacherSurveyCount)
SELECT
schNo, svyYear, count(tchsID)
FROM
SchoolSurvey SS
INNER JOIN TeacherSurvey TS
ON Ss.ssID = TS.ssID
WHERE
(
(@SchoolNo is null or @SchoolNo = SchNo)
AND (@SurveyYear is null or @SurveyYEar = svyYEar)
)
group by schNo, svyYear
-- add the set of classroom records
INSERT INTO @tmpAS
(SchNo, svyYear, ClassroomCount)
SELECT
schNo, svyYear, count(rmID)
FROM
SchoolSurvey SS
INNER JOIN Rooms
ON Ss.ssID = Rooms.ssID
where
(
(@SchoolNo is null or @SchoolNo = SchNo)
AND (@SurveyYear is null or @SurveyYEar = svyYEar)
)
and rmType = ''CLASS''
group by schNo, svyYear
-- calculate the minimum and maximum levels for enrol
------
------Select SchNo
------ , SvyYear
------ , MinYear
------ , MaxYear
------ , min(case when L.lvlYear = MinYear then L.codeCode else null end) MinLevelCode
------ , min(case when L.lvlYear = MinYear then L.codeDescription else null end) MinLevel
------ , min(case when L.lvlYear = MaxYear then L.codeCode else null end) MaxLevelCode
------ , min(case when L.lvlYear = MAxYear then L.codeDescription else null end) MaxLevel
------
------
------FROM
------ Enrollments E
------
------ INNER JOIN TRLevels L
------ ON E.enLevel = L.codeCode
------ INNER JOIN
------ (
------ Select S.SchNo
------ , S.svyYEar
------ , S.ssID
------ ,min(lvlYear) MinYear
------ , max(lvlYear) MaxYear
------ from SchoolSurvey S
------ INNER JOIN Enrollments E
------ ON S.ssID = E.ssID
------ INNER JOIN lkpLevels L
------ ON E.enLevel = L.codeCode
------ GROUP BY S.SchNo, S.svyYEar, S.ssID
------ ) MiniMax
------ ON MiniMax.ssID = E.ssID
------GROUP BY SchNo, svyYEar, minYEar, maxYear
Select
TMP.schNo
, TMP.svyYear
, SS.ssSchType
, SS.ssAuth
, SS.ssEnrol
, SS.ssEnrolM
, SS.ssEnrolF
, SS.ssID
, SS.ssPrinFirstName
, SS.ssPrinSurname
, SS.ssNote
, LMin.CodeDescription minLevel
, LMax.CodeDescription maxLevel
, ELS.MaxLevelEnrol
, LMaxEnrol.codeDescription maxEnrolLevel
, TeacherSurveyCount
, ClassroomCount
, case when TeacherSurveyCount = 0 then null else cast(SS.ssEnrol as float)/TeacherSurveyCount end PTR
, eAudit.Loaded
, eAudit.Errors
, eAudit.Warnings
, case when eAudit.Loaded is null then 0 else 1 end eForm
FROM
(
Select
schNo
, svyYear
, min(minClass) minClass
, max(maxClass) maxClass
, max(TeacherSurveyCount) TeacherSurveyCount
, max(ClassroomCount) ClassroomCount
FROM
@tmpAS
GROUP BY
schNo, svyYear
) TMP
LEFT JOIN SchoolSurvey SS
ON ss.schNo = Tmp.SchNo
AND SS.svyYear = TMP.svyYear
LEFT JOIN pEnrolmentRead.ssIDEnrolmentLevelSummary ELS
ON SS.ssID = ELS.ssID
LEFT JOIN TRLevels LMin
On ELS.LowestLevel = LMin.codeCode
LEFT JOIN TRLevels LMax
ON ELS.HighestLevel = LMAx.CodeCode
LEFT JOIN TRLevels LMaxEnrol
ON ELS.LEvelOfMaxEnrol = LMaxEnrol.CodeCode
LEFT JOIN
(
Select ssID
, max(sxaDate) Loaded
, sum(case when sxaErrorflag = 2 then 1 else null end) Errors
, sum(case when sxaErrorflag = 1 then 1 else null end) Warnings
FROM audit.xfdfAudit A
GROUP BY ssID
) eAudit
ON eAudit.ssID = SS.ssID
END
')
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'pEnrolmentRead.SchoolAnnualSummary Procedure Updated Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Update pEnrolmentRead.SchoolAnnualSummary Procedure'
END
GO
--
-- Script to Update pSchoolRead.SchoolReadEx in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Updating pSchoolRead.SchoolReadEx Procedure'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
DROP PROCEDURE [pSchoolRead].[SchoolReadEx]
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
exec('-- =============================================
-- Author: Brian Lewis
-- Create date: 13 08 2015
-- Description: Read multiple reordsets about school
-- =============================================
CREATE PROCEDURE pSchoolRead.SchoolReadEx
-- Add the parameters for the stored procedure here
@SchoolNo nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT * from Schools WHERE schNo = @schoolNo
-- survey summary data
exec pEnrolmentRead.schoolAnnualSummary @schoolNo
Select * from pExamRead.SchoolExams WHERE schNo = @schoolNo
Select * from pInspectionRead.SchoolInspections WHERE schNo = @schoolNo
Select * from pInspectionRead.QuarterlyReports WHERE schNo = @schoolNo
END
')
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'pSchoolRead.SchoolReadEx Procedure Updated Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Update pSchoolRead.SchoolReadEx Procedure'
END
GO
--
-- Script to Update pSchoolRead.SchoolScatterChart in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Updating pSchoolRead.SchoolScatterChart Procedure'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
DROP PROCEDURE [pSchoolRead].[SchoolScatterChart]
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
exec('-- =============================================
-- Author: Brian Lewis
-- Create date: 18 05 2010
-- Description: School scatter chart
-- june 2014: support kml export
-- =============================================
CREATE PROCEDURE [pSchoolRead].[SchoolScatterChart]
-- Add the parameters for the stored procedure here
@BaseYear int = 0,
@XSeries nvarchar(50),
@XSeriesOffset int,
@YSeries nvarchar(50),
@YSeriesOffset int,
@xArg1 nvarchar(50) = null,
@xArg2 nvarchar(50) = null,
@yArg1 nvarchar(50) = null,
@yArg2 nvarchar(50) = null,
@SchoolType nvarchar(10) = null,
@Authority nvarchar(10) = null,
@District nvarchar(10) = null,
@SchoolNo nvarchar(10) = null,
@kml nvarchar(max) = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @XData TABLE
(
schNo nvarchar(50)
, XValue float
, Estimate int
, XQuality int
)
DECLARE @YData TABLE
(
schNo nvarchar(50)
, YValue float
, Estimate int
, YQuality int
)
DECLARE @scatter TABLE
(
schNo nvarchar(50)
, schName nvarchar(50)
, XValue float
, YValue float
, XEstimate int
, YEstimate int
, XQuality int
, YQuality int
, authCode nvarchar(10)
, authName nvarchar(100)
, AuthorityGroupCode nvarchar(10)
, AuthorityGroup nvarchar(100)
, schType nvarchar(10)
, stDescription nvarchar(100)
, dID nvarchar(10)
, District nvarchar(50)
, langCode nvarchar(10)
, langName nvarchar(50)
-- 15 10 2014 added lat and long
, schLat decimal(12,8)
, schLong decimal(12,8)
-- calculate3d values
, YonX float
, XQ float
, YQ float
, YonXQ float
, YonXQuality int
, PtSelected int
)
-- decode the series identifier to get the stored proc
Select @Xseries = case @XSeries
when ''Enrolment'' then ''pSchoolRead.SchoolDataSetEnrolment''
when ''Repeaters'' then ''pSchoolRead.SchoolDataSetRepeaterCount''
when ''Teachers'' then ''pSchoolRead.SchoolDataSetTeacherCount''
when ''TeachersQualCert'' then ''pSchoolRead.SchoolDataSetQualCertCount''
when ''TeachersQualCertPerc'' then ''pSchoolRead.SchoolDataSetQualCertPerc''
else @XSeries
end
Select @YSeries = case @YSeries
when ''Enrolment'' then ''pSchoolRead.SchoolDataSetEnrolment''
when ''Repeaters'' then ''pSchoolRead.SchoolDataSetRepeaterCount''
when ''Teachers'' then ''pSchoolRead.SchoolDataSetTeacherCount''
when ''TeachersQualCert'' then ''pSchoolRead.SchoolDataSetTeacherQualCertCount''
when ''TeachersQualCertPerc'' then ''pSchoolRead.SchoolDataSetTeacherQualCertPerc''
else @YSeries
end
declare @YearArg int
declare @SQL nvarchar(400)
------ set up the XData
Select @YearArg = @BaseYear + @XSeriesOffset
SELECT @SQL = ''exec '' + @XSeries + '' '' + cast(@YearArg as nvarchar(4))
+ '', '' + case when @SchoolNo is null then ''NULL'' else quotename(@SchoolNo,'''''''') end
+ case when @xArg1 is null then
case when @xArg2 is null then '''' else '', NULL'' end
else '', '' + quotename(@xArg1,'''''''') end
+ case when @xArg2 is null then '''' else '', '' + quotename(@xArg2,'''''''') end
INSERT INTO @XData
EXEC sp_executesql @SQL
--- the y data
Select @YearArg = @BaseYear + @YSeriesOffset
SELECT @SQL = ''exec '' + @YSeries + '' '' + cast(@YearArg as nvarchar(4))
+ '', '' + case when @SchoolNo is null then ''NULL'' else quotename(@SchoolNo,'''''''') end
+ case when @yArg1 is null then
case when @yArg2 is null then '''' else '', NULL'' end
else '', '' + quotename(@yArg1,'''''''') end
+ case when @yArg2 is null then '''' else '', '' + quotename(@yArg2,'''''''') end
INSERT INTO @YData
exec sp_executesql @SQL
--combine these into the main stagiung table
INSERT INTO @scatter
(
schNo
, schName
, XValue
, YValue
, XEstimate
, YEstimate
, XQuality
, YQuality
, authCode
, authName
, AuthorityGroupCode
, AuthorityGroup
, schType
, stDescription
, dID
, District
, langCode
, langName
, schLat
, schLong
, YonX
, PtSelected
)
Select
S.schNo
, s.schName
, X.XValue
, Y.YValue
, X.Estimate XEstimate
, Y.Estimate YEstimate
, X.XQuality
, Y.YQuality
, isnull(SYH.syAuth, S.schAuth) authCode
, AUTH.Authority
, AuthorityGroupCode
, AuthorityGroup
, isnull(SYH.systCode, S.schType) schType
, ST.stDescription
, dID
, dName District
, schLang
, LangName
-- lat and long 15 10 2014
, S.schLat
, S.schLong
, case when isnull(XValue,0) = 0 then null else YValue / XValue end YonX
, 0
from Schools S
LEFT JOIN @XData X
ON S.schNo = X.schNo
LEFT JOIN @YData Y
ON S.schNo = Y.schNo
LEFT JOIN SchoolYearHistory SYH
ON SYH.schNo = S.schNo
AND SYH.syYear = @BaseYear
LEFT JOIN DimensionAuthority AUTH
ON isnull(SYH.syAuth,S.schAuth) = AUTH.authorityCode
LEFT JOIN TRSchoolTypes ST
ON isnull(SYH.systCode, S.schType) = ST.stCode
LEFT JOIN Islands I
ON I.iCode = S.iCode
LEFT JOIN Districts D
ON I.iGRoup = D.dID
LEFT JOIN TRLanguage LNG
ON S.schLang= LNG.langCode
WHERE (XValue is not null or YValue is not null)
AND (dID = @District or @District is null)
AND (isnull(SYH.syAuth,S.schAuth) = @Authority or @Authority is null)
AND (isnull(SYH.systCode,S.schType) = @SchoolType or @SchoolType is null)
ORDER BY schNo, XValue, YValue
declare @avgX float
declare @stdevX float
declare @minX float
declare @maxX float
declare @medianX float
declare @Q1X float
declare @Q3X float
declare @avgY float
declare @stdevY float
declare @minY float
declare @maxY float
declare @medianY float
declare @Q1Y float
declare @Q3Y float
declare @avgYonX float
declare @stdevYonX float
declare @minYonX float
declare @maxYonX float
declare @medianYonX float
declare @Q1YonX float
declare @Q3YonX float
;
with X as
(
Select *
, row_number() OVER (ORDER BY XValue, schNo) UPX
, row_number() OVER (ORDER BY XValue DESC, schNo DESC ) DOWNX
, row_number() OVER (ORDER BY YValue, schNo) UPY
, row_number() OVER (ORDER BY YValue DESC, schNo DESC ) DOWNY
from @Scatter
)
Select @avgX = avg(XValue)
, @stdevX = stdev(XValue)
, @minX = min(XValue)
, @maxX = max(XValue)
, @medianX = avg(case when (UPX - DOWNX) between -1 and 1 then XValue else null end)
, @Q1X = avg(case when (3*UPX - DOWNX) between -3 and 3 then XValue else null end)
, @Q3X = avg(case when (3*DOWNX - UPX) between -3 and 3 then XValue else null end)
, @avgY = avg(YValue)
, @stdevY = stdev(YValue)
, @minY = min(YValue)
, @maxY= max(YValue)
, @medianY = avg(case when (UPY - DOWNY) between -1 and 1 then YValue else null end)
, @Q1Y = avg(case when (3*UPY - DOWNY) between -3 and 3 then YValue else null end)
, @Q3Y= avg(case when (3*DOWNY - UPY) between -3 and 3 then YValue else null end)
from X
;
WITH X AS
(
Select YonX
, row_number() OVER (ORDER BY YonX, schNo) UP
, row_number() OVER (ORDER BY YonX DESC, schNo DESC ) DOWN
from @Scatter
WHERE isnull(XValue,0) <> 0
)
Select @avgYonX = avg(YonX)
, @stdevYonX = stdev(YonX)
, @minYonX = min(YonX)
, @maxYonX = max(YonX)
, @medianYonX = avg(case when (UP - DOWN) between -1 and 1 then YonX else null end)
, @Q1YonX = avg(case when (3*UP - DOWN) between -3 and 3 then YonX else null end)
, @Q3YonX = avg(case when (3*DOWN - UP) between -3 and 3 then YonX else null end)
from X
declare @IQX float -- interquartile range
declare @IQY float -- interquartile range
declare @IQYonX float
Select @IQX = @Q3X - isnull(@Q1X,0)
Select @IQY = @Q3Y - isnull(@Q1Y,0)
Select @IQYonX = @Q3YonX - isnull(@Q1YonX,0)
UPDATE @scatter
SET XQ =
case when @IQX = 0 then 0
when XValue > @Q3X then (XValue - @Q3X) / @IQX
when XValue < @Q1X then (@Q1X - XValue) / @IQX
else null
end
, YQ = case when @IQY = 0 then 0
when YValue > @Q3Y then (YValue - @Q3Y) / @IQY
when YValue < @Q1Y then (@Q1Y - YValue) / @IQY
else null
end
, YonXQ = case when @IQYonX = 0 then 0
when YonX > @Q3YonX then (YonX - @Q3YonX) / @IQYonX
when YonX < @Q1YonX then (@Q1YonX - YonX) / @IQYonX
else null
end
, YonXQuality =
case when YQuality is null and XQuality is null then null
when YQuality = 2 or XQuality = 2 then 2
when YQuality = 1 or XQuality = 1 then 1
else 0
end
--------------------------------------------------------------------------------
-- 18 10 2014 (caulfield cup!)
-- support fo kml with the @kml flag
if (@kml is null) begin
-- first recordset is the data
SELECT *
, row_Number() OVER (ORDER BY schNo) - 1 PtIndex
from @scatter
ORDER By schNo
-- second is the statistical values
SELECT
case when sum((XValue - @AvgX)*(XValue - @AvgX)) *sum((YValue - @avgY)*(YValue - @avgY)) =0 then null
else
sum((XValue - @AvgX)*(YValue - @avgY))
/ power
(sum((XValue - @AvgX)*(XValue - @AvgX)) *sum((YValue - @avgY)*(YValue - @avgY))
, .5)
end correlation
, @avgX XAvg
, @avgY YAvg
, @avgYonX YonXAvg
, @stdevX Xstdev
, @stdevY Ystdev
, @stdevYonX YonXstdev
, @minX Xmin
, @minY Ymin
, @minYonX YonXmin
, @maxX Xmax
, @maxY Ymax
, @maxYonX YonXmax
, @medianX Xmedian
, @medianY Ymedian
, @medianYonX YonXmedian
, @Q1X XQ1
, @Q1Y YQ1
, @Q1YonX YonXQ1
, @Q3X XQ3
, @Q3Y YQ3
, @Q3YonX YonXQ3
FROM @scatter
WHERE xValue is not null and yValue is not null
-- third is the quality analysis
SELECT count(schNo) schoolCount
, sum(case when XQ > 3 then 1 else null end) XIQ3
, sum(case when YQ > 3 then 1 else null end) YIQ3
, sum(case when YonXQ > 3 then 1 else null end) YonXIQ3
, sum(case when XQ > 3 then
case when (YonXQuality is not null) then 1 else 0 end
else null end) XIQ3QualityAlerts
, sum(case when YQ > 3 then
case when (YonXQuality is not null) then 1 else 0 end
else null end) YIQ3QualityAlerts
, sum(case when YonXQ > 3 then
case when (YonXQuality is not null) then 1 else 0 end
else null end) YonXIQ3QualityAlerts
-- these are points with all alerts confirmed
, sum(case when XQ > 3 then
case when (YonXQuality = 0) then 1 else 0 end
else null end) XIQ3QualityOK
, sum(case when YQ > 3 then
case when (YonXQuality = 0) then 1 else 0 end
else null end) YIQ3QualityOK
, sum(case when YonXQ > 3 then
case when (YonXQuality = 0) then 1 else 0 end
else null end) YonXIQ3QualityOK
, sum(case when YonXQ > 3 then XValue else null end) XSumIQ3
, sum(case when YonXQ > 3 then YValue else null end) YSumIQ3
, sum(XValue) XSum
, sum(YValue) YSum
from @Scatter
end
--------------------------------------------------------------------------------
-- 18 10 2014
-- support fo kml with the @kml flag
-- we export xml in this form to schools kml
-- <data><d key="schNo"><ExtendedData><Data name="xvalue"><displayName>....</displayName>
if (@kml is not null)begin
declare @dt TABLE
(
Tag int
, parent int
, schNo nvarchar(50)
, seq int
, name nvarchar(100)
, displayname nvarchar(100)
, value nvarchar(100)
, yr int
, est nvarchar(10)
)
-- root
INSERT INTO @dt
(Tag, Parent, SchNo, seq)
SELECT 1, null, null, 0
-- first the schools
INSERT INTO @dt
(Tag,
Parent,
Schno,
Seq
)
Select 10
, 1
, schNo
, 0
FROM @scatter
-- node for extndeddata
INSERT INTO @dt
(Tag,
Parent,
Schno,
Seq
)
Select 11
, 10 -- child of d
, schNo
, 0
FROM @scatter
-- xvalue
-- xvalue - twice
INSERT INTO @dt
(Tag,
Parent,
Schno,
Seq,
name,
displayname,
value,
yr,
est
)
Select num
, 11
, schNo
, 0
, ''xvalue''
, replace(@XSeries,''pSchoolRead.schoolDataSet'','''')
, xvalue
,@BaseYear + @XSeriesOffset
, case when xEstimate = 1 then ''est'' else '''' end
FROM @scatter
-- xvalue
CROSS JOIN metaNumbers
WHERE num in (21,22, 23)
-- xvalue - twice
INSERT INTO @dt
(Tag,
Parent,
Schno,
Seq,
name,
displayname,
value,
yr,
est
)
Select num
, 11
, schNo
, 0
, ''yvalue''
, replace(@YSeries,''pSchoolRead.schoolDataSet'','''')
, Yvalue
,@BaseYear + @YSeriesOffset
, case when yEstimate = 1 then ''est'' else '''' end
FROM @scatter
-- xvalue
CROSS JOIN metaNumbers
WHERE num in (31,32,33)
declare @extdata xml
Select @extdata =
(
Select Tag, Parent
, ''schoolScatterChart'' [data!1!src]
, schNo [d!10!key]
, schNo [ExtendedData!11!!hide]
, name [Data!21!name]
, displayname [Data!21!displayName!element]
, value [Data!21!value!element]
, ''xyear'' [Data!22!name]
, yr [Data!22!value!element]
, ''xest'' [Data!23!name]
, est [Data!23!value!element]
, name [Data!31!name]
, displayname [Data!31!displayName!element]
, value [Data!31!value!element]
, ''yyear'' [Data!32!name]
, yr [Data!32!value!element]
, ''yest'' [Data!33!name]
, est [Data!33!value!element]
FROM @dt
ORDER BY schNo, Tag, seq
FOR XML EXPLICIT
)
-- Now invoke SchoolsKML with the extended data, and the balloon template passed by the caller
exec dbo.schoolsKml @kml, @extdata
end
END
')
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'pSchoolRead.SchoolScatterChart Procedure Updated Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Update pSchoolRead.SchoolScatterChart Procedure'
END
GO
--
-- Script to Update pSurveyRead.xfdfReadAudit in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Updating pSurveyRead.xfdfReadAudit Procedure'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
DROP PROCEDURE [pSurveyRead].[xfdfReadAudit]
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
exec('-- =============================================
-- Author: Brian Lewis
-- Create date: 25 4 2015
-- Description: detail audit record
-- =============================================
CREATE PROCEDURE [pSurveyRead].[xfdfReadAudit]
-- Add the parameters for the stored procedure here
@schNo nvarchar(50)
, @svyYear int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT
sxaSection Section,
sxaDesc Info,
sxaNum Number,
sxaData Code,
case sxaErrorFlag when 2 then ''Error'' when 1 then ''Warning'' else '''' end Status
FROM SchoolSurvey SS
LEFT JOIN audit.xfdfAudit A
ON SS.ssID = A.ssID
WHERE SS.schNo = @schNo AND SS.svyYEar = @svyYear
ORDER BY sxaID
-- summary
Select SS.ssID
, max(sxaDate) Loaded
, sum(case when sxaErrorflag = 2 then 1 else null end) Errors
, sum(case when sxaErrorflag = 1 then 1 else null end) Warnings
FROM SchoolSurvey SS
LEFT JOIN audit.xfdfAudit A
ON SS.ssID = A.ssID
WHERE SS.schNo = @schNo AND SS.svyYear = @svyYear
GROUP BY SS.ssID
END
')
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'pSurveyRead.xfdfReadAudit Procedure Updated Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Update pSurveyRead.xfdfReadAudit Procedure'
END
GO
--
-- Script to Update pSurveyWrite.xfdfSite in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Updating pSurveyWrite.xfdfSite Procedure'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
DROP PROCEDURE [pSurveyWrite].[xfdfSite]
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
exec('-- =============================================
-- Author: Brian Lewis
-- Create date: 30 10 2014
-- Description: update parent committe / school council
-- 2016 08 02 Modified to allow for decimals in site sizes - these are rounded for storage
-- =============================================
CREATE PROCEDURE [pSurveyWrite].[xfdfSite]
@SurveyID int
, @xml xml
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @idoc int
-- the xfdf file has the default adobe namespace
-- we have to alias this namespace ( as ''x'' ) so as to be able to use
-- the xpath expressions
declare @xmlns nvarchar(500) = ''<root xmlns:x="http://ns.adobe.com/xfdf/"/>''
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml, @xmlns
/*
Format of the Site block
<field name="Site">
<field name="Site">
<field name="Size">
<value>230</value>
</field>
<field name="L"/>
<field name="W"/> -- support Siexe ecplicitly or L and W
</field>
<field name="Playground">
<field name="Size">
<value>230</value>
</field>
</field>
<field name="Garden">
<field name="Size">
<value>100</value>
</field>
</field>
<field name="Services">
<value>N</value>
</field>
<field name="Secure">
<value>N</value>
</field>
<field name="Rating"/>
<field name="Rubbish">
freq... method
</field>
<field name="RandM">
freq... method
</field>
</field>
*/
begin try
UPDATE schoolSurvey
SET ssSizeSite = round(SiteSize,0)
, ssSizePlayground = round(PlaygroundSize,0)
, ssSizeFarm = round(GardenSize,0)
, ssSizeFarmUsed = round(GardenUsedSize,0)
, ssSizeRating = SiteSizeRating
, ssSiteSecure = case SiteSecure when ''Y'' then -1 when ''N'' then 0 else null end
, ssISClass = case SiteServices when ''Y'' then ''URBAN'' when ''N'' then ''RURAL'' else null end
, ssRubbishFreq = RubbishFreq
, ssRubbishMethod = RubbishMethod
, ssRubbishNote = RubbishNote
, ssRandM = case RandM when ''Y'' then -1 when ''N'' then 0 else null end
, ssRandMResp = RandMResp
-- L and W variation (vanuatu)
, ssPlaygroundL = round(PlaygroundL,0)
, ssPlaygroundW = round(PlaygroundW,0)
, ssFarmL = round(GardenL,0)
, ssFarmW = round(GardenW,0)
, ssFarmUsedL = round(GardenUsedL,0)
, ssFarmUsedW = round(GardenUsedW,0)
-- vanuatu specific
, ssSiteHost = SiteHost
, ssSiteHostDetail = SiteHostDetail
FROM OPENXML(@idoc, ''/x:field'',2) -- base is the Site Node
WITH
(
SiteSize float ''x:field[@name="Site"]/x:field[@name="Size"]/x:value''
, SiteL float ''x:field[@name="Site"]/x:field[@name="L"]/x:value''
, SiteW float ''x:field[@name="Site"]/x:field[@name="W"]/x:value''
, SiteSizeRating float ''x:field[@name="Rating"]/x:value''
, SiteSecure nvarchar(1) ''x:field[@name="Secure"]/x:value'' -- Y/N
, SiteServices nvarchar(1) ''x:field[@name="Services"]/x:value'' -- Y/N
, SiteHost nvarchar(10) ''x:field[@name="Host"]/x:field[@name="Type"]/x:value''
, SiteHostDetail nvarchar(400) ''x:field[@name="Host"]/x:field[@name="Detail"]/x:value''
, GardenSize float ''x:field[@name="Garden"]/x:field[@name="Size"]/x:value''
, GardenL float ''x:field[@name="Garden"]/x:field[@name="L"]/x:value''
, GardenW float ''x:field[@name="Garden"]/x:field[@name="W"]/x:value''
-- also known as ''Farm Used''
, GardenUsedSize float ''x:field[@name="GardenUsed"]/x:field[@name="Size"]/x:value''
, GardenUsedL float ''x:field[@name="GardenUsed"]/x:field[@name="L"]/x:value''
, GardenUsedW float ''x:field[@name="GardenUsed"]/x:field[@name="W"]/x:value''
, PlaygroundSize float ''x:field[@name="Playground"]/x:field[@name="Size"]/x:value''
, PlaygroundL float ''x:field[@name="Playground"]/x:field[@name="L"]/x:value''
, PlaygroundW float ''x:field[@name="Playground"]/x:field[@name="W"]/x:value''
, RubbishFreq nvarchar(1) ''x:field[@name="Rubbish"]/x:field[@name="Freq"]/x:value''
, RubbishMethod nvarchar(1) ''x:field[@name="Rubbish"]/x:field[@name="Method"]/x:value''
, RubbishNote nvarchar(50) ''x:field[@name="Rubbish"]/x:field[@name="Note"]/x:value''
, RandM nvarchar(1) ''x:field[@name="RandM"]/x:field[@name="Plan"]/x:value''
, RandMResp nvarchar(10) ''x:field[@name="RandM"]/x:field[@name="Resp"]/x:value''
) X
WHERE SchoolSurvey.ssID = @SurveyID
exec audit.xfdfInsert @SurveyID, ''Survey updated'',''Site'',@@rowcount
end try
begin catch
DECLARE @err int,
@ErrorMessage NVARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT;
Select @err = @@error,
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
if @@trancount > 0
begin
rollback transaction
select @errorMessage = @errorMessage + '' The transaction was rolled back.''
end
exec audit.xfdfError @SurveyID, @ErrorMessage,''Site''
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);
return @err
end catch
END
')
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'pSurveyWrite.xfdfSite Procedure Updated Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Update pSurveyWrite.xfdfSite Procedure'
END
GO
--
-- Script to Update dbo.xmlSchoolCounts in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Updating dbo.xmlSchoolCounts Procedure'
GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
DROP PROCEDURE [dbo].[xmlSchoolCounts]
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
exec('-- =============================================
-- Author: Brian Lewis
-- Create date: 10 09 2011
-- Description: SimpleXML of school counts to feed into PAF
-- =============================================
CREATE PROCEDURE dbo.xmlSchoolCounts
-- Add the parameters for the stored procedure here
@SendAsXML int
, @xmlOut xml OUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
exec dbo.BuildRank
DECLARE @xml xml
SELECT @xml =
(
Select svyYear [@year]
, rankSchType [@schoolType]
, count(schNo) [count]
, sum(rankEnrol) [enrol]
, sum(rankEnrolM) [enrolM]
, sum(rankEnrolF) [enrolF]
, convert(decimal(7,4),convert(float,sum(case when rankEstimate = 1 then isnull(rankEnrol,0) else 0 end)) / sum(rankEnrol)) [est]
FROM SurveyYearRank
GROUP BY svyYear
, rankSchType
FOR XML PATH(''SchoolCount'')
)
SELECT @xmlOut=
(
SELECT @xml
FOR XML PATH(''SchoolCounts'')
)
END
')
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'dbo.xmlSchoolCounts Procedure Updated Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Update dbo.xmlSchoolCounts Procedure'
END
GO
--
-- Script to Update dbo.lkpLevels in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Updating dbo.lkpLevels Table'
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
IF EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_lkpLevels_EducationSectors')
ALTER TABLE [dbo].[lkpLevels] DROP CONSTRAINT [FK_lkpLevels_EducationSectors]
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = N'FK_lkpLevels_EducationSectors')
ALTER TABLE [dbo].[lkpLevels] ADD CONSTRAINT [FK_lkpLevels_EducationSectors] FOREIGN KEY ([secCode]) REFERENCES [dbo].[EducationSectors] ([secCode])
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'dbo.lkpLevels Table Updated Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Update dbo.lkpLevels Table'
END
GO
--
-- Script to Create dbo.QuarterlyReport in VCNSQL90.webhost4life.com.pineapples
-- Generated Sunday, August 21, 2016, at 09:13 PM
--
-- Please backup VCNSQL90.webhost4life.com.pineapples before executing this script
--
-- ** Script Begin **
BEGIN TRANSACTION
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT 'Creating dbo.QuarterlyReport Table'
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
IF EXISTS (SELECT name FROM sysobjects WHERE name = N'FK__QuarterlyR__qrID__71E7C201')
ALTER TABLE [dbo].[QuarterlyReport] DROP CONSTRAINT [FK__QuarterlyR__qrID__71E7C201]
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = N'FK__QuarterlyR__qrID__71E7C201')
ALTER TABLE [dbo].[QuarterlyReport] ADD CONSTRAINT [FK__QuarterlyR__qrID__71E7C201] FOREIGN KEY ([qrID]) REFERENCES [dbo].[SchoolInspection] ([inspID]) ON UPDATE CASCADE ON DELETE CASCADE
GO
IF @@ERROR <> 0
IF @@TRANCOUNT = 1 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT = 1
BEGIN
PRINT 'dbo.QuarterlyReport Table Added Successfully'
COMMIT TRANSACTION
END ELSE
BEGIN
PRINT 'Failed To Add dbo.QuarterlyReport Table'
END
GO
Comments (1)
-
- Log in to comment
I think this can be closed.