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