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;