SQL Scripts

Permissions Analysis Queries
Attached are SQL Queries to evaluate either Aeries Web Security or SQL Security for your legacy Aeries Client accounts.
Fri, Jun 16, 2017 at 8:39 AM
Parent/Student Portal Accounts Percentage and Recent Utilization
Parent/Student Portal Accounts Percentage and Recent Utilization --This is a nice global view of students having one or more PWA parent or student ac...
Thu, Jun 29, 2017 at 2:16 PM
Teacher Gradebook Usage
Teacher Gradebook Usage: Example SQL queries attached in text file
Wed, May 10, 2017 at 4:15 PM
Combining multiple rows into one row or variable
select cd from locĀ  This will return one row per record. DECLARE @something NVARCHAR(10) DECLARE @together NVARCHAR(max) = '' DECLAR...
Thu, Feb 9, 2017 at 9:23 AM
Finding overlapping ENR records using SQL
SELECT enr.sc, enr.pr,ENR.ID, ENR.ED, ENR.LD, enr.er, enr_1.sc,enr_1.pr, ENR_1.ED AS Expr1, ENR_1.LD AS Expr2, enr_1.er FROM ENR INNER JOIN ENR A...
Thu, Feb 9, 2017 at 9:45 AM
Determining a student's graduation year using SQL
This will return the appropriate graduation year for the student based on the year of the database and grade of the student. SELECT id,gr,'20'...
Wed, May 10, 2017 at 4:10 PM
Parsing a single row into multiple rows
Use this to break a single row of elements into multiple rows. DECLARE @stuff as nvarchar(1000) = '6,,7,99,3,22,4' -- String to be parsed. DE...
Thu, Feb 9, 2017 at 10:33 AM
List all tables and sort by size in kilobytes
CREATE TABLE #RowCountsAndSizes (TableName NVARCHAR(128),rows CHAR(11), reserved VARCHAR(18),data VARCHAR(18),index_size VARCHAR(18), ...
Thu, Feb 9, 2017 at 10:40 AM
Determining the academic year of an Aeries database
How to determine the academic year of an Aeries database in SQL. SELECT CAST(20-FLOOR(CAST(SUBSTRING(DB_NAME(),4,2) AS INT)/80) AS VARCHAR(2)) + CAST(SU...
Tue, Mar 21, 2017 at 11:07 AM
Attendance and Enrollment totals
------use variables DECLARE @THISDAY AS DATETIME = getdate() -- By default this selects today for the "as of" date DECLARE @StarDate AS DATETIM...
Fri, Jun 23, 2017 at 10:24 AM
Using the Get Description Function in SQL Server
Overview The Aeries SQL Server database definitions have for a long time included a scalar-valued function named dbo.Get_Description. Until recently, the Q...
Mon, Oct 23, 2017 at 8:56 AM
Dynamic SQL queries to provide a rich one line result of whatever unique Programs and/or Authorizations are current or historical
People sometimes indicated a desire to output all PGM and/or AUT statuses that are relevant on one line. While Analytics allows a comfortable way to drill i...
Mon, Mar 5, 2018 at 1:50 PM
Custom Tables and Views feature invites dynamic SQL to create views tailored to what you use
This tSQL will not only create a view called PGMS (Program and Authorization Status), but it also adds itself to the CTD (Custom Table Definitions) and CTF ...
Thu, Jun 28, 2018 at 3:42 PM