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

Post date: Feb 22, 2018 3:48:44 PM

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