SQL‎ > ‎

TSQL - Campus - EMIS - FT - Student Truancy and Excessive Absence

posted Feb 22, 2018, 7:48 AM by Chris Franklin

TSQL Code

DECLARE @districtID AS INT
SET @districtID=999 -- District ID

/*
 000001 - Junior High School
 000002 - Other School
 000003 - Elementary 0
 000004 - Middle School
 000005 - Elementary 1
 000006 - Elementary 2
 000007 - Elementary 3
 000008 - High School
*/
DECLARE @schoolIDs TABLE (id INT);
INSERT @schoolIDs(id) VALUES (024570),(024570),(024521),(003806),(003848),(043612),(014242),(034710),(000005),(011908)

/*
  Create a TMP table with a virtual rowid column 
*/
;WITH logs AS (
 SELECT 
  [personID],
  [districtID],
  [dateTimeStamp], 
  [Text],
  [module],
  [contactMode],
  rowid=ROW_NUMBER() OVER (
   PARTITION BY 
    personID, 
districtID,
module
   ORDER BY 
    [personID] DESC,
[districtID] DESC,
[dateTimeStamp] DESC
  )
 FROM
  [districtname].[dbo].[ContactLog] conlog
 WHERE 
  conlog.module = 'PLP'
  AND
  conlog.contactMode IN ('Attendance Letter','AIP Implementation Meeting')
)

SELECT --DISTINCT
 right(' ',8), -- filler
 right(' '+'FT',2), -- 2 
 right(' ',1), -- filler
 right(' '+scyr.endYear,4), -- 4d year
 right(' ',1), --Reporting Period
 right(' '+dis.number,6), -- district IRN
 right(' '+per.studentNumber,9), -- student id
 right(' '+CONVERT(CHAR(8), log.dateTimeStamp, 112),8), -- event date CCYYMMDD
 log.text
 --.per.personID
 --.right(' '+log.text,1)-- Absence Event
FROM
 [districtname].[dbo].[District] dis 
 INNER JOIN [districtname].[dbo].[School] sch ON
  sch.number IN (SELECT id FROM @schoolIDs)
  AND
  sch.districtID = dis.districtID
  AND
  sch.districtID = @districtID
 INNER JOIN [districtname].[dbo].[Calendar] cal ON 
  cal.districtID=dis.districtID
  AND
  cal.schoolID = sch.schoolID
 INNER JOIN [districtname].[dbo].[SchoolYear] scyr ON 
  scyr.endYear = cal.endYear
  AND 
  scyr.active = 1
 INNER JOIN [districtname].[dbo].[Enrollment] enr ON 
  enr.serviceType != 'S'
  AND
  enr.calendarID = cal.calendarID
  AND
  enr.active = 1 
  AND 
  (
   enr.noShow IS NULL 
   OR 
   enr.noShow = 0
  )
  INNER JOIN [districtname].[dbo].[Person] per ON
  per.personID = enr.personID
 INNER JOIN [districtname].[dbo].[Identity] id ON
  id.identityID=per.currentIdentityID
 INNER JOIN logs log ON
  log.personID = per.personID
  AND
  log.rowid = 1
 
ORDER BY
 per.studentNumber,
 log.dateTimeStamp


Comments