LSPR - PENCARIAN NILAI TIDAK LULUS
S1 NON PJJ
SELECT
a.IDMAHASISWA,
b.NAMA,
CONCAT(
pd.NAMA,
' ',
CASE
WHEN pd.KODEJENJANG = 'C' THEN 'S1'
WHEN pd.KODEJENJANG = 'B' THEN 'S2'
ELSE 'Unknown'
END
) AS NAMAPRODI,
b.ANGKATAN,
b.STATUS,
a.IDMAKUL,
a.NAMA AS NAMAMK,
a.SKS,
a.SEMESTERMK,
a.BOBOT,
a.NILAI,
a.SIMBOL,
b.hp,
b.TELEPON,
b.EMAIL,
m2.EMAIL_PRIBADI
FROM
transkrip_detil a
JOIN mahasiswa b ON a.IDMAHASISWA = b.ID
JOIN prodi pd ON b.IDPRODI = pd.ID
left join mahasiswa2 m2 on b.ID = m2.ID
WHERE
b.IDPRODI not in ( '70203' ,'70101')
AND b.STATUS IN ('A', 'C', 'N')
AND a.BOBOT < 2
AND a.STATUSDELETE = '0'
AND a.NAMA NOT LIKE 'Thesis%'
AND a.NAMA NOT LIKE 'Internship%'
AND a.NAMA NOT LIKE 'Seminar%'
AND NOT EXISTS (
SELECT
1
FROM
pengambilanmk c
WHERE
c.THNSM = '20241'
AND c.IDPRODI not in ( '70203' ,'70101')
AND c.IDMAHASISWA = a.IDMAHASISWA
AND c.IDMAKUL = a.IDMAKUL
)
ORDER BY
a.NAMA;
S1 PJJ
SELECT
a.IDMAHASISWA,
b.NAMA,
CONCAT(
pd.NAMA,
' ',
CASE
WHEN pd.KODEJENJANG = 'C' THEN 'S1'
WHEN pd.KODEJENJANG = 'B' THEN 'S2'
ELSE 'Unknown'
END
) AS NAMAPRODI,
b.ANGKATAN,
b.STATUS,
a.IDMAKUL,
a.NAMA AS NAMAMK,
a.SKS,
a.SEMESTERMK,
a.BOBOT,
a.NILAI,
a.SIMBOL,
b.hp,
b.TELEPON,
b.EMAIL,
m2.EMAIL_PRIBADI
FROM
transkrip_detil a
JOIN mahasiswa b ON a.IDMAHASISWA = b.ID
JOIN prodi pd ON b.IDPRODI = pd.ID
left join mahasiswa2 m2 on b.ID = m2.ID
WHERE
b.IDPRODI in ( '70203')
AND b.STATUS IN ('A', 'C', 'N')
AND a.BOBOT < 1.7
AND a.STATUSDELETE = '0'
AND a.NAMA NOT LIKE 'Thesis%'
AND a.NAMA NOT LIKE 'Internship%'
AND a.NAMA NOT LIKE 'Seminar%'
AND NOT EXISTS (
SELECT
1
FROM
pengambilanmk c
WHERE
c.THNSM = '20242'
AND c.IDPRODI in ( '70203')
AND c.IDMAHASISWA = a.IDMAHASISWA
AND c.IDMAKUL = a.IDMAKUL
)
ORDER BY
a.NAMA;
S2
SELECT
a.IDMAHASISWA,
b.NAMA,
CONCAT(
pd.NAMA,
' ',
CASE
WHEN pd.KODEJENJANG = 'C' THEN 'S1'
WHEN pd.KODEJENJANG = 'B' THEN 'S2'
ELSE 'Unknown'
END
) AS NAMAPRODI,
b.ANGKATAN,
b.STATUS,
a.IDMAKUL,
a.NAMA AS NAMAMK,
a.SKS,
a.SEMESTERMK,
a.BOBOT,
a.NILAI,
a.SIMBOL,
b.hp,
b.TELEPON,
b.EMAIL,
m2.EMAIL_PRIBADI
FROM
transkrip_detil a
JOIN mahasiswa b ON a.IDMAHASISWA = b.ID
JOIN prodi pd ON b.IDPRODI = pd.ID
left join mahasiswa2 m2 on b.ID = m2.ID
WHERE
b.IDPRODI in ( '70101')
AND b.STATUS IN ('A', 'C', 'N')
AND a.BOBOT < 2.7
AND a.STATUSDELETE = '0'
AND a.NAMA NOT LIKE 'Thesis%'
AND a.NAMA NOT LIKE 'Internship%'
AND a.NAMA NOT LIKE 'Seminar%'
AND NOT EXISTS (
SELECT
1
FROM
pengambilanmk c
WHERE
c.THNSM = '20242'
AND c.IDPRODI in ( '70101')
AND c.IDMAHASISWA = a.IDMAHASISWA
AND c.IDMAKUL = a.IDMAKUL
)
ORDER BY
a.NAMA;
