STATS Export Explained

Owned by Dmitry Vinarsky (Unlicensed)
Last updated: Sept 03, 2024 by David Rogers

This article also in Synergetic help portal minus SQL queries https://help.synergetic.net.au/s/article/STATS-Export-Explained

Navigation

SynMain > Students > Student Data Import/Export... > STATS

Summary

This KB article is a guide to set up, interpret and troubleshoot the STATS export. 

Interpreting the STATS export (How is it Calculated)

  • School Days - Calculates number of days between Start Date and End Date selected for the Export not counting weekends / public holidays or school holidays (this is based on Timetable Maintenance > Calendar Events tab)
  • Enrollment Days - Total number of possible attendance days for the students in the relevant YearLevel / gender / Indigenous Status in the period selected
  • Creates a list of students that fit Year Level / Gender criteria (ie Year 9 Males etc)
  • For each student: 
    • If Entry Date (when student began study) > Start Date then student is a LATE ARRIVAL 
    • If Leaving Date (when student has left the school) < End Date then student is an EARLY LEAVER
    • If Student has been enrolled from the Start Date to the End Date then Enrollment Days = School Days
  •  Enrollment Days for selected Year Level / Gender = Sum of Enrollment Days for each student in that group
  • Attendance Days - Total number of actual attendance days for the students in the relevant YearLevel / gender / Indigenous Status in the period selected
  • Student Count Under Ninety - Count of the total number of students in the relevant YearLevel / gender / Indigenous Status who attended under 90%
  • Student Count Over Ninety - Count of the total number of students in the relevant YearLevel / gender / Indigenous Status who attended over 90%
  • School Days Over Ninety - Count of the total number of school days in the relevant YearLevel / gender / Indigenous Status for students who attended over 90%
  • Indigenous Status - 'I' for Indigenous or T' for everyone else
  • Collection - time period that the report is for

Additional Information and Error Checking with SQL Management Studio

Running the export using SQL Management Studio (No Debugging Mode)


(copy and paste this code into an SQL Query to generate the STATS export)

This will produce a spreadsheet that will look identical to the export generated by the STATS export in Synergetic, with the addition of column headers.

EXEC dbo.spsStudentDataExportSTATS-- @RETURN_VALUE = 0,
@ExportType = 'Term 3',
@StartDate = '15 Jul 2019 12:00 AM',
@EndDate = '13 Sep 2019 12:00 AM',
@Campus = '',
@YearLevelFrom = 1,
@YearLevelTo = 12,
@ReportMonth = '',
@DebugPrintFlag = 0


Running the export using SQL Management Studio (Debugging Mode).


(copy and paste this code into an SQL query to generate the STATS export with debugging enabled)

This is useful if you believe that the export data is incorrect NOTE the DebugPrintFlag is set to 1, this query will produce an additional three tables compared to the No Debug query.


EXEC dbo.spsStudentDataExportSTATS

-- @RETURN_VALUE = 0,
@ExportType = 'Term 3',
@StartDate = '15 Jul 2019 12:00 AM',
@EndDate = '13 Sep 2019 12:00 AM',
@Campus = '',
@YearLevelFrom = 1,
@YearLevelTo = 12,
@ReportMonth = '',
@DebugPrintFlag = 1


Additional Table 1

Sorted by school date this table displays which students were in attendance on each school date for the reporting period.  

Additional Table 2

Displays additional information regarding attendance of Indigenous students

Additional Table 3

Ordered by year level this table displays the total number of days attended by each year level sorted by gender and indigenous status


Common Issues and how to troubleshoot

Number of School Days is wrong

Go to Students > Timetable Maintenance > Calendar Events - check to make sure all public holidays and beginning of school holidays are marked as D0

Check School Date column in Additional Table 1 (debugging mode) to make sure all included dates are in the reporting period and aren't non-school days

Incorrect number of students included in export

All students that are not classified as EXTERNAL and are present for any part of the export period will be included in the export

If students are boarders this does not count as EXTERNAL

As a troubleshooting step check the Student ID's that appear in Additional Table 1 against ID's of Students expected to be in the export

Incorrect attendance (student count over/under 90%)

Check absence event daily calc

select * from AbsenceEventsDailyCalc
where absencedate >= 'yyyymmdd' and AbsenceDate < 'yyyymmdd' 

If this is incorrect backup and delete records in AbsenceEventsDailyCalc and re-run the proc

Begin Tran

Select *
INTO scratch.AbsenceEventsDailyCalcYYYYMMDD
FROM dbo.AbsenceEventsDailyCalc
where absencedate >= 'YYYYMMDD' and AbsenceDate < 'YYYYMMDD' 

Delete --Select *
FROM dbo.AbsenceEventsDailyCalc
where absencedate >= 'YYYYMMDD' and AbsenceDate < 'YYYYMMDD' 

EXEC dbo.spgAbsenceEventsDailyCalc

Select *
FROM dbo.AbsenceEventsDailyCalc
where absencedate >= 'YYYYMMDD' and AbsenceDate < 'YYYYMMDD' 

ROLLBACK
--COMMIT

spsStudentDataExportsSTATS (code)

DECLARE
@wkDate DATETIME,
@DESTNumber VARCHAR(12),
@wkStartDate DATETIME,
@wkEndDate DATETIME,
@wkCampus VARCHAR(3),
@wkYearLevelFrom INTEGER,
@wkYearLevelTo INTEGER,
@CEOVICFlag BIT = 0,
@SchoolSystemCode VARCHAR(30)

SELECT
@CEOVICFlag = 1
FROM
dbo.RefModule
WHERE
Code = 'CEOVIC'
AND ModuleInstalledFlag= 1

SELECT
@SchoolSystemCode = SchoolSystemCode--SELECT *
FROM
dbo.School

SELECT
@wkStartDate = dbo.fnDateOnly(@StartDate),
@wkEndDate = dbo.fnDateOnly(@EndDate),
--@wkExportType = @ExportType,
@wkStartDate = @StartDate,
@wkEndDate = @EndDate,
@wkCampus = @Campus,
@wkYearLevelFrom = @YearLevelFrom,
@wkYearLevelTo = @YearLevelTo


DECLARE TimetableGroupCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT
d.[Date],
a.TimetableGroup
FROM
dbo.Dates d
CROSS JOIN
(
SELECT DISTINCT
TimetableGroup
FROM
dbo.luYearLevel l
WHERE
Code BETWEEN @wkYearLevelFrom AND @wkYearLevelTo
AND @wkCampus IN ('',l.Campus)
) a
WHERE
d.[Date] >=@wkStartDate
AND d.[Date] <=@wkEndDate

IF OBJECT_ID('tempdb..#SchoolDates') IS NOT NULL
DROP TABLE #SchoolDates

CREATE TABLE #SchoolDates
(
TimetableGroup VARCHAR(15),
SchoolDate DATETIME,
DayNumber TINYINT,
FileYear INT,
FileSemester INT
)

CREATE NONCLUSTERED INDEX #SchoolDates_idx ON #SchoolDates (SchoolDate)

DECLARE
@wkTimetableGroup VARCHAR(15),
@wkDayNumber TINYINT,
@wkMessage VARCHAR(100),
-- 01/04/2010 AR10543, remove hard coded saturday and sunday
@WeekendDayName1 VARCHAR(10),
@WeekendDayName2 VARCHAR(10)

SET @WeekendDayName1 = dbo.fnGetWeekendDayName(1, 0)
SET @WeekendDayName2 = dbo.fnGetWeekendDayName(2, 0)

--SET @wkDate = @pmDateFrom

OPEN TimetableGroupCursor
FETCH NEXT FROM TimetableGroupCursor INTO @wkDate,@wkTimetableGroup
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT (DATENAME(dw, @wkDate) IN (@WeekendDayName1, @WeekendDayName2))
BEGIN

-- SELECT @wkTimetableGroup AS TTGroup, @wkDate AS wkDate

--calc day number
EXEC dbo.[spgTimetableDay]
@TimeTableGroup = @wkTimetableGroup,
@Date = @wkDate,
@CurrentDay = @wkDayNumber OUTPUT,
@MessageInDataSetFlag = 0,
@Message = @wkMessage OUTPUT

IF @wkDayNumber <> 0
INSERT INTO #SchoolDates(TimetableGroup, SchoolDate, DayNumber)
VALUES(@wkTimetableGroup, @wkDate, @wkDayNumber)

END
FETCH NEXT FROM TimetableGroupCursor INTO @wkDate,@wkTimetableGroup
END --TimetableGroupCursor

CLOSE TimetableGroupCursor
DEALLOCATE TimetableGroupCursor

UPDATE sd
SET --select
FileYear = fs2.FileYear,
FileSemester = fs2.FileSemester
FROM
#SchoolDates sd
INNER JOIN dbo.FileSemesters fs2 ON
(
fs2.FileSemestersSeq = (
SELECT top (1) fs.FileSemestersSeq
FROM dbo.FileSemesters fs
WHERE sd.SchoolDate >= fs.StartDate
AND sd.SchoolDate <= fs.EndDate
AND Year(sd.SchoolDate) = fs.FileYear
AND fs.SeasonType = 'Numbered'
ORDER BY fs.FileSemester
)
)

SELECT
@wkDate = @wkStartDate,
@DESTNumber =
(SELECT [value]
FROM dbo.Config
WHERE Key1 = 'ExternalSystem'
AND Key2 = 'MCEETYA'
AND Key3 = 'SchoolCode')

-- Create table list for applicable students

IF OBJECT_ID('tempdb..#StudentList') IS NOT NULL
DROP TABLE #StudentList

CREATE TABLE #StudentList
(
ID INT,
FileYear INT,
Campus VARCHAR(5),
YearLevel SMALLINT,
Gender VARCHAR(1),
IndigenousFlag BIT,
EntryDate DATETIME,
LeavingDate DATETIME,
SchoolDate DATETIME
)

INSERT INTO #StudentList
(
ID,
FileYear,
Campus,
YearLevel,
Gender,
IndigenousFlag,
EntryDate,
LeavingDate,
SchoolDate
)
SELECT
SY.ID,
SY.FileYear,
SY.StudentCampus,
SY.YearLevel,
C.Gender,
CASE WHEN S.IndigenousFlag =1 OR s.TSIFlag = 1 THEN 1 ELSE 0 END,
s.EntryDate,
sy.LeavingDate,
sd.SchoolDate
FROM
dbo.StudentYears SY
INNER JOIN dbo.Students S ON
(
SY.ID = S.ID
)
INNER JOIN dbo.Community C ON
(
C.ID = SY.ID
)
INNER JOIN dbo.luStudentStatus LSS ON
(
LSS.Code = SY.[Status]
)
INNER JOIN dbo.luYearLevel ly ON
(
ly.Campus = sy.StudentCampus
AND ly.Code = sy.YearLevel
)
CROSS JOIN #SchoolDates sd

WHERE
S.EntryDate <= sd.SchoolDate
AND ISNULL(SY.LeavingDate, '31 DEC 9999') >= sd.SchoolDate
AND sd.TimetableGroup = ly.TimetableGroup
AND SY.SchoolFTE = 1
AND LSS.SynergyMeaning NOT IN ('External')
AND s.ExternalStudentFlag <> 1
AND @wkCampus IN ('',sy.StudentCampus)
AND SY.YearLevel BETWEEN @wkYearLevelFrom AND @wkYearLevelTo
AND ly.IncludeInCensusFlag = 1
AND sd.FileYear = sy.FileYear
UNION ALL
SELECT
SY.ID,
SY.FileYear,
SY.StudentCampus,
SY.YearLevel,
C.Gender,
CASE WHEN pso.IndigenousFlag =1 OR pso.TSIFlag = 1 THEN 1 ELSE 0 END,
s.EntryDate,
sy.LeavingDate,
sd.SchoolDate
FROM
dbo.PastStudentYears SY
INNER JOIN dbo.PastStudents S ON
(
SY.ID = S.ID
)
INNER JOIN dbo.PastStudentsOriginal pso ON
(
pso.ID = s.ID
)
INNER JOIN dbo.Community C ON
(
C.ID = SY.ID
)
INNER JOIN dbo.luStudentStatus LSS ON
(
LSS.Code = SY.[Status]
)
INNER JOIN dbo.luYearLevel ly ON
(
ly.Campus = sy.StudentCampus
AND ly.Code = sy.YearLevel
)
CROSS JOIN #SchoolDates sd
WHERE
S.EntryDate <= sd.SchoolDate
AND ISNULL(SY.LeavingDate, '31 DEC 9999') >= sd.SchoolDate
AND sd.TimetableGroup = ly.TimetableGroup
AND SY.SchoolFTE = 1
AND LSS.SynergyMeaning NOT IN ('External')
AND pso.ExternalStudentFlag <> 1
AND @wkCampus IN ('',sy.StudentCampus)
AND SY.YearLevel BETWEEN @wkYearLevelFrom AND @wkYearLevelTo
AND ly.IncludeInCensusFlag = 1
AND sd.FileYear = sy.FileYear


CREATE NONCLUSTERED INDEX #StudentList_idx ON #StudentList (ID,SchoolDate)

IF @DebugPrintFlag = 1
BEGIN
PRINT '#StudentList'
SELECT
ID,
FileYear,
Campus,
YearLevel,
Gender,
IndigenousFlag,
EntryDate,
LeavingDate,
SchoolDate
FROM
#StudentList
END


-- Create table list for Year Levels
IF OBJECT_ID('tempdb..#StudentCounts') IS NOT NULL
DROP TABLE #StudentCounts

CREATE TABLE #StudentCounts
(
SchoolDate DATETIME,
Campus VARCHAR(5),
YearLevel SMALLINT,
Gender VARCHAR(1),
IndigenousFlag BIT,
StudentCount INT
)

INSERT INTO #StudentCounts
(
SchoolDate,
Campus,
YearLevel,
Gender,
IndigenousFlag,
StudentCount
)
SELECT
SchoolDate,
Campus,
YearLevel,
Gender,
IndigenousFlag,
COUNT(*)
FROM
#StudentList
GROUP BY
SchoolDate,
Campus,
YearLevel,
Gender,
IndigenousFlag

IF @DebugPrintFlag = 1
BEGIN
PRINT '#StudentContacts'
SELECT
SchoolDate,
Campus,
YearLevel,
Gender,
IndigenousFlag
FROM #StudentCounts
END

IF OBJECT_ID('tempdb..#YearLevels') IS NOT NULL
DROP TABLE #YearLevels

CREATE TABLE #YearLevels
(
Campus VARCHAR(5),
DESTCampusNumber VARCHAR(30),
YearLevel INT,
YearLevelSort INT,
TotalMales INT,
TotalFemales INT,
TotalIndigenousMales INT,
TotalIndigenousFemales INT,
TimetableGroup VARCHAR(15),
SchoolDate DATETIME
)


INSERT INTO #YearLevels
(
Campus,
DESTCampusNumber,
YearLevel,
YearLevelSort,
TotalMales,
TotalFemales,
TotalIndigenousMales,
TotalIndigenousFemales,
TimetableGroup,
SchoolDate
)
SELECT -- multiple select needed to include on insert
yl.Campus,
c.MCEETYASchoolCode,
YearLevel = yl.Code,
yl.YearLevelSort,
TotalMales = ISNULL((
SELECT SUM(StudentCount)
FROM #StudentCounts s
WHERE
s.Gender = 'M'
AND s.Campus = yl.Campus
AND s.YearLevel = yl.Code
AND YEAR(s.SchoolDate) = sd.SchoolDate
),0),
TotalFemales = ISNULL((
SELECT SUM(StudentCount)
FROM #StudentCounts s
WHERE
s.Gender = 'F'
AND s.Campus = yl.Campus
AND s.YearLevel = yl.Code
AND YEAR(s.SchoolDate) = sd.SchoolDate
),0),
TotalIndigenousMales = ISNULL((
SELECT SUM(StudentCount)
FROM #StudentCounts s
WHERE
s.Gender = 'M'
AND IndigenousFlag = 1
AND s.Campus = yl.Campus
AND s.YearLevel = yl.Code
AND YEAR(s.SchoolDate) = sd.SchoolDate
),0),
TotalIndigenousFemales = ISNULL((
SELECT SUM(StudentCount)
FROM #StudentCounts s
WHERE
s.Gender = 'F'
AND IndigenousFlag = 1
AND s.Campus = yl.Campus
AND s.YearLevel = yl.Code
AND YEAR(s.SchoolDate) = sd.SchoolDate
),0),
yl.TimetableGroup,
CONVERT(DATETIME,'1 Jan ' + CONVERT(VARCHAR(30),sd.SchoolDate))
FROM
dbo.luYearLevel yl
INNER JOIN dbo.luCampus c ON
(
c.Code = yl.Campus
)
CROSS JOIN (
SELECT
YEAR(Schooldate) SchoolDate
FROM
#SchoolDates
GROUP BY
YEAR(Schooldate)) sd
WHERE
yl.Code BETWEEN @wkYearLevelFrom AND @wkYearLevelTo
AND @wkCampus IN ('',yl.Campus)
AND yl.IncludeInCensusFlag = 1


IF @DebugPrintFlag = 1
BEGIN
PRINT '#'
SELECT
Campus,
DESTCampusNumber,
YearLevel,
YearLevelSort,
TotalMales,
TotalFemales,
TotalIndigenousMales,
TotalIndigenousFemales,
TimetableGroup,
SchoolDate
FROM
#YearLevels
END

IF OBJECT_ID('tempdb..#AbsencePeriod') IS NOT NULL
DROP TABLE #AbsencePeriod

CREATE TABLE #AbsencePeriod
(
AbsenceDate DATETIME,
ID INT,
AbsencePeriod VARCHAR(10),
AbsenceCount FLOAT,
AbsenceEventsSeq INT,
AttendanceSeq INT
)

INSERT INTO #AbsencePeriod
(
AbsenceDate,
ID,
AbsencePeriod,
AbsenceCount,
AbsenceEventsSeq,
AttendanceSeq
)
SELECT
ae.AbsenceDate,
AE.ID,
'',
ae.AbsenceFactor,
NULL,
NULL
FROM
dbo.AbsenceEventsDailyCalc AE
INNER JOIN #StudentList S ON
(
S.ID = AE.ID
)
WHERE
ae.AbsenceDate = s.SchoolDate
AND dbo.fnDateOnly(ae.AbsenceDate) >= @wkStartDate
AND dbo.fnDateOnly(ae.AbsenceDate) <= @wkEndDate


IF OBJECT_ID('tempdb..#TotalAbsences') IS NOT NULL
DROP TABLE #TotalAbsences

CREATE TABLE #TotalAbsences
(
AbsenceDate DATETIME,
ID INT,
Total DECIMAL(8,2)
)
INSERT INTO #TotalAbsences
(
AbsenceDate,
ID,
Total
)
SELECT
AbsenceDate,
ID,
CASE
WHEN SUM(AbsenceCOUNT) > 1
THEN 1
ELSE SUM(AbsenceCount)
END
FROM
#AbsencePeriod
GROUP BY
AbsenceDate,ID

IF OBJECT_ID('tempdb..#StudentPercentages') IS NOT NULL
DROP TABLE #StudentPercentages

SELECT
s.ID,
s.Campus,
s.YearLevel,
s.Gender,
s.IndigenousFlag,
l.TimetableGroup,
SchoolDays = COUNT(*) ,
[Percentage] = (CONVERT(DECIMAL(8,4),(SELECT COUNT(sl.ID) FROM #StudentList sl WHERE sl.ID = s.ID) - ISNULL((SELECT SUM(Total) FROM #TotalAbsences ta WHERE ta.ID = s.ID),0)) / CONVERT(DECIMAL(8,4),(SELECT COUNT(sl.ID) FROM #StudentList sl WHERE sl.ID = s.ID)))
INTO
#StudentPercentages
FROM
#StudentList s
INNER JOIN dbo.luYearLevel l ON
(
l.Campus = s.Campus
AND l.Code = s.YearLevel
)
GROUP BY
s.ID,
s.Campus,
s.YearLevel,
s.Gender,
s.IndigenousFlag,
l.TimetableGroup

IF OBJECT_ID('tempdb..#TotalStudentPercentages') IS NOT NULL
DROP TABLE #TotalStudentPercentages

SELECT
Campus,
YearLevel,
Gender,
IndigenousFlag,
TimetableGroup,
STudentCountOverNinety = SUM(CASE WHEN Percentage >= .9 THEN 1 ELSE 0 END),
StudentCountUnderNinety = SUM(CASE WHEN Percentage < .9 THEN 1 ELSE 0 END),
SchoolDays = SUM(CASE WHEN Percentage >= .9 THEN SchoolDays ELSE 0 END)
INTO
#TotalStudentPercentages
FROM
#StudentPercentages
GROUP BY
YearLevel,
Campus,
Gender,
IndigenousFlag,
TimetableGroup

IF @CEOVICFlag = 1
DELETE FROM
#YearLevels
WHERE
YearLevel < 1
OR YearLevel > 10

IF OBJECT_ID('tempdb..#ExportSTATS') IS NOT NULL
DROP TABLE #ExportSTATS

CREATE TABLE #ExportSTATS
(
DEEWRID INT,
DateString VARCHAR(12),
YearLevel VARCHAR(10),
TotalMales INT,
TotalFemales INT,
TotalIndigenousMale INT,
TotalIndigenousFemale INT,
TotalMaleAttendance DECIMAL(8,2),
TotalFemaleAttendance DECIMAL(8,2),
TotalIndigenousMaleAttendance DECIMAL(8,2),
TotalIndigenousFemaleAttendance DECIMAL(8,2),
YearLevelSort SMALLINT,
DateSort DATETIME,
StudentCountUnderNinetyMale INT,
STudentCountOverNinetyMale INT,
StudentCountUnderNinetyFeMale INT,
STudentCountOverNinetyFeMale INT,
StudentCountUnderNinetyIndigenousMale INT,
STudentCountOverNinetyIndigenousMale INT,
STudentCountUnderNinetyIndigenousFemale INT,
STudentCountOverNinetyIndigenousFemale INT,
SchoolDaysOverNinetyMale INT,
SchoolDaysOverNinetyFeMale INT,
SchoolDaysOverNinetyIndigenousMale INT,
SchoolDaysOverNinetyIndigenousFemale INT,
SchoolDays INT
)


INSERT INTO #ExportSTATS
(
DEEWRID,
DateString,
YearLevel,
TotalMales,
TotalFemales,
TotalIndigenousMale,
TotalIndigenousFemale,
TotalMaleAttendance,
TotalFemaleAttendance,
TotalIndigenousMaleAttendance,
TotalIndigenousFemaleAttendance,
YearLevelSort,
DateSort,
StudentCountUnderNinetyMale,
STudentCountOverNinetyMale,
StudentCountUnderNinetyFeMale,
STudentCountOverNinetyFeMale,
StudentCountUnderNinetyIndigenousMale,
STudentCountOverNinetyIndigenousMale,
STudentCountUnderNinetyIndigenousFemale,
STudentCountOverNinetyIndigenousFemale,
SchoolDaysOverNinetyMale,
SchoolDaysOverNinetyFeMale,
SchoolDaysOverNinetyIndigenousMale,
SchoolDaysOverNinetyIndigenousFemale,
SchoolDays
)
SELECT
DEEWRID = CASE
WHEN ISNULL(NULLIF(DESTCampusNumber,0),'') <> '' THEN DESTCampusNumber
ELSE @DESTNumber
END,
[Year] = YEAR(@wkEndDate),
YearLevel = 'Y' + CONVERT(VARCHAR(30), YearLevel),
TotalMales = YL.TotalMales,
TotalFemales = YL.TotalFemales,
TotalIndigenousMale = YL.TotalIndigenousMales,
TotalIndigenousFemales = YL.TotalIndigenousFemales,
TotalMaleAttendance = YL.TotalMales -
ISNULL((
SELECT SUM(ta.Total)
FROM #TotalAbsences ta
INNER JOIN #StudentList S ON
(
S.ID = ta.ID
AND s.SchoolDate = ta.AbsenceDate
)
WHERE
YL.YearLevel = S.YearLevel
AND S.Gender = 'M'
AND YL.Campus = S.Campus
AND YEAR(ta.AbsenceDate) = YEAR(yl.SchoolDate)
),0),
TotalFemaleAttendance = YL.TotalFemales -
ISNULL((
SELECT SUM(ta.Total)
FROM #TotalAbsences ta
INNER JOIN #StudentList S ON
(
S.ID = ta.ID
AND s.SchoolDate = ta.AbsenceDate
)
WHERE
YL.YearLevel = S.YearLevel
AND S.Gender = 'F'
AND YL.Campus = S.Campus
AND YEAR(ta.AbsenceDate) = YEAR(yl.SchoolDate)
),0),
TotalIndigenousMaleAttendance = YL.TotalIndigenousMales -
ISNULL((
SELECT SUM(ta.Total)
FROM #TotalAbsences ta
INNER JOIN #StudentList S ON
(
S.ID = ta.ID
AND s.SchoolDate = ta.AbsenceDate
)
WHERE
YL.YearLevel = S.YearLevel
AND S.Gender = 'M'
AND YL.Campus = S.Campus
AND s.IndigenousFlag = 1
AND YEAR(ta.AbsenceDate) = YEAR(yl.SchoolDate)
),0),
TotalIndigenousFemaleAttendance = YL.TotalIndigenousFemales -
ISNULL((
SELECT SUM(ta.Total)
FROM #TotalAbsences ta
INNER JOIN #StudentList S ON
(
S.ID = ta.ID
AND s.SchoolDate = ta.AbsenceDate
)
WHERE
YL.YearLevel = S.YearLevel
AND S.Gender = 'F'
AND YL.Campus = S.Campus
AND s.IndigenousFlag = 1
AND YEAR(ta.AbsenceDate) = YEAR(yl.SchoolDate)
),0),
yl.YearLevelSort,
yl.SchoolDate,
ISNULL((SELECT SUM(StudentCountUnderNinety) FROM #TotalStudentPercentages sp WHERE sp.Campus = yl.Campus ANd sp.YearLevel = yl.YearLevel AND Gender = 'M'),0),
ISNULL((SELECT SUM(StudentCountOverNinety) FROM #TotalStudentPercentages sp WHERE sp.Campus = yl.Campus ANd sp.YearLevel = yl.YearLevel AND Gender = 'M'),0),
ISNULL((SELECT SUM(StudentCountUnderNinety) FROM #TotalStudentPercentages sp WHERE sp.Campus = yl.Campus ANd sp.YearLevel = yl.YearLevel AND Gender = 'F'),0),
ISNULL((SELECT SUM(StudentCountOverNinety) FROM #TotalStudentPercentages sp WHERE sp.Campus = yl.Campus ANd sp.YearLevel = yl.YearLevel AND Gender = 'F'),0),
ISNULL((SELECT SUM(StudentCountUnderNinety) FROM #TotalStudentPercentages sp WHERE sp.Campus = yl.Campus ANd sp.YearLevel = yl.YearLevel AND Gender = 'M' AND IndigenousFlag = 1),0),
ISNULL((SELECT SUM(StudentCountOverNinety) FROM #TotalStudentPercentages sp WHERE sp.Campus = yl.Campus ANd sp.YearLevel = yl.YearLevel AND Gender = 'M' AND IndigenousFlag = 1),0),
ISNULL((SELECT SUM(StudentCountUnderNinety) FROM #TotalStudentPercentages sp WHERE sp.Campus = yl.Campus ANd sp.YearLevel = yl.YearLevel AND Gender = 'F' AND IndigenousFlag = 1),0),
ISNULL((SELECT SUM(StudentCountOverNinety) FROM #TotalStudentPercentages sp WHERE sp.Campus = yl.Campus ANd sp.YearLevel = yl.YearLevel AND Gender = 'F' AND IndigenousFlag = 1),0),

ISNULL((SELECT SUM(SchoolDays) FROM #TotalStudentPercentages sp WHERE sp.Campus = yl.Campus ANd sp.YearLevel = yl.YearLevel AND Gender = 'M'),0),
ISNULL((SELECT SUM(SchoolDays) FROM #TotalStudentPercentages sp WHERE sp.Campus = yl.Campus ANd sp.YearLevel = yl.YearLevel AND Gender = 'F'),0),
ISNULL((SELECT SUM(SchoolDays) FROM #TotalStudentPercentages sp WHERE sp.Campus = yl.Campus ANd sp.YearLevel = yl.YearLevel AND Gender = 'M' AND IndigenousFlag = 1),0),
ISNULL((SELECT SUM(SchoolDays) FROM #TotalStudentPercentages sp WHERE sp.Campus = yl.Campus ANd sp.YearLevel = yl.YearLevel AND Gender = 'F' AND IndigenousFlag = 1),0),
SchoolDays = (SELECT COUNT(sc.TimetableGroup) FROM #SchoolDates sc
INNER JOIN dbo.luYearLevel lu ON (lu.TimetableGroup = sc.TimetableGroup)
WHERE lu.Campus = yl.Campus
AND lu.Code = yl.YearLevel)
FROM
#YearLevels YL
ORDER BY
YEAR(@wkEndDate),
YearLevelSort

IF OBJECT_ID('tempdb..#Export') IS NOT NULL
DROP TABLE #Export

CREATE TABLE #Export
(
DEEWRID INT,
[Collection] VARCHAR(100),
YearLevel VARCHAR(10),
Gender CHAR(1),
IndigenousStatus CHAR(1),
EnrolmentDays INT,
AttendanceDays DECIMAL(8,2),
StudentCountUnderNinety INT,
StudentCountOverNinety INT,
YearLevelSort SMALLINT,
SchoolDaysOverNinety SMALLINT,
SchoolDays SMALLINT
)

INSERT INTO #Export
(
DEEWRID,
[Collection],
YearLevel,
Gender,
IndigenousStatus,
EnrolmentDays,
AttendanceDays,
StudentCountUnderNinety,
StudentCountOverNinety,
YearLevelSort,
SchoolDaysOverNinety,
SchoolDays
)
SELECT
DEEWRID,
[Collection] = @ExportType ,
MAX(e.YearLevel),
'M' Gender,
'T' IndigenousStatus,
TotalMales = SUM(TotalMales),
TotalMaleAttendance = SUM(TotalMaleAttendance),
SUM(e.StudentCountUnderNinetyMale),
SUM(e.STudentCountOverNinetyMale),
e.YearLevelSort,
SUM(SchoolDaysOverNinetyMale),
MAX(SchoolDays)
FROM
#ExportSTATS e
GROUP BY
DEEWRID,
e.YearLevelSort

UNION

SELECT
DEEWRID,
@ExportType,
MAX(e.YearLevel),
'F' Gender,
'T',
TotalMales = SUM(TotalFemales),
TotalMaleAttendance = SUM(TotalFemaleAttendance),
SUM(e.StudentCountUnderNinetyFeMale),
SUM(e.STudentCountOverNinetyFeMale),
e.YearLevelSort,
SUM(SchoolDaysOverNinetyFemale),
MAX(SchoolDays)
FROM
#ExportSTATS e
GROUP BY
DEEWRID,
e.YearLevelSort

UNION
SELECT
DEEWRID,
@ExportType,
MAX(e.YearLevel),
'M' Gender,
'I',
TotalMales = SUM(TotalIndigenousMale),
TotalMaleAttendance = SUM(TotalIndigenousMaleAttendance),
SUM(e.StudentCountUnderNinetyIndigenousMale),
SUM(e.StudentCountOverNinetyIndigenousMale),
e.YearLevelSort,
SUM(SchoolDaysOverNinetyIndigenousMale),
MAX(SchoolDays)
FROM
#ExportSTATS e
GROUP BY
DEEWRID,
e.YearLevelSort

UNION
SELECT
DEEWRID,
@ExportType,
MAX(e.YearLevel),
'F' Gender,
'I',
TotalMales = SUM(TotalIndigenousFeMale),
TotalMaleAttendance = SUM(TotalIndigenousFemaleAttendance),
SUM(e.STudentCountUnderNinetyIndigenousFemale),
SUM(e.STudentCountOverNinetyIndigenousFemale),
YearLevelSort,
SUM(SchoolDaysOverNinetyIndigenousFemale),
MAX(SchoolDays)
FROM
#ExportSTATS e
GROUP BY
DEEWRID,
e.YearLevelSort
ORDER BY
DEEWRID,
e.YearLevelSort,
Gender,
IndigenousStatus

--IF EXISTS(SELECT * FROM #Export WHERE )
--BEGIN
-- RAISERROR('Number of School days outside of range.')
-- RETURN
--END

IF @CEOVICFlag = 0
BEGIN
SELECT
DEEWRID,
[Collection],
CONVERT(VARCHAR(30),YearLevel),
Gender,
IndigenousStatus,
EnrolmentDays,
AttendanceDays,
StudentCountUnderNinety,
StudentCountOverNinety,
SchoolDaysOverNinety,
SchoolDays
FROM
#Export e
WHERE
EnrolmentDays <> 0
END
ELSE
BEGIN
SELECT
@SchoolSystemCode,
DEEWRID,
[Collection],
YearLevel,
Gender,
IndigenousStatus,
EnrolmentDays,
AttendanceDays,
StudentCountUnderNinety,
StudentCountOverNinety,
SchoolDaysOverNinety,
SchoolDays,
YEAR(@StartDate),
'SynergeticManual'
FROM
#Export e
WHERE
EnrolmentDays <> 0
END
END