DECLARE @_Ngay DATE = '2024-01-01';
-- Ngày truyền vào (lưu ý định dạng ngày YYYY-MM-DD để tránh lỗi)

-- Truy vấn lấy thông tin bài kiểm tra của ngày gần nhất
WITH LatestExamScores AS (
    -- CTE để lấy danh sách bài kiểm tra của ngày gần nhất cho mỗi trường
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY th.TenTruong ORDER BY kq.Ngay DESC) AS Stt,
        sv.MaSv AS [Mã sinh viên],
        sv.HoTen AS [Tên sinh viên],
        th.TenTruong AS [Tên trường],
        CONVERT(VARCHAR(10), kq.Ngay, 120) AS [Ngày kiểm tra],
        ISNULL(kq.Diem, 0) AS [Điểm kiểm tra]
    FROM
        DmSinhVien sv
        JOIN KetQuaBt kq ON sv.MaSv = kq.MaSinhVien
        JOIN DmTruongHoc th ON sv.MaTruongHoc = th.MaTruong
    WHERE
        kq.Ngay = (SELECT MAX(Ngay) FROM KetQuaBt WHERE Ngay <= @_Ngay)
)
-- Truy vấn
SELECT 
    Stt,
    [Mã sinh viên],
    [Tên sinh viên],
    [Tên trường],
    [Ngày kiểm tra],
    [Điểm kiểm tra]
FROM
    LatestExamScores

-- Union với dòng thứ 2 tính trung bình điểm theo từng trường
UNION ALL

SELECT
    0 AS Stt,  -- Số thứ tự 0 cho dòng tính trung bình, hoặc tăng lên hơn nữa để hiển thị ở phía sau
    '' AS [Mã sinh viên],
    '' AS [Tên sinh viên],
    [Tên trường],
    '' AS [Ngày kiểm tra],
    AVG([Điểm kiểm tra]) AS [Điểm kiểm tra]  -- Trung bình điểm của từng trường
FROM
    LatestExamScores
GROUP BY
    [Tên trường]

ORDER BY
    [Tên trường],  -- Sắp xếp theo Tên trường
    Stt;  -- Sắp xếp theo Stt để đảm bảo thứ tự các bản ghi của mỗi trường