Complex SQL statement feasibility

Question

I am currently developing a database system for a school that will work with our booking system so students can book their courses and we can better track their activity. Right now I am faced with a complex issue, what I want to do is check which student contracts are valid, I can do so by using the following function, but I want to see if there is an easier way to properly do it (besides storing data I can compute in the system.)

If a contract is valid then the student has not used up all the hours they have purchased, hours purchased is the sum of the length of teach class they have attended added to the sum of the lengths of each class they missed but booked (after a certain number, such as 3 or 5). I can do it with the following query but I feel like there must be a simpler way:

SELECT
    level.name
FROM
    (
        SELECT
            contract.level_package_id,
            contract_class_hours.hours_purchased,
            (
                SELECT
                    isnull(sum(DATEPART(hh, class.end_date - class.start_date)), 0)
                FROM
                    booking
                    JOIN class ON class.id = booking.class_id
                WHERE
                    booking.booking_state_id = 3
                    AND booking.contract_id = contract.id
            ) AS time_attended,
            (
                SELECT 
                    isnull(sum(absent_class_lengths.length), 0)
                FROM
                    (
                        SELECT
                            DATEPART(hh, class.end_date - class.start_date) AS length,
                            row_number() OVER (ORDER BY class.start_date) AS rn
                        FROM
                            booking
                            JOIN class ON class.id = booking.class_id
                        WHERE
                            student_id = 5
                            AND booking_state_id = 4
                            AND booking.contract_id = contract.id
                    ) absent_class_lengths
                WHERE 
                    rn > contract_class_hours.absences_allowed
            ) as time_absent
        FROM
            contract
            JOIN contract_class_hours ON contract_class_hours.contract_id = contract.id
    ) test
    JOIN level_package_level ON level_package_level.level_package_id = test.level_package_id
    JOIN level ON level.id = level_package_level.level_id
WHERE
    test.time_absent + test.time_attended < test.hours_purchased
    AND level.study_type_id = 2

• booking.state_id = 3 means the student attended class
• booking.state_id = 4 means the student was absent
• level.study_type_id = 2 is just a course subject

The tables contain data columns like these (ignore level_id, it is just a value I need to return):

CLASS
    id - int
    end_date - datetime
    start_date - datetime

BOOKING
    id - int
    class_id - int
    student_id - int
    booking_state_id - smallint

BOOKING_STATE
    id - int
    state - varchar(20) [absent, attended]

CONTRACT
    id - int
    student_id - int
    level_id - int
    hours_purchased - smallint
    absenses_allowed - smallint

STUDENT
    id - int

I realize this may be to complicated a question to ask, but I am just wondering if this really is the proper way to do things, or if I should just save some sort of field in the contract table that has a number of hours attended and assume it is always accurate.

Answer 1

Seems like you could do a few things to improve the situation

1. Create a view with some of the code
2. Use the above response to create a stored procedure, and make the hard coded ids variables WHERE class.student_id = 5 AND booking_state_id = 4

I am guessing these are not always going to be the same and having a stored procedure will allow a plan to be cached as compared to an AD Hoc sql statement. This will allow you to send in various ids to get the results you want and return a data set that could be used in a web applciaiton of simply converted to and Exce or Word table for reporting.

Answer 2

This should yield the same functionality and be easier to read:

SELECT
   level.name
FROM contract
INNER JOIN contract_class_hours ON contract_class_hours.contract_id = contract.id
INNER JOIN level_package_level ON level_package_level.level_package_id = contract.level_package_id
INNER JOIN level ON level.id = level_package_level.level_id
Outer Apply(
    SELECT isnull(sum(DATEPART(hh, class.end_date - class.start_date)), 0) AS time_attended
    FROM booking
    INNER JOIN class ON class.id = booking.class_id
    WHERE booking.booking_state_id = 3
        AND booking.contract_id = contract.id
) T1
Outer Apply(
    SELECT snull(sum(absent_class_lengths.length), 0) AS time_absent
    FROM
    (
        SELECT DATEPART(hh, class.end_date - class.start_date) AS length,
            row_number() OVER (ORDER BY class.start_date) AS rn
        FROM booking
        INNER JOIN class ON class.id = booking.class_id
        WHERE class.student_id = 5
           AND booking_state_id = 4
           AND booking.contract_id = contract.id
    ) absent_class_lengths
    WHERE 
    rn > contract_class_hours.absences_allowed
) T2
WHERE T2.time_absent + T1.time_attended < contract_class_hours.hours_purchased
   AND level.study_type_id = 2