SQL Reports Examples

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