DECLARE @Ngay DATE = '2024-06-12';

WITH LatestTests AS (
    SELECT 
        MaSinhVien, 
        MAX(Ngay) AS LatestNgay
    FROM 
        KetQuaBt
    WHERE 
        Ngay <= @Ngay
    GROUP BY 
        MaSinhVien
),
StudentScores AS (
    SELECT 
        sv.MaSv,
        sv.HoTen,
        th.TenTruong,
        COALESCE(bt.Ngay, @Ngay) AS NgayKiemTra,
        COALESCE(bt.Diem, 0) AS DiemKiemTra
    FROM 
        DmSinhVien sv
    JOIN 
        DmTruongHoc th ON sv.MaTruongHoc = th.MaTruong
    LEFT JOIN 
        LatestTests lt ON sv.MaSv = lt.MaSinhVien
    LEFT JOIN 
        KetQuaBt bt ON sv.MaSv = bt.MaSinhVien AND lt.LatestNgay = bt.Ngay
),
AggregatedScores AS (
    SELECT
        TenTruong,
        COUNT(*) AS SoSinhVien,
        SUM(DiemKiemTra) AS TongDiem,
        AVG(DiemKiemTra) AS DiemTrungBinh
    FROM 
        StudentScores
    GROUP BY 
        TenTruong
),
TotalScores AS (
    SELECT 
        'Tổng cộng' AS TenTruong,
        COUNT(*) AS SoSinhVien,
        SUM(DiemKiemTra) AS TongDiem,
        AVG(DiemKiemTra) AS DiemTrungBinh
    FROM 
        StudentScores
)

SELECT 
    ROW_NUMBER() OVER (PARTITION BY ss.TenTruong ORDER BY ss.TenTruong, ss.MaSv) AS Stt,
    ss.MaSv AS [Mã sinh viên],
    ss.HoTen AS [Tên sinh viên],
    ss.TenTruong AS [Tên trường],
    ss.NgayKiemTra AS [Ngày kiểm tra],
    ss.DiemKiemTra AS [Điểm kiểm tra]
FROM 
    StudentScores ss

UNION ALL

SELECT 
    NULL AS Stt,
    NULL AS [Mã sinh viên],
    NULL AS [Tên sinh viên],
    ag.TenTruong AS [Tên trường],
    NULL AS [Ngày kiểm tra],
    ag.TongDiem AS [Điểm kiểm tra]
FROM 
    AggregatedScores ag

UNION ALL

SELECT 
    NULL AS Stt,
    NULL AS [Mã sinh viên],
    NULL AS [Tên sinh viên],
    ts.TenTruong AS [Tên trường],
    NULL AS [Ngày kiểm tra],
    ts.TongDiem AS [Điểm kiểm tra]
FROM 
    TotalScores ts

ORDER BY 
    [Tên trường], Stt;