Subscribe to posts

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


TSQL - Campus - Student Schedule with Room names by period

posted Sep 21, 2017, 4:07 AM by Chris Franklin   [ updated Sep 21, 2017, 4:08 AM ]

student_numberfirst_namelast_name1234567891011
1234TomAnderson221223101CAFPOLPOLPOLPOLPOLPOLPOL
33434LisaFerguson226233251207176CAFCAF231231232105
1234123HomerHudimacOMBOMBOMBOMBOMBOMBOMBOMBOMBOMBOMB
1234BartMichael231243252107CAF106106CAFWORLDWORLDWORLD
4312BenderBlaise106202WORLDWORLDWORLDWORLDWORLDWORLDWORLDWORLDWORLD
2341FranklinStrongarm221GYM101225CAF252252223223251WORLD
42341BobGoogle273AIDE251CAF176225225106106WORLDWORLD


SQL

-- All Students + Room name per PERIOD 
DECLARE @districtID AS INT
SET @districtID=1 -- District ID

DECLARE @schoolIDs TABLE (id INT);
INSERT @schoolIDs(id) VALUES (111111); 

SELECT 
 student_number,
 MAX(student_firstname) AS first_name,
 MAX(student_lastname) AS last_name,
 MAX(CASE WHEN period_name = '1' THEN room_name ELSE NULL END) AS [1],
 MAX(CASE WHEN period_name = '2' THEN room_name ELSE NULL END) AS [2],
 MAX(CASE WHEN period_name = '3' THEN room_name ELSE NULL END) AS [3],
 MAX(CASE WHEN period_name = '4' THEN room_name ELSE NULL END) AS [4],
 MAX(CASE WHEN period_name = '5' THEN room_name ELSE NULL END) AS [5],
 MAX(CASE WHEN period_name = '6' THEN room_name ELSE NULL END) AS [6],
 MAX(CASE WHEN period_name = '7' THEN room_name ELSE NULL END) AS [7],
 MAX(CASE WHEN period_name = '8' THEN room_name ELSE NULL END) AS [8],
 MAX(CASE WHEN period_name = '9' THEN room_name ELSE NULL END) AS [9],
 MAX(CASE WHEN period_name = '10' THEN room_name ELSE NULL END) AS [10],
 MAX(CASE WHEN period_name = '11' THEN room_name ELSE NULL END) AS [11]
FROM
 (
  SELECT DISTINCT
   dis.number district_number, 
   sch.number school_number,
   sch.name as school_name,
 --  CONVERT(date,te.startDate) term_start,
 --  CONVERT(date,te.endDate) term_end,
 --  te.name term_name,
   cou.courseID course_id,
   cou.number course_number,
   cou.name course_name,
   cou.stateCode course_statecode,
   sec.sectionID section_id,
   sec.number section_number,
   sec.teacherPersonID section_teacher_id,
   sec.teacherDisplay section_teacher,
   sec.roomID section_room,
   sec.sectionID sectionID,
   --tri.trialID trialID,
   --te.termID termID,
   pe.name period_name,
   pe.seq period_number,
   id.firstName student_firstname,
   id.lastName student_lastname,
   per.studentNumber student_number,
   per.personID persion_id,
   roo.name room_name
   /*
   per.studentNumber,
   id.firstName,
   id.lastName,
   pe.name,
   roo.name
   */
  FROM 
   [berea].[dbo].[District] dis 
   INNER JOIN
   [berea].[dbo].[Calendar] cal ON 
    dis.districtID=cal.districtID
    AND cal.endYear = 
     CASE 
   WHEN MONTH(GETDATE()) >= 7 THEN YEAR(GETDATE())+1
   ELSE YEAR(GETDATE())
 END
   INNER JOIN
   [berea].[dbo].[Course] cou ON
    cou.calendarID=cal.calendarID
    AND
    cou.active = 1
   INNER JOIN
   [berea].[dbo].[School] sch ON
    sch.schoolID = cal.schoolID
    AND 
    sch.number IN (SELECT id FROM @schoolIDs)
   INNER JOIN 
    [berea].[dbo].[ScheduleStructure] scst ON
    scst.calendarID=cal.calendarID
   INNER JOIN 
    [berea].[dbo].[Trial] tri ON
     tri.calendarID = cal.calendarID
     AND
     tri.structureID=scst.structureID
     AND 
     tri.active = 1
   INNER JOIN
   [berea].[dbo].[TermSchedule] tesc ON
    tesc.structureID=tri.structureID
   INNER JOIN
   [berea].[dbo].[Term] te ON
    te.termScheduleID=tesc.termScheduleID
    AND
te.endDate >= CAST(YEAR(GETDATE()) AS varchar(4)) + '-12-31'

   INNER JOIN
   [berea].[dbo].[Section] sec ON 
    sec.trialID=tri.trialID
    AND
    sec.courseID=cou.courseID
   INNER JOIN 
[berea].[dbo].[Roster] ro ON
ro.sectionID = sec.sectionID
AND
ro.trialID = tri.trialID
AND
ro.endDate IS NULL
   INNER JOIN
    [berea].[dbo].[SectionPlacement] sepl ON
     sepl.sectionID=sec.sectionID 
     AND
     sepl.trialID=tri.trialID 
     AND 
     sepl.termID=te.termID
    INNER JOIN
[berea].[dbo].[PeriodSchedule] pesc ON
      pesc.structureID=tri.structureID
    INNER JOIN
[berea].[dbo].[Period] pe ON
 pe.periodID=sepl.periodID
 AND
 pe.periodScheduleID=pesc.periodScheduleID
    INNER JOIN
[berea].[dbo].[Room] roo ON
 roo.schoolID = sch.schoolID
 AND
 roo.roomID = sec.roomID
INNER JOIN 
[berea].[dbo].[Identity] id ON
 id.personID=ro.personID
INNER JOIN 
[berea].[dbo].[Person] per ON
 per.personID = ro.personID
  WHERE 
   dis.districtID = @districtID 
) AS thesource 
GROUP BY 
 student_number
ORDER BY
 student_number

TSQL - Campus - Return (Local studentNumber, Students State ID, Students Last name, Students First name, Students Middle name, Students Birthday as dd/mm/yyyy, School name that they Graduated from, Date of Diploma Diploma Type)

posted Sep 21, 2017, 3:44 AM by Chris Franklin

  • Local studentNumber
  • Students State ID
  • Students Last name
  • Students First name
  • Students Middle name
  • Students Birthday as dd/mm/yyyy
  • School name that they Graduated from
  • Date of Diploma
  • Diploma Type

SQL

SELECT 
 Person.studentNumber      AS StudentID,
 Person.stateID            AS StateID,
 [Identity].lastName       AS LastName,
 [Identity].firstName      AS FirstName,
 [Identity].middleName     AS MiddleName,
 CONVERT(varchar(10),[Identity].birthdate,101) AS BirthDate,
 MAX(School.name)          AS SchoolName,
 CONVERT(varchar(10),Graduation.diplomaDate,101) AS DiplomaDate,
 Graduation.diplomaType    AS DiplomaType,

 CASE
  WHEN preference.value='1' THEN
   CONVERT(DECIMAL(4, 1), SUM(TranscriptCourse.gpaWeight * COALESCE(TranscriptCourse.gpaValue, TranscriptCourse.unweightedGpaValue))/SUM(TranscriptCourse.gpaWeight))
  WHEN preference.value='2' THEN
   CONVERT(DECIMAL(5, 2), SUM(TranscriptCourse.gpaWeight * COALESCE(TranscriptCourse.gpaValue, TranscriptCourse.unweightedGpaValue))/SUM(TranscriptCourse.gpaWeight))
  WHEN preference.value='4' THEN
   CONVERT(DECIMAL(7, 4), SUM(TranscriptCourse.gpaWeight * COALESCE(TranscriptCourse.gpaValue, TranscriptCourse.unweightedGpaValue))/SUM(TranscriptCourse.gpaWeight))
  WHEN preference.value='5' THEN
   CONVERT(DECIMAL(8, 5), SUM(TranscriptCourse.gpaWeight * COALESCE(TranscriptCourse.gpaValue, TranscriptCourse.unweightedGpaValue))/SUM(TranscriptCourse.gpaWeight))
  ELSE
   CONVERT(DECIMAL(6, 3), SUM(TranscriptCourse.gpaWeight * COALESCE(TranscriptCourse.gpaValue, TranscriptCourse.unweightedGpaValue))/SUM(TranscriptCourse.gpaWeight))
 END cumGpa,

 creditsEarned         AS CreditsEarned,
 creditsAttempted      AS CreditsAttempted,
 MAX(Enrollment.grade) AS GradGrade
 
FROM
-- School
 District
 INNER JOIN Calendar ON Calendar.districtID = District.districtID
 INNER JOIN School ON School.schoolID = Calendar.schoolID
  AND School.type = 'PS'
 INNER JOIN Enrollment ON Enrollment.calendarID = Calendar.calendarID
  AND Enrollment.endStatus = '99' 
  AND Enrollment.serviceType = 'P'
  AND (
   Enrollment.noShow IS NULL 
   OR 
   Enrollment.noShow = 0
  )

-- Personal
 INNER JOIN Person ON Person.personID=Enrollment.personID 
  INNER JOIN Graduation ON Graduation.personID = Person.personID 
  AND YEAR(graduation.diplomaDate) >= 2011
 INNER JOIN [Identity] ON [Identity].identityID = Person.currentIdentityID

-- GPA
 INNER JOIN GradeLevel ON GradeLevel.calendarID = Enrollment.calendarID 
  AND GradeLevel.structureID = Enrollment.structureID 
  AND COALESCE(GradeLevel.excludeGPA,0) = 0
 INNER JOIN TranscriptCourse ON TranscriptCourse.personID = Person.personID 
  AND TranscriptCourse.grade = GradeLevel.name
  AND (
   TranscriptCourse.gpaValue IS NOT NULL 
   OR 
   TranscriptCourse.unweightedGpaValue IS NOT NULL
  )
 LEFT OUTER JOIN preference ON preference.element='GPADigits'

-- Credits
 INNER JOIN (
  SELECT 
   PersonID, 
   SUM(creditsEarned) as creditsEarned, 
   SUM(creditsAttempted) as creditsAttempted 
  FROM (
   SELECT 
TranscriptCredit.personID as personID,
SUM(creditsEarned) as creditsEarned, 
SUM(creditsAttempted) as creditsAttempted
   FROM 
    TranscriptCredit 
    INNER JOIN TranscriptCourse ON TranscriptCourse.transcriptID = TranscriptCredit.transcriptID 
AND TranscriptCourse.personID = TranscriptCredit.personID
    INNER JOIN CurriculumStandard ON TranscriptCredit.standardID = CurriculumStandard.standardID
    INNER JOIN CurriculumStandard CurriculumStandard2 ON CurriculumStandard.parentID = CurriculumStandard2.standardID
   GROUP BY 
TranscriptCredit.personID, 
CurriculumStandard2.standardID, 
CurriculumStandard.standardID, 
CurriculumStandard.name, 
CurriculumStandard2.name  
  ) AS Credits 
  GROUP BY 
   PersonID
 ) AS Credits ON Credits.personID = Person.personID

WHERE
 District.inactive = 0 

GROUP BY
 Person.personID,
 Person.studentNumber,
 Person.stateID,
 Enrollment.calendarID,
 [Identity].lastName,
 [Identity].firstName,
 [Identity].middleName,
 CONVERT(varchar(10),[Identity].birthdate,101),
 school.name,
 CONVERT(varchar(10),graduation.diplomaDate,101),
 graduation.diplomaType,
 preference.value,
 creditsEarned,
 creditsAttempted

ORDER BY 
 GradGrade,
 LastName, 
 FirstName, 
 MiddleName

1-3 of 3