TSQL CodeDECLARE @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 |
SQL >