Introduction
The following article provides SQL report codes to allow you to use in different use case scenarios. An SQL report allows you to produce formatted reports with tables in the form of data, graph, images, and charts. These reports are hosted on a server that can be executed any time using parameters defined by the admin users in your Genius SIS instance. They can also be scheduled to be emailed to defined participants.
Use-Case Scenario SQL Codes
If you need a report that provides the number of seats available in all courses:
select Sec.Startdate , Sec.Enddate , sec.SectionIndex AS Sec_#,
sec.name , sec.cap AS Max,
(select count(x.enrollmentindex)
from Enrollments x
where x.SectionIndex = sec.SectionIndex
and x.Status = 'ACTIVE') as #Enrolled,
sec.cap - (select count(x.enrollmentindex) from Enrollments x
where x.SectionIndex = sec.SectionIndex
and x.Status = 'ACTIVE') as #SeatsAvailable,
(select count(distinct req.StudentIndex)
from RequestedCourses req
inner join sections x
on x.CourseIndex = req.CourseIndex
where req.Approved = 'WAITING_LIST'
and x.SectionIndex = sec.SectionIndex) as #WaitingList
from Sections sec
where Sec.Status = 'Active' AND sec.AffiliationIndex IN (@CurrentUserAfiliations)
ORDER BY Sec.StartDate ASC
If you need a report that provides the date that each new learner account has been created:
SELECT StudentIndex, Email, FirstName + ' ' + LastName, RowInsertDate as [AccountCreationDate] FROM STUDENTS WHERE STATUS = 'ACTIVE'
If you need a ledger report of all transactions made by affiliation:
SELECT
led.[Date] as [Date]
, Students.Lastname+', '+students.Firstname as [Student]
, led.TransactionIndex as [Genius Trans. ID]
, Transactions.AuthorizationNumber as [Gateway Receipt Number]
, Transactions.PaymentId as [Gateway Payment Id]
, led.Description as [Description]
, CASE
WHEN Sections.Name IS NOT NULL THEN Sections.Name
WHEN sec2.Name IS NOT NULL THEN sec2.Name
WHEN Programs.name IS NOT NULL THEN Programs.name
END as [Section/LP]
, CASE
WHEN Affiliations.Name IS NOT NULL THEN Affiliations.Name
WHEN aff2.Name IS NOT NULL THEN aff2.Name
END as [Affiliation]
, led.[Type] as [Type]
, led.PaymentMethod as [Method]
, Coupons.Code as [Coupon]
, CAST((CASE WHEN led.[Type]='CREDIT' THEN led.Amount ELSE -led.Amount END) as DECIMAL(21,6)) as [Amount]
FROM Ledger led
LEFT JOIN Students ON led.studentindex = Students.studentindex
LEFT JOIN Coupons ON led.CouponIndex = Coupons.CouponIndex
LEFT JOIN Sections ON led.SectionIndex = Sections.SectionIndex
LEFT JOIN Programs ON led.ProgramIndex = Programs.ProgramIndex
LEFT JOIN Transactions ON led.TransactionIndex = Transactions.TransactionIndex and led.[Type] = 'CREDIT'
LEFT JOIN Affiliations ON Affiliations.AffiliationIndex = Sections.AffiliationIndex
LEFT JOIN Ledger led2 ON led2.TransactionIndex = led.TransactionIndex and led2.LedgerIndex <> led.LedgerIndex
LEFT JOIN Sections sec2 on sec2.SectionIndex = led2.SectionIndex
LEFT JOIN Affiliations aff2 on aff2.AffiliationIndex = sec2.AffiliationIndex
WHERE ledger.[Date]>=@StartDate and ledger.[Date]<=@EndDate
If you need a report that matches each course with its corresponding sections:
select cou.courseindex, cou.name as CourseName, cou.status courseStatus,
sec.sectionindex, sec.Name as SectionName, sec.status as sectionStatus
from courses cou (nolock)
inner join sections sec (nolock)
on cou.courseindex = sec.courseindex