True
MainConnection
4FrZz09dQ6RsW+2Yd0miD9V8wQtNUDHNy2Wl3PEcor/CYNHrxVmrrWhshPhiZOFniXr1jF1xAd1rTKGteizjKuRa4etNBUOZ4k6EGOR0YGfCeGmDY0qS9Wlu/cx6SeB383C/D0xKa4XhFaHcZ3CkE9wS++vZS6PZagWlmGI9dw/2cFMC2F1z
MainConnection
MainData
PupilID,System.String
ForeName,System.String
Surname,System.String
TotalSessions,System.Int32
Presents,System.Int32
Absents,System.Int32
PresentPercent,System.Decimal
SessionsThisYear,System.Int32
ReportStartDate,System.DateTime
ReportEndDate,System.DateTime
SchoolID,System.String
SessionsLeftThiYear,System.Int32
Description,System.String
YearGroup,System.String
30
MainData
MainConnection
_x0040_Startdate,dtpStart.Value,4,0
_x0040_SchoolYear,SchoolYearInt,8,0
SELECT @StartDate =CAST(CONVERT(NVARCHAR(10), @StartDate, 120) + ' 00:00:00' as DATETIME)
SELECT @StartDate = DATEADD(DD, 2 -DATEPART(DW, @StartDate), @StartDate)
DECLARE @EndDate DATETIME = DATEADD(d,6,@StartDate)
DECLARE @PrevWeekStartDate DATETIME, @PrevWeekEndDate DATETIME
SELECT @PrevWeekStartDate = DATEADD(d,-7,@StartDate), @PrevWeekEndDate = DATEADD(d,-7,@EndDate)
DECLARE @SchoolYearStart DATETIME
DECLARE @SchoolYearEnd DATETIME
DECLARE @TermDates TABLE (Dates DATETIME, IsSchoolHol NVARCHAR(6))
SELECT @SchoolYearStart = MIN(StartDate), @SchoolYearEnd = MAX(EndDate)
FROM SchoolTerms
GROUP BY SchoolYear
HAVING
@StartDate BETWEEN MIN(StartDate) AND MAX(EndDate)
OR @EndDate BETWEEN MIN(StartDate) AND MAX(EndDate)
;WITH dates AS
(
SELECT @SchoolYearStart AS [Date]
UNION ALL
SELECT DATEADD(d,1,[Date]) FROM dates WHERE [Date] <@SchoolYearEnd
)
INSERT INTO @TermDates
SELECT Dts.Date, CASE WHEN ISNULL(CAST(Hol.StartDate AS NVARCHAR),'FALSE') = 'FALSE' THEN 'FALSE' ELSE 'TRUE' END from dates Dts --where DATENAME(dt) NOT IN ('Saturday','Sunday')
LEFT OUTER JOIN
(
-- Term Breaks
SELECT
StartDate,
DATEADD(d, 1, DATEADD(s, -1, EndDate)) AS EndDate
FROM TermBreaks
WHERE StartDate BETWEEN @SchoolYearStart AND @SchoolYearEnd
UNION ALL
-- Bank Holidays
SELECT
HolidayDate,
DATEADD(d, 1, DATEADD(s, -1, HolidayDate))
FROM BankHolidays
WHERE HolidayDate BETWEEN @SchoolYearStart AND @SchoolYearEnd
UNION ALL
-- Gaps between Terms
SELECT
DATEADD(d, 1, ST.EndDate),
DATEADD(s, -1, (SELECT MIN(StartDate) NextTermStartDate FROM SchoolTerms ST2 WHERE ST2.StartDate > ST.EndDate)) EndDate
FROM SchoolTerms ST
UNION ALL
-- School Events
SELECT
EventDate,
DATEADD(d, 1, DATEADD(s, -1, EventDate))
FROM SchoolEvents
WHERE
IsSchoolClosed = 1
AND EventDate Between @SchoolYearStart AND @SchoolYearEnd
)
Hol ON Dts.[Date] BETWEEN Hol.StartDate AND Hol.EndDate
OPTION (MAXRECURSION 0)
DECLARE @SessionsInSchoolYear INT
SELECT @SessionsInSchoolYear = COUNT(Dates) * 2
FROM @TermDates
WHERE
IsSchoolHol = 'FALSE'
AND DATENAME(dw,Dates) NOT IN ('Saturday', 'Sunday')
DECLARE @SessionsLeftThiYear INT
SELECT @SessionsLeftThiYear = COUNT(Dates) * 2
FROM @TermDates
WHERE
IsSchoolHol = 'FALSE'
AND DATENAME(dw,Dates) NOT IN ('Saturday', 'Sunday')
AND Dates>@EndDate
DECLARE @PrevAttendance TABLE (PupilID NVARCHAR(20), PresentPercent DECIMAL)
INSERT INTO @PrevAttendance
SELECT
PSA.PupilID,
(CAST(SUM(CASE WHEN AT.IsInAttendance = 1 THEN 1 ELSE 0 END) AS DECIMAL) / CAST(COUNT(PSA.PupilID) AS DECIMAL)) * 100
FROM PupilSessionAttendance PSA
INNER LOOP JOIN AbsenceTypes AT
ON PSA.AbsenceTypeID = AT.AbsenceTypeID
AND PSA.AttendanceDate BETWEEN @PrevWeekStartDate AND @PrevWeekEndDate
GROUP BY PSA.PupilID
--Create a table to decide if you should use Period or Session attendance from the Attendance Parameters set up in Engage to avoid duplicate record taking.
DECLARE @periodStruc TABLE (
academicYear int,
clusterID nvarchar(20),
schoolid nvarchar(20),
yearGroup nvarchar(5),
dayOfWeek int,
periodNo int,
sessionid nvarchar(5)
)
-- yeargroup level
INSERT INTO @periodStruc (academicYear, clusterID, schoolid, yearGroup, dayOfWeek, periodNo, sessionID)
SELECT
aps.academicYear,
aps.clusterID,
aps.SchoolID,
aps.yearGroup,
aps.dayOfWeek,
aps.PeriodID,
atts.AttendanceSession
FROM
AttendancePeriods aps
LEFT OUTER JOIN AttendanceSessions atts
ON aps.schoolid = atts.SchoolID
AND atts.AcademicYear = aps.academicYear
AND atts.YearGroup = aps.yearGroup
AND atts.DayOfWeek = aps.dayOfWeek
AND atts.AttendancePeriodNo = aps.PeriodID
WHERE
aps.YearGroup <> ''
AND aps.AcademicYear = @SchoolYear
-- school level
INSERT INTO @periodStruc (academicYear, clusterID, schoolid, yearGroup, dayOfWeek, periodNo, sessionID)
SELECT
aps.academicYear,
aps.clusterID,
aps.SchoolID,
yglu.LookupDetailsID,
aps.dayOfWeek,
aps.PeriodID,
atts.AttendanceSession
FROM
AttendancePeriods aps
INNER JOIN LookupDetails yglu
ON yglu.LookupID = 3004
AND LookupDetailsID <> '0000'
LEFT OUTER JOIN AttendanceSessions atts
ON aps.schoolid = atts.SchoolID
AND atts.AcademicYear = aps.academicYear
AND atts.YearGroup = aps.yearGroup
AND atts.DayOfWeek = aps.dayOfWeek
AND atts.AttendancePeriodNo = aps.PeriodID
WHERE
aps.YearGroup = ''
AND aps.schoolid <> ''
AND aps.AcademicYear = @SchoolYear
AND yglu.LookupDetailsID NOT IN (SELECT YearGroup FROM @periodStruc)
-- cluster level
INSERT INTO @periodStruc (academicYear, clusterID, schoolid, yearGroup, dayOfWeek, periodNo, sessionID)
SELECT
aps.academicYear,
aps.clusterID,
aps.SchoolID,
yglu.LookupDetailsID,
aps.dayOfWeek,
aps.PeriodID,
atts.AttendanceSession
FROM AttendancePeriods aps
INNER JOIN LookupDetails yglu
ON yglu.LookupID = 3004
AND LookupDetailsID <> '0000'
LEFT OUTER JOIN AttendanceSessions atts
ON aps.schoolid = atts.SchoolID
AND atts.AcademicYear = aps.academicYear
AND atts.YearGroup = aps.yearGroup
AND atts.DayOfWeek = aps.dayOfWeek
AND atts.AttendancePeriodNo = aps.PeriodID
WHERE aps.YearGroup = ''
AND aps.schoolid = ''
AND aps.ClusterID <> ''
AND aps.AcademicYear = @SchoolYear
AND yglu.LookupDetailsID NOT IN (SELECT YearGroup FROM @periodStruc)
SELECT
PPD.SchoolID,
PPD.PupilID,
PPD.ForeName,
PPD.Surname,
SLD.Description,
PCS.YearGroup,
COUNT(PPD.PupilID) AS TotalSessions,
SUM(CASE WHEN AT.IsInAttendance = 1 THEN 1 ELSE 0 END) AS Presents,
SUM(CASE WHEN AT.IsInAttendance = 0 THEN 1 ELSE 0 END) AS Absents,
PA.PresentPercent,
@SessionsInSchoolYear AS SessionsThisYear,
@SessionsLeftThiYear as SessionsLeftThiYear,
@StartDate AS ReportStartDate,
@EndDate AS ReportEndDate
FROM CurrentPupil
INNER JOIN PupilPersonalDetails PPD
ON CurrentPupil.PupilID = PPD.PupilID
AND CurrentPupil.SchoolID = PPD.SchoolID
AND ('%wc%' = '%wc%')-- AND PPD.PupilID = 1440
LEFT OUTER JOIN PupilSessionAttendance PSA
ON PPD.PupilID = PSA.PupilID
AND PPD.SchoolID = PPD.SchoolID
AND PSA.AttendanceDate BETWEEN @SchoolYearStart AND @EndDate
LEFT OUTER LOOP JOIN AbsenceTypes AT
ON PSA.AbsenceTypeID = AT.AbsenceTypeID
LEFT OUTER JOIN @PrevAttendance PA
ON PPD.PupilID = PA.PupilID
LEFT OUTER JOIN PupilCurrentSchool PCS
ON PPD.PupilID = PCS.PupilID
LEFT OUTER JOIN SchoolLookupDetails SLD
ON PCS.Form = SLD.LookupDetailsID
AND SLD.LookupID = 1002
GROUP BY
PPD.SchoolID,
PPD.PupilID,
PPD.ForeName,
PPD.Surname,
PA.PresentPercent,
PCS.YearGroup,
SLD.Description
SessionAttendance
PupilID,System.String
AttendanceDate,System.DateTime
StringDate,System.String
Weekday,System.String
AttendanceSession,System.String
DisplaySymbol,System.String
30
False
SessionAttendance
MainConnection
_x0040_PupilIDs,getPupilIDs_x0028__x0029_,12,-1
--SELECT * FROM LookupDetails WHERE LookupID = 2200
--SELECT * FROM SchoolTerms
--SELECT * FROM AbsenceTypes
--SELECT @StartDate =CAST(CONVERT(NVARCHAR(10), @StartDate, 120) + ' 00:00:00' as DATETIME)
--SELECT @StartDate = DATEADD(DD, 2 -DATEPART(DW, @StartDate), @StartDate)
DECLARE @EndDate DATETIME
DECLARE @StartDate DATETIME
DECLARE @SchoolYear INT = 2017
DECLARE @SchoolTerm NVARCHAR(20) = 'Summer Term'
SET @StartDate = (
SELECT ST.StartDate
FROM SchoolTerms ST
LEFT JOIN LookupDetails LD
ON LD.LookupDetailsID = ST.Name
WHERE
LD.Description = @SchoolTerm
AND ST.SchoolYear = @SchoolYear
AND LD.LookupID = 2200
)
SET @EndDate = (
SELECT ST.EndDate
FROM SchoolTerms ST
LEFT JOIN LookupDetails LD
ON LD.LookupDetailsID = ST.Name
WHERE
LD.Description = @SchoolTerm
AND ST.SchoolYear = @SchoolYear
AND LD.LookupID = 2200
)
--DATEADD(d,6,@StartDate)
SELECT
CP.PupilID,
PSA.AttendanceDate,
CONVERT(NVARCHAR(10), PSA.AttendanceDate, 120) AS StringDate,
DATENAME(dw,PSA.AttendanceDate) Weekday,
PSA.AttendanceSession,
AT.DisplaySymbol,
AT.AbsenceTypeID,
AT.IsInAttendance,
AT.AbsenceType,
AT.IsAuthorised
--INTO TempTable
FROM CurrentPupil CP
--INNER JOIN PupilPersonalDetails PPD ON CurrentPupil.PupilID = PPD.PupilID AND CurrentPupil.SchoolID = PPD.SchoolID
INNER JOIN PupilSessionAttendance PSA
ON CP.PupilID = PSA.PupilID
AND CP.SchoolID = CP.SchoolID
AND PSA.AttendanceDate BETWEEN @StartDate AND @EndDate
/*INNER JOIN FNSplitTextIDs(@PupilIDs) PID ON CP.PupilID = PID.id*/
LEFT OUTER LOOP JOIN AbsenceTypes AT
ON PSA.AbsenceTypeID = AT.AbsenceTypeID
Dates
StringDate,System.String
dates,System.DateTime
30
False
Dates
MainConnection
_x0040_StartDate,dtpStart.Value,4,0
/* SELECT @StartDate = DATEADD(DD, 2 -DATEPART(DW, @StartDate), @StartDate)
DECLARE @EndDate DATETIME = DATEADD(d,6,@StartDate)
;with dates as(
select @StartDate as dates
union all
select DATEADD(d,1,dates) from dates where dates < @EndDate
)
select
CONVERT(NVARCHAR(10), dates, 120) as StringDate, dates
from dates WHERE DATENAME(dw,dates) NOT IN ('Saturday','Sunday')*/
Weeks
WkNo,System.Int32
DateRange,System.String
sowDR,System.String
eowDR,System.String
SoW,System.DateTime
EoW,System.DateTime
30
False
Weeks
MainConnection
_x0040_SchoolYear,SchoolYearInt,8,4
_x0040_SchoolTerm,SchoolTerm,12,20
declare @startdate datetime;
declare @enddate datetime;
SET @startdate =
(
SELECT ST.StartDate
FROM SchoolTerms ST
LEFT JOIN LookupDetails LD
ON ST.Name = LD.LookupDetailsID
AND LD.LookupID = '2200'
WHERE
ST.SchoolYear = @SchoolYear AND
LD.Description = @SchoolTerm
)
SET @enddate =
(
SELECT ST.EndDate
FROM SchoolTerms ST
LEFT JOIN LookupDetails LD
ON ST.Name = LD.LookupDetailsID
AND LD.LookupID = '2200'
WHERE
ST.SchoolYear = @SchoolYear AND
LD.Description = @SchoolTerm
)
;WITH GetDates AS
(
SELECT
DATEADD(DAY, 0, @startdate) AS TheDate,
DATEPART(WK, DATEADD(DAY, 1, @startdate)) WkNo,
DATEPART(YEAR, DATEADD(DAY, 1, @startdate)) DateYear
UNION ALL
SELECT
DATEADD(DAY, 1, TheDate),
DATEPART(WK, DATEADD(DAY, 1, TheDate)),
DATEPART(YEAR, DATEADD(DAY, 1, TheDate))
FROM GetDates
WHERE TheDate < @enddate
)
SELECT
WkNo,
MIN(TheDate) AS SoW,
MAX(TheDate) AS EoW,
RIGHT('0' + CONVERT(VARCHAR, DATEPART(dd, CAST(MIN(TheDate) AS DATETIME))), 2) + '/' + RIGHT('0' + CONVERT(VARCHAR, DATEPART(mm, CAST(MIN(TheDate) AS DATETIME))) ,2) AS sowDR,
RIGHT('0' + CONVERT(VARCHAR, DATEPART(dd, CAST(MAX(TheDate) AS DATETIME))), 2) + '/' + RIGHT('0' + CONVERT(VARCHAR, DATEPART(mm, CAST(MAX(TheDate) AS DATETIME))), 2) AS eowDR
FROM GetDates
GROUP BY WkNo
OPTION (MAXRECURSION 0);
sTermDropDown
StartDate,System.DateTime
EndDate,System.DateTime
Description,System.String
YearTerm,System.String
SchoolYear,System.Int32
Name,System.String
30
sTermDropDown
MainConnection
SELECT TOP 12
CAST(ST.SchoolYear AS NVARCHAR(4)) + ' ~ ' + LD.Description AS YearTerm,
ST.StartDate,
ST.EndDate,
ST.SchoolYear,
ST.Name
FROM
SchoolTerms ST
LEFT JOIN
LookupDetails LD ON ST.Name = LD.LookupDetailsID AND LD.LookupID = '2200'
ORDER BY
ST.SchoolID,
ST.SchoolYear DESC,
ST.Name
PeriodClassSessionStruct
academicYear,System.Int32
clusterID,System.String
schoolid,System.String
yearGroup,System.String
dayOfWeek,System.Int32
periodNo,System.Int32
sessionid,System.String
30
False
PeriodClassSessionStruct
MainConnection
_x0040_SchoolYear,SchoolYearInt,8,0
DECLARE @periodStruc TABLE (
academicYear int,
clusterID nvarchar(20),
schoolid nvarchar(20),
yearGroup nvarchar(5),
dayOfWeek int,
periodNo int,
sessionid nvarchar(5)
)
-- yeargroup level
INSERT INTO @periodStruc (academicYear, clusterID, schoolid, yearGroup, dayOfWeek, periodNo, sessionID)
SELECT
aps.academicYear,
aps.clusterID,
aps.SchoolID,
aps.yearGroup,
aps.dayOfWeek,
aps.PeriodID,
atts.AttendanceSession
FROM AttendancePeriods aps
LEFT OUTER JOIN AttendanceSessions atts
ON aps.schoolid = atts.SchoolID
AND atts.AcademicYear = aps.academicYear
AND atts.YearGroup = aps.yearGroup
AND atts.DayOfWeek = aps.dayOfWeek
AND atts.AttendancePeriodNo = aps.PeriodID
WHERE
aps.YearGroup <> ''
AND aps.AcademicYear = @SchoolYear
-- school level
INSERT INTO @periodStruc (academicYear, clusterID, schoolid, yearGroup, dayOfWeek, periodNo, sessionID)
SELECT
aps.academicYear,
aps.clusterID,
aps.SchoolID,
yglu.LookupDetailsID,
aps.dayOfWeek,
aps.PeriodID,
atts.AttendanceSession
FROM AttendancePeriods aps
INNER JOIN LookupDetails yglu
ON yglu.LookupID = 3004
AND LookupDetailsID <> '0000'
LEFT OUTER JOIN AttendanceSessions atts
ON aps.schoolid = atts.SchoolID
AND atts.AcademicYear = aps.academicYear
AND atts.YearGroup = aps.yearGroup
AND atts.DayOfWeek = aps.dayOfWeek
AND atts.AttendancePeriodNo = aps.PeriodID
WHERE
aps.YearGroup = ''
AND aps.schoolid <> ''
AND aps.AcademicYear = @SchoolYear
AND yglu.LookupDetailsID NOT IN (SELECT YearGroup FROM @periodStruc)
-- cluster level
INSERT INTO @periodStruc (academicYear, clusterID, schoolid, yearGroup, dayOfWeek, periodNo, sessionID)
SELECT
aps.academicYear,
aps.clusterID,
aps.SchoolID,
yglu.LookupDetailsID,
aps.dayOfWeek,
aps.PeriodID,
atts.AttendanceSession
FROM AttendancePeriods aps
INNER JOIN LookupDetails yglu
ON yglu.LookupID = 3004
AND LookupDetailsID <> '0000'
LEFT OUTER JOIN AttendanceSessions atts
ON aps.schoolid = atts.SchoolID
AND atts.AcademicYear = aps.academicYear
AND atts.YearGroup = aps.yearGroup
AND atts.DayOfWeek = aps.dayOfWeek
AND atts.AttendancePeriodNo = aps.PeriodID
WHERE
aps.YearGroup = ''
AND aps.schoolid = ''
AND aps.ClusterID <> ''
AND aps.AcademicYear = @SchoolYear
AND yglu.LookupDetailsID NOT IN (SELECT YearGroup FROM @periodStruc)
SELECT *
FROM @periodStruc ps
ORDER BY
yearGroup,
dayofweek,
periodNo,
sessionid
WeeklyPct
PupilID,System.Int32
30
WeeklyPct
MainConnection
_x0040_SchoolYear,SchoolYearInt,8,0
_x0040_SchoolTerm,SchoolTerm,12,20
--DECLARE @SchoolYear INT = 2015, @SchoolTerm NVARCHAR(200) = 'Summer Term'
-- Find All school holiday/off days to be ignored in the attendance calculation
DECLARE @SchoolYearStart DATETIME
DECLARE @SchoolYearEnd DATETIME
DECLARE @TermDates TABLE (Dates DATETIME, IsSchoolHol NVARCHAR(6))
SELECT
@SchoolYearStart = MIN(StartDate),
@SchoolYearEnd = MAX(EndDate)
FROM SchoolTerms ST
INNER JOIN LookupDetails LD
ON LookupID = 2200
AND LD.LookupDetailsID = ST.Name
WHERE
SchoolYear = @SchoolYear
AND LD.Description = @SchoolTerm
GROUP BY SchoolYear
;WITH dates AS
(
SELECT @SchoolYearStart AS [Date]
UNION ALL
SELECT DATEADD(d,1,[Date]) FROM dates WHERE [Date] <@SchoolYearEnd
)
INSERT INTO @TermDates
SELECT
Dts.Date,
CASE WHEN ISNULL(CAST(Hol.StartDate AS NVARCHAR),'FALSE') = 'FALSE' THEN 'FALSE' ELSE 'TRUE' END
FROM dates Dts --where DATENAME(dt) NOT IN ('Saturday','Sunday')
LEFT OUTER JOIN
(
-- Term Breaks
SELECT
StartDate,
DATEADD(d, 1, DATEADD(s, -1, EndDate)) AS EndDate
FROM TermBreaks
WHERE StartDate BETWEEN @SchoolYearStart AND @SchoolYearEnd
UNION ALL
-- Bank Holidays
SELECT
HolidayDate,
DATEADD(d, 1, DATEADD(s, -1, HolidayDate))
FROM BankHolidays
WHERE HolidayDate BETWEEN @SchoolYearStart AND @SchoolYearEnd
UNION ALL
-- Gaps between Terms
SELECT
DATEADD(d, 1, ST.EndDate),
DATEADD(s, -1, (SELECT MIN(StartDate) NextTermStartDate FROM SchoolTerms ST2 WHERE ST2.StartDate > ST.EndDate)) EndDate
FROM SchoolTerms ST
UNION ALL
-- School Events
SELECT
EventDate,
DATEADD(d, 1, DATEADD(s, -1, EventDate))
FROM SchoolEvents
WHERE
IsSchoolClosed = 1
AND EventDate Between @SchoolYearStart AND @SchoolYearEnd
)
Hol
ON Dts.[Date] BETWEEN Hol.StartDate AND Hol.EndDate
OPTION (MAXRECURSION 0)
-- Select all attendance data from PupilSessionAttendance and PupilClassAttendance and the data in temp table #CombinedAttClSe
CREATE TABLE #CombinedAttClSe
(
SchoolID NVARCHAR(40),
PupilID NVARCHAR(40),
AttendanceDate DATETIME,
WeekNo INT,
AttendanceSession NVARCHAR(8),
AbsenceTypeID NVARCHAR(40),
AbsenceType NVARCHAR(200),
IsAuthorised BIT,
IsInAttendance BIT,
PeriodNumber TINYINT,
SchoolYear INT,
YearGroupID NVARCHAR(40),
SubjectID NVARCHAR(40),
DivisionID NVARCHAR(40),
BatchID NVARCHAR(40)
)
INSERT INTO #CombinedAttClSe
(
SchoolID,
PupilID,
SchoolYear,
AttendanceDate,
WeekNo,
AbsenceType,
AbsenceTypeID,
IsInAttendance,
IsAuthorised,
AttendanceSession
)
SELECT
PSA.SchoolID,
PSA.PupilID,
ST.SchoolYear,
PSA.AttendanceDate,
DATEPART(WK,PSA.AttendanceDate),
ABT.AbsenceType,
PSA.AbsenceTypeID,
ABT.IsInAttendance,
ABT.IsAuthorised,
PSA.AttendanceSession
FROM PupilSessionAttendance PSA
LEFT JOIN AbsenceTypes ABT
ON PSA.AbsenceTypeID = ABT.AbsenceTypeID
INNER JOIN CurrentPupil CP
ON CP.PupilID = PSA.PupilID
INNER JOIN SchoolTerms ST
ON PSA.SchoolID = ST.SchoolID
AND PSA.AttendanceDate BETWEEN ST.StartDate AND ST.EndDate
INNER JOIN LookupDetails LD
ON ST.NAME = LD.LookupDetailsID
AND LD.LookupID = 2200
LEFT JOIN @TermDates TD
ON PSA.AttendanceDate = TD.Dates
WHERE
LD.Description = @SchoolTerm
AND ST.SchoolYear = @SchoolYear
AND IsSchoolHol = 'FALSE'
AND ('%wc%' = '%wc%')
INSERT INTO #CombinedAttClSe
(
SchoolID,
PupilID,
SchoolYear,
AttendanceDate,
WeekNo,
AbsenceType,
AbsenceTypeID,
IsInAttendance,
IsAuthorised,
PeriodNumber
)
SELECT
PCA.SchoolID,
PCA.PupilID,
ST.SchoolYear,
PCA.AttendanceDate,
DATEPART(WK,PCA.AttendanceDate),
ABT.AbsenceType,
PCA.AbsenceTypeID,
ABT.IsInAttendance,
ABT.IsAuthorised,
PCA.PeriodNumber
FROM PupilClassAttendance PCA
LEFT JOIN AbsenceTypes ABT
ON PCA.AbsenceTypeID = ABT.AbsenceTypeID
INNER JOIN CurrentPupil CP
ON CP.PupilID = PCA.PupilID
INNER JOIN SchoolTerms ST
ON PCA.SchoolID = ST.SchoolID
AND PCA.AttendanceDate BETWEEN ST.StartDate AND ST.EndDate
INNER JOIN LookupDetails LD
ON ST.NAME = LD.LookupDetailsID
AND LD.LookupID = 2200
LEFT JOIN @TermDates TD
ON PCA.AttendanceDate = TD.Dates
WHERE
LD.Description = @SchoolTerm
AND ST.SchoolYear = @SchoolYear
AND IsSchoolHol = 'FALSE'
AND ('%wc%' = '%wc%')
DECLARE @WkCols AS NVARCHAR(MAX),
@WkQuery AS NVARCHAR(MAX)
select @WkCols = STUFF((SELECT ',' + QUOTENAME(WeekNo)
FROM #CombinedAttClSe
GROUP BY WeekNo
ORDER BY WeekNo
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @WkQuery = 'SELECT PupilID, SchoolYear,' + @WkCols + ' FROM
(
SELECT
CA.SchoolYear,
CA.PupilID,
CA.WeekNo,
AttPct = SUM(CASE WHEN CA.IsInAttendance = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(1)
FROM
#CombinedAttClSe AS CA
GROUP BY
CA.SchoolYear,
CA.PupilID,
CA.WeekNo
) x
PIVOT
(
MAX(AttPct)
FOR WeekNo IN (' + @WkCols + ')
) p '
execute(@WkQuery);
,SchoolName,SchoolName,System.String,Saint_x0020_Nicholas_x0020_School,False,False
,ReportSubTitle,ReportSubTitle,System.String,,False,False
,ReportTitle,ReportTitle,System.String,Weekly_x0020_Attendance_x0020_Summary,False,False
,ReportDateTime,ReportDateTime,System.DateTime,,False,False
,EngageCopyright,EngageCopyright,System.String,_x00A9__x0020_2018_x0020_Double_x0020_First_x0020_Ltd.,False,False
,UserID,UserID,System.String,engagesupport,False,False
Formula Variables
,TotalPupils,TotalPupils,System.Decimal,,False,False
,PupilsUnder85,PupilsUnder85,System.Decimal,,False,False
,PupilsUnder92,PupilsUnder92,System.Decimal,,False,False
Formula_x0020_Variables,AutumnTotal,AutumnTotal,System.Decimal,,False,False
Formula_x0020_Variables,SpringTotal,SpringTotal,System.Decimal,,False,False
Formula_x0020_Variables,SummerTotal,SummerTotal,System.Decimal,,False,False
Formula_x0020_Variables,YTDTotal,YTDTotal,System.Decimal,,False,False
Formula_x0020_Variables,PossibleTotal,PossibleTotal,System.Decimal,,False,False
Formula_x0020_Variables,SummerPupilCount,SummerPupilCount,System.Decimal,,False,False
Formula_x0020_Variables,SpringPupilCount,SpringPupilCount,System.Decimal,,False,False
Formula_x0020_Variables,AutumnPupilCount,AutumnPupilCount,System.Decimal,,False,False
UserDefined
UserDefined,TermDropDown,TermDropDown,System.String,,False,False
UserDefined,SchoolTerm,SchoolTerm,System.String,,False,False
UserDefined,SchoolYearString,SchoolYearString,System.String,,False,False
UserDefined,SchoolYearInt,SchoolYearInt,System.Int32,,False,False
UserDefined,NoOfChildren,NoOfChildren,System.Int32,,False,False
EngineV2
if(!MainData.IsConnected)
MainData.Connect();
None;Black;2;Solid;False;4;Black
Transparent
Transparent
0,0.4,27.7,1.76
Transparent
0.2,0,13.65,0.58
Trebuchet MS,12
646b7d73644a4c36a80992c0e5960953
0,0,0,0
ReportTitleText
Weekly Attendance Summary
Black
Center
Transparent
13.65,0,13.65,0.58
Trebuchet MS,12
e15a71690f5b465fa7f144b5e460815a
Right
0,0,0,0
SchoolNameText
Saint Nicholas School
Black
Center
Transparent
13.65,0.78,13.65,0.58
Trebuchet MS,12
67dee33e0480474481cef151cd392bf0
Right
0,0,0,0
ReportDateTimeText
{ReportDateTime}
Black
dd/MM/yyyy HH:mm
Center
0.2,1.56,27.3,0.0254
Black
Black
4e24cdc2d9024d21b53d7bbe7484b454
MainHeaderLine
None
Black
94256dbd4c9347ebb2793a2a05cc391f
MainHeader
Transparent
True
0,2.96,27.7,1.2
True
Transparent
10.4,0,7.2,0.6
Calibri,11,Bold
0,0,0,0
Text1
{SchoolName} Weekly Attendance Summary
Black
Expression
Center
Transparent
0,0.15,4.8,0.6
Calibri,11,Underline
0,0,0,0
Text7
{SchoolYearString} {SchoolTerm}
Black
Expression
PageHeaderBand1
Transparent
0,17.04,27.7,1.96
Transparent
0.2,0.4,27.3,0.58
Trebuchet MS,12
adfe15f6c7e54a99a61b8044eb47cd37
0,0,0,0
ReportPageNumberText
{PageNumber}
Black
Bottom
Transparent
0.2,1.18,27.3,0.58
Trebuchet MS,12
27b34795f57549c4a72232a11ffd8411
Right
0,0,0,0
ReportCopyrightText
© 2018 Double First Ltd.
Black
Bottom
0.2,0.2,27.3,0.0254
Black
Black
ed2ae05c00144cf7b4ea18d23fcb2e72
MainFooterLine
Black
fd12cf4aa5e347ce860af897e9909748
MainFooter
CrossGroupHeaderBand3.Width = 0;
Transparent
0,4.96,27.7,1.2
Transparent
0,0,6.2,1.2
All;Black;1;Solid;False;4;Black
Transparent
0,0,2.2,1.2
Calibri,10,Bold
True
1538911ef5554387b718911a4d3283cb
Center
1,0,0,0
Text37
First Name
Black
Expression
Bottom
All;Black;1;Solid;False;4;Black
Transparent
2.2,0,3,1.2
Calibri,10,Bold
True
d5cfd436a05b4a43b97251dfc496434e
Center
1,0,0,0
Text41
Surname
Black
Expression
Bottom
All;Black;1;Solid;False;4;Black
Transparent
5.2,0,1,1.2
Calibri,10,Bold
True
e8f46aab6e4748cba276f2c00dd4523b
Center
1,0,0,0
Text42
Form
Black
Expression
Bottom
CrossHeaderBand2
Transparent
6.2,0,1,1.2
{Weeks.WkNo}
CrossGroupHeaderBand3
Transparent
7.2,0,1,1.2
Top, Left, Right;Black;1;Solid;False;4;Black
Transparent
0,0,1,1.2
Calibri,9,Bold
True
40943797f44943909fc59a5ea29eb1c9
Center
1,0,0,0
Text9
True
{Weeks.sowDR}
-
{Weeks.eowDR}
Black
Expression
Bottom
Weeks
CrossDataBand2
Transparent
8.2,0,2.4,1.2
All;Black;1;Solid;False;4;Black
Transparent
0,0,1,1.2
Calibri,10,Bold
True
afb754b16bcc4f92bea8448d8e4baebf
Center
1,0,0,0
Text12
Total
Black
Expression
Bottom
All;Black;1;Solid;False;4;Black
Transparent
1,0,1.4,1.2
Calibri,10,Bold
True
01adfeaaab5646c7bb824145c7afd8f5
Center
1,0,0,0
Text2
Poss. Att.
to
EoY %
Black
Expression
Center
CrossFooterBand2
{MainData.SchoolID}
GroupHeaderBand2
True
NoOfChildren++;
CrossGroupHeaderBand1.Width = 0;
Transparent
0,6.96,27.7,0
{MainData.Surname}{MainData.ForeName}{MainData.PupilID}
GroupHeaderBand1
Transparent
True
0,7.76,27.7,0
Left, Right, Bottom;Black;1;Solid;False;4;Black
Transparent
0,0,3.6,0
Calibri,11,Bold
0,0,0,0
Text4
{sTermDropDown.Parameters["@YearDropDown"].ParameterValue}
Black
DataColumn
MainData
8e72468b142b48a3b29239bb228edb01
DataMainData
CrossGroupHeaderBand1.Width = 0;
Transparent
0,8.56,27.7,0.6
Transparent
0,0,6.2,0.6
All;Black;1;Solid;False;4;Black
Transparent
True
0,0,2.2,0.6
Calibri,10
True
Width
1,0,0,0
Text20
True
{MainData.ForeName}
Black
HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20,
Expression
Center
All;Black;1;Solid;False;4;Black
Transparent
2.2,0,3,0.6
Calibri,10
True
03a01c3caacf4695923afc89fb19fa5c
Width
1,0,0,0
Text43
True
7
{MainData.Surname}
Black
HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20,
Expression
Center
All;Black;1;Solid;False;4;Black
Transparent
True
5.2,0,1,0.6
Calibri,10
True
5159b9fedec04b73beb64dfc029e4efd
Center
1,0,0,0
Text11
True
{MainData.Description}
Black
Expression
Center
CrossHeaderBand1
Transparent
6.2,0,1,0.6
{Weeks.WkNo}
CrossGroupHeaderBand1
All;Black;1;Solid;False;4;Black
Transparent
7.2,0,1,0.6
Weeks
CrossDataBand1
Transparent
8.2,0,2.4,0.6
if(totalPossAttend() < 85.0m)
PupilsUnder85 ++;
if(totalPossAttend() > 85.0m && totalPossAttend() < 92.0m)
PupilsUnder92 ++;
TotalPupils ++;
PossibleTotal = totalPossAttend()+PossibleTotal;
All;Black;1;Solid;False;4;Black
Transparent
True
1,0,1.4,0.6
Calibri,9
True
6ece99ee1ee542b5871bb31e94625427
Center
0,0,0,0
Text24
{(totalPossAttend()).ToString("#0.##") + " %"}
Black
HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20,
Expression
Center
All;Black;1;Solid;False;4;Black
Transparent
True
0,0,1,0.6
Calibri,11
True
5a16e766c5304d2daae4827c2f1b80d7
Center
1,0,0,0
Text5
{Sum(GroupHeaderBand1,MainData.PresentPercent)}
Black
Totals
Center
CrossFooterBand1
GroupFooterBand1
CrossGroupHeaderBand2.Width = 0;
Transparent
0,9.96,27.7,3
Transparent
0,0,6.2,3
All;Black;1;Solid;False;4;Black
Transparent
0,0,6.2,0.6
Calibri,11,Bold
4f643e9d78f94816a545c2eba417cbb9
1,0,0,0
Text6
True
Number of Children
Black
HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20,
Expression
Bottom
All;Black;1;Solid;False;4;Black
Transparent
0,1.2,6.2,0.6
Calibri,11,Bold
35ada845974c4578b10899bc83fbe401
1,0,0,0
Text8
True
Average
Black
HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20,
Expression
Bottom
All;Black;1;Solid;False;4;Black
Transparent
0,1.8,6.2,0.6
Calibri,11,Bold
622f5c771f954df2915956f0f1abb92e
1,0,0,0
Text10
True
Sessions Available
Black
HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20,
Expression
Bottom
All;Black;1;Solid;False;4;Black
Transparent
0,2.4,6.2,0.6
Calibri,11,Bold
a613a5f0239a4a8cbe934567a001d4b6
1,0,0,0
Text13
True
Sessions Filled
Black
HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20,
Expression
Bottom
All;Black;1;Solid;False;4;Black
Transparent
0,0.6,6.2,0.6
Calibri,11,Bold
aeae0a3bd0fc4227b1f8b0330b469d53
1,0,0,0
Text19
True
School Attendance %
Black
HotkeyPrefix=None, LineLimit=False, RightToLeft=False, Trimming=None, WordWrap=True, Angle=0, FirstTabOffset=40, DistanceBetweenTabs=20,
Expression
Bottom
CrossHeaderBand3
Transparent
6.2,0,0.8,3
{Dates.dates}
8c4da0efd2c34b6598222531b614e4bc
CrossGroupHeaderBand2
Transparent
7,0,1,3
All;Black;1;Solid;False;4;Black
Transparent
0,0,1,0.6
Calibri,11,Bold
cb9ddeae1dec4c479342460ad8731232
Center
1,0,0,0
Text14
Black
Expression
Center
All;Black;1;Solid;False;4;Black
Transparent
0,0.6,1,0.6
Calibri,11
4ffe1a9a21084beeb2d46c0580330ff2
Center
1,0,0,0
Text15
{Avg(GroupHeaderBand2,MainData.SessionsLeftThiYear)}
Black
Expression
Center
All;Black;1;Solid;False;4;Black
Transparent
0,1.2,1,0.6
Calibri,11
6279a14c94c5401081b65a600a761c8f
Center
1,0,0,0
Text16
%
Black
Expression
Center
All;Black;1;Solid;False;4;Black
Transparent
0,1.8,1,0.6
Calibri,11
1d739dd11a6a4a9bb2dd3c08e7d594ba
Center
1,0,0,0
Text17
%
Black
Expression
Center
All;Black;1;Solid;False;4;Black
Transparent
0,2.4,1,0.6
Calibri,11
4fd95b0ec47b4253bde5cceab4242e23
Center
1,0,0,0
Text18
%
Black
Expression
Center
Weeks
CrossDataBand3
Transparent
8,0,1.2,3
CrossFooterBand3
GroupFooterBand2
f8decc2f15314a11acd3ba0893ba21c2
1,1,1,1
Main_Page
Landscape
21
29.7
Arial,100
[50:0:0:0]
Control
Window
296,56,176,20
False
Microsoft Sans Serif,8
Black
296, 56
12/31/9998 12:00:00 AM
1/1/1753 12:00:00 AM
dtpStart
176, 20
7/13/2015 4:05:45 PM
getYearTermString();
if(!Weeks.IsConnected)
Weeks.Connect();
if(!PeriodClassSessionStruct.IsConnected)
PeriodClassSessionStruct.Connect();
8,72,176,24
OK
Microsoft Sans Serif,8
Black
8, 72
ButtonControl1
176, 24
OK
Control
16,16,144,16
Microsoft Sans Serif,8,Bold
Black
72cd3d00dea24d01b54622bc07b6b3d6
16, 16
LabelControl2
144, 16
School Year + Term
Window
16,40,144,20
Microsoft Sans Serif,8
Black
ba5d3174824549dc9b8e48ad8280f8f9
sTermDropDown.YearTerm
sTermDropDown.YearTerm
16, 40
10
20
lub_SchoolYear
144, 20
LookUpBox
Microsoft Sans Serif,8
0111bab1c6c74bdcb642d522c6e3d227
0, 0
Form1
224, 128
System.Dll
System.Drawing.Dll
System.Windows.Forms.Dll
System.Data.Dll
System.Xml.Dll
Stimulsoft.Controls.Dll
Stimulsoft.Base.Dll
Stimulsoft.Report.Dll
Weekly Attendance Summary
Double First Ltd
3/29/2018 12:17:20 PM
8/22/2007 9:17:27 AM
CurrentPupils|Attendance|CurrentPupil.PupilID
C:\Customer Data\Acorn Care and Education\Reports\Weekly Pupil Att\sstA655.mrt
469582b94ca14febb6271477ff4df2d0
Weekly Attendance Summary
Centimeters
2013.3.1800
CSharp