LSPR - Query Mahasiswa Lunas Komponen belum KRS

select
  a.IDMAHASISWA,
  e.NAMA,
  CONCAT(
  f.NAMA,
  '-',
  CASE 
    WHEN f.KODEJENJANG = 'C' THEN 'S1'
    WHEN f.KODEJENJANG = 'B' THEN 'S2'
    ELSE ''
  END
) AS nama_prodi,
  e.ANGKATAN,
  CASE
  WHEN e.STATUS ='A' THEN 'Aktif'
  WHEN e.STATUS ='C' THEN 'Cuti'
  WHEN e.STATUS ='N' THEN 'Non-Aktif'
  WHEN e.STATUS ='L' THEN 'Lulus'
  else ''
  end STATUS,
  f.NAMA PEMBIMBING,
  e.HP,
  a.IDKOMPONEN,
  a.TAGIHAN,
  COALESCE(SUM(b.BAYAR), 0) AS "Total Pembayaran",
  CASE
    WHEN COALESCE(SUM(b.BAYAR), 0) >= a.TAGIHAN THEN 'LUNAS'
    ELSE 'BELUM LUNAS'
  END AS status_pembayaran
from
  biayatagihan a
  join bayartagihan_detil b on a.IDMAHASISWA = b.IDMAHASISWA
  and a.TAHUN = b.TAHUN
  and a.SEMESTER = b.SEMESTER
  and a.IDKOMPONEN = b.IDKOMPONEN
  join bayartagihan c on b.TANGGAL = c.TANGGAL
  and b.IDMAHASISWA = c.IDMAHASISWA
  and c.STATUS = '1'
  left join pengambilanmk d on a.IDMAHASISWA = d.IDMAHASISWA
  and a.TAHUN = d.TAHUN
  and a.SEMESTER = d.SEMESTER
  join mahasiswa e on a.IDMAHASISWA = e.ID
  join prodi f on e.IDPRODI = f.ID
  join dosen g on e.IDDOSEN = g.id
where
  a.TAHUN = '2025'
  and a.semester = '2'
  and a.IDKOMPONEN in ('LSPS1', 'TF')
  and d.IDMAHASISWA is null
group by
  a.IDMAHASISWA,
  a.IDKOMPONEN,
  a.TAHUN,
  a.SEMESTER,
  a.BIAYA
HAVING
  COALESCE(SUM(b.BAYAR), 0) >= a.TAGIHAN
order by a.IDMAHASISWA;