A-30 6.4. Layar Chart Laporan Grade dan Nilai Akhir (Tanpa Dosen) ~ A, B, C, D, E, F, G, L
Layar ini berisi grafik jumlah mahasiswa yang mendapatkan grade A, B, C, D, E, F, G, L.
6.5. Layar Chart Laporan Grade dan Nilai Akhir (Tanpa Dosen) ~ Avg Nilai Akhir
A-31 Layar ini berisi grafik tentang rata – rata nilai akhir mahasiswa.
6.6. Layar Detail Laporan Grade dan Nilai Akhir (Tanpa Dosen) ~ A, B, C, D, E, F, G, L, % A, % B, % C, % D, % E, % F, % G, % L
Layar ini berisi informasi detail jumlah mahasiswa atau persentase mahasiswa yang mendapatkan grade A atau B atau C atau D atau E atau F atau G atau L.
6.7. Layar Detail Laporan Grade dan Nilai Akhir (Tanpa Dosen) ~ Avg Nilai Akhir
Layar ini berisi tentang detail informasi rata – rata nilai akhir mahasiswa.
A-32 6.8. Layar Laporan Grade dan Nilai Akhir (Dengan Dosen)
Layar ini berisi tentang informasi tentang grade dan nilai akhir mahasiswa dengan adanya dimensi dosen. Semua layar grafik dan layar detail yang ada pada laporan ini, sama dengan layar grafik dan layar detail yang ada di laporan distribusi grade dan nilia akhir tanpa dosen.
A-33 6.9. Layar Laporan Distribusi IP dan Lulus Sidang
Layar ini berisi informasi tentang IP mahasiswa dan mahasiswa yang lulus sidang.
A-34 6.10. Layar Chart Laporan Distribusi IP dan Lulus Sidang ~ % IPK 0.00 - 4.00
Layar ini berisi grafik tentang persentase IPK mahasiswa dari yang IPKnya 0 sampai dengan mahasiswa yang IPKnya 4.00
6.11. Layar Chart Laporan Distribusi IP dan Lulus Sidang ~ % IPS 0.00 - 4.00
A-35
Layar ini berisi informasi tentang persentase mahasiswa dari yang IPSnya 0 sampai dengan yang IPSnya 4.00
6.12. Layar Chart Laporan Distribusi IP dan Lulus Sidang ~ Avg IpSem, Avg Ipkum
A-36
Layar ini berisi grafik tentang rata – rata IP semester dan rata – rata IP kumulatif
6.13. Layar Chart Laporan Distribusi IP dan Lulus Sidang ~ Avg Sksem, Avg Skkum
Layar ini berisi grafik tentang rata – rata SKS semester dan rata – rata SKS kumulatif mahasiswa.
A-37 6.14. Layar Chart Laporan Distribusi IP dan Lulus Sidang ~ IPK 0.00 - 4.00
Layar ini berisi grafik tentang jumlah mahasiswa yang IPKnya 0 sampai dengan 4.00
6.15. Layar Chart Laporan Distribusi IP dan Lulus Sidang ~ IPS 0.00 - 4.00
A-38
Layar ini berisi grafik tentang jumlah mahasiswa yang IPSnya 0 sampai dengan 4.00
6.16. Layar Chart Laporan Distribusi IP dan Lulus Sidang ~ IPS Min, IPS Max, IPK Min, IPK Max
A-39
Layar ini berisi grafik tentang IPS minimum, IPS maksimal, IPK minimum dan IPK maksimal yang ada.
6.17. Layar Chart Laporan Distribusi IP dan Lulus Sidang ~ Lulus UjiKe
A-40
Layar ini berisi grafik tentang jumlah mahasiswa yang lulus uji sidang ke
6.18. Layar Chart Laporan Distribusi IP dan Lulus Sidang ~ Skkum 0 – 145
A-41
Layar ini berisi grafik tentang SKS kumulatif mahasiswa.
A-42 6.19. Layar Detail Laporan Distribusi IP dan Lulus Sidang ~ IPS 0.00 - 4.00, IPKum 0.00 - 4.00, Skkum 0.00 - 4.00
Layar ini berisi informasi detail mahasiswa yang IPSnya 0 sampai dengan 4.00 atau IPKnya 0 sampai dengan 4.00 atau SKS kumulatifnya antara 0 sampai dengan 145.
6.20. Layar Detail Laporan Distribusi IP dan Lulus Sidang ~ Lulus UjiKe
Layar ini berisi informasi detail mahasiswa yang lulus sidang ke
7. Layar Transfer
A-43
Layar ini merupakan layar yang digunakan untuk mentransfer data yang ada di sistem operasional ke dalam data warehouse.
8. Layar About
B-1 B. LAMPIRAN CODING
1. Query untuk tabel fakta Registrasi create proc dbo.spr_create_fact_registrasi @priod_awal char (4), @kdsem_awal char (1), @priod_akhir char (4), @kdsem_akhir char (1) as set ansi_warnings off set nocount on insert into fact_registrasi select distinct --informasi mengenai distribusi matakuliah yang ada a.priod, a.kdsem, a.kdfak, a.kdjur, a.kdstu, a.shift, a.thang, --informasi mengenai matakuliah apa saja yang terdapat pada distribusi matakuliah b.kdmtk, --informasi mengenai mahasiswa yang blum lulus matakuliah --kalau '' maka tidak ada mahasiswa yang blum mengambil matakuliah tersebut nimhs = coalesce (d.nimhs, ''), --mahasiswa yang terdaftar yang blum lulus matakuliah -- 1 maka terdaftar , 0 maka tidak terdaftar mhs_terdaftar = case when e.nimhs is not null then '1' else '0' end, --yang seharusnya paket atau krs, jika (paket = 0) maka krs, jika (paket = 1) maka paket
B-2 --dari sini bisa diketahui dia ambil shift yang mana jika paket = 1 --jika paket = 0 maka shiftnya adalah yang transaksi_krss(i) tidak null f.paket, f.nmmhs, --untuk ambil mahasiswa yang aktif semester itu dan berhasil melakukan registrasi baik -- paket maupun krs, jika ada maka berhasil melakukan registrasi status_registrasi = case when g.nimhs is not null then '1' else '0' end, --untuk mengambil informasi apakah mahasiswa tersebut telah melakukan paket, --jika tidak berarti krs / manual --paket = 1, krs = 2, manual = 3, generate dari sistem = 4 registrasi_by = case when h.kdpkt is not null then '1' when i.stsrc = 'A' then '2' when i.stsrc = 'T' then '3' when i.stsrc = 'R' then '4' end, --untuk mengetahui mahasiswa ambil krss apakah sedang skripsi atau bukan --1 = sedang skripsi, 2 = kuliah reguler, 3 = lulus skripsi status_perkuliahan = case when j.nimhs is not null then '1' when j.nimhs is null and k.nimhs is null then '2' when k.nimhs is not null then '3' end, --untuk ambil grade matakuliah tersebut sebelumnya, jika grade = null maka mahasiswa --tersebut blum ambil matakuliah tersebut sebelumnya l.grade, --informasi faktultas , jurusan, dan jenjang studi
B-3 m.nmfak, n.nmjur, o.nmstu, --untuk mengambil informasi matakuliah p.nmmtk, p.kdggs, q.nmggs from --pertama-tama dari transaksi_distribusi_angkatan untuk melihat nomor distribusi -- matakuliah yang harus diambil oleh mahasiswa angkatan tertentu (select priod, kdsem, kdfak, kdjur, kdstu, shift, thang, idmst from transaksi_distribusi_angkatan where stsrc = 'A' and priod+kdsem >= @priod_awal+@kdsem_awal and priod+kdsem <= @priod_akhir+@kdsem_akhir ) a --kemudian dijoin dengan transaksi_matakuliah_distribusi untuk mengambil --matakuliah apa saja yang terdapat pada distribusi tersebut join (select distinct idmst, kdmtk from transaksi_matakuliah_distribusi where stsrc = 'A' )b on a.idmst = b.idmst join (select distinct priod, kdsem, kdfak, kdjur, kdstu, shift, kdmtk from tabel_matakuliah_dibuka where stsrc = 'A' )c on a.priod = c.priod and a.kdsem = c.kdsem and a.kdfak = c.kdfak and a.kdjur = c.kdjur and a.kdstu = c.kdstu and b.kdmtk = c.kdmtk and a.shift = case when c.shift = '0' then a.shift else c.shift end
B-4 join wrfkrs d on d.stsrc = 'A' and a.priod = d.priod and a.kdsem = d.kdsem and b.kdmtk = d.kdmtk left join (select x.priod, x.kdsem, y.nimhs, y.thang, y.kdstu, jml_cuti = count (z.nimhs) from (select distinct priod, kdsem, kdfak, kdjur, kdstu, thang from transaksi_distribusi_angkatan where stsrc = 'A') x left join (select nimhs, kdfak, kdjur, kdstu, thang from msmhs1 where stsrc = 'A') y on x.kdfak = y.kdfak and x.kdjur = y.kdjur and x.kdstu = y.kdstu and x.thang = y.thang left join transaksi_cuti_resmi z on z.stsrc = 'A' and y.nimhs = z.nimhs and x.priod + x.kdsem >= z.priod + z.kdsem group by x.priod, x.kdsem, y.nimhs, y.thang, y.kdstu --syarat mahasiswa terdaftar jika s1 maka 7 tahun dan d3 maka 5 tahun --untuk mahasiswa cuti tidak akan muncul having (convert (int, x.priod) - convert (int, y.thang)) * 2 + x.kdsem - coalesce (count (z.nimhs), 0) <= case when y.kdstu = '01' and x.kdsem <> '3' then 14 when y.kdstu = '01' and x.kdsem = '3' then 15 when y.kdstu = '02' and x.kdsem <> '3' then 10 else 11 end ) e on d.priod = e.priod and d.kdsem = e.kdsem and d.nimhs = e.nimhs
B-5 left join (select priod, kdsem, nimhs, nmmhs, paket, shift from tb_info_mhs where stsrc = 'A') f on d.priod = f.priod and d.kdsem = f.kdsem and d.nimhs = f.nimhs and a.shift = case when paket = '1' then f.shift else a.shift end left join (select distinct priod, kdsem, nimhs from transaksi_nilai_mahasiswa where stsrc='A') g on f.priod = g.priod and f.kdsem = g.kdsem and f.nimhs = g.nimhs left join transaksi_generate_paket h on h.stsrc = 'A' and f.priod = h.priod and f.kdsem = h.kdsem and f.nimhs = h.nimhs left join (select distinct priod, kdsem, nimhs, kdmtk, stsrc, shift from transaksi_krss where stsrc <> 'D' )i on f.priod = i.priod and f.kdsem = i.kdsem and f.nimhs = i.nimhs and b.kdmtk = i.kdmtk and a.shift = i.shift left join (select distinct priod, kdsem, nimhs from transaksi_krss j1 join master_matakuliah j2 on j2.stsrc = 'A' and j1.kdmtk = j2.kdmtk where j1.stsrc = 'A' and kdkom in ('LL0801', 'LL0803', 'LL0667') )j on i.priod = j.priod and i.kdsem = j.kdsem and i.nimhs = j.nimhs
B-6 --dijoin berdasarkan nimhs aja karena bisa saja dia lulusnya priod dan kdsem -- sebelumnya, jadi ga perlu join by priod, kdsem left join (select distinct nimhs from transaksi_nilai_skripsi where grade in ('A', 'B', 'C') and stsrc = 'A' )k on i.nimhs = k.nimhs --ini grade yang diambil adalah grade yg terbagus dari priod dan kdsem sebelumnya left join (select l1.priod, l1.kdsem , l1.nimhs , l1.kdmtk, grade = min(grade) from (select priod, kdsem, nimhs, kdmtk from transaksi_krss where stsrc = 'A' )l1 left join (select priod, kdsem, nimhs, kdmtk, grade from transaksi_nilai_mahasiswa where stsrc = 'A' )l2 on l1.nimhs = l2.nimhs and l1.kdmtk = l2.kdmtk and l1.priod+l1.kdsem > l2.priod+l2.kdsem group by l1.priod, l1.kdsem , l1.nimhs , l1.kdmtk )l on i.priod = l.priod and i.kdsem = l.kdsem and i.kdmtk = l.kdmtk and i.nimhs = l.nimhs left join tbfak m
B-7 on m.stsrc = 'A' and a.kdfak = m.kdfak left join tbjur n on n.stsrc = 'A' and a.kdjur = n.kdjur left join tbstu o on o.stsrc = 'A' and a.kdstu = o.kdstu left join master_matakuliah p on p.stsrc = 'A' and b.kdmtk = p.kdmtk left join tabel_gugus q on q.stsrc = 'A' and p.kdggs = q.kdggs
2. Query View untuk registrasi View untuk registrasi dibagi 2 dengan menggunakan stored procedure yaitu view untuk
melihat
jumlah
mahasiswa
yang
registrasi
secara
paket
dan
krs
(spr_dwh_mhs_registrasi) dan view untuk melihat jumlah mahasiswa yang melakukan krss (spr_dwh_mhs_krss)
2.1. spr_dwh_mhs_registrasi create proc dbo.spr_dwh_mhs_registrasi @kdfak char (2) = '', @kdjur char (2) = '' as select Fakultas = a.kdfak + ' - ' + a.nmfak, Jurusan = a.kdjur + ' - ' + a.nmjur, Jenjang_Studi = a.kdstu + ' - ' + a.nmstu,
B-8 priod = a.priod, semester = case when a.kdsem = '1' or b.kdsem = '1' then 'Ganjil' when a.kdsem = '2' or b.kdsem = '2' then 'Genap' when a.kdsem = '3' or b.kdsem = '3' then 'Pendek' end, thang = a.thang, jml_paket = sum (case when a.paket = '1' then 1 else 0 end), jml_krs = sum (case when a.paket = '0' then 1 else 0 end), jml_real_paket = sum (case when b.registrasi_by = '1' then 1 else 0 end), jml_real_krs = sum (case when b.registrasi_by = '2' and c.nimhs is null then 1 else 0 end), jml_real_manual = sum (case when b.registrasi_by is null and c.nimhs is not null then 1 else 0 end), jml_real_krs_manual = sum (case when b.registrasi_by = '2' and c.nimhs is not null then 1 else 0 end) from (select distinct kdfak, nmfak, kdjur, nmjur, kdstu, nmstu, thang, nimhs, priod, kdsem, paket from fact_registrasi where paket is not null and kdfak = case when @kdfak = '' then kdfak else @kdfak end and kdjur = case when @kdjur = '' then kdjur else @kdjur end )a left join (-- untuk ambil yang real krs dan paket select distinct nimhs, priod, kdsem, registrasi_by
B-9 from fact_registrasi where paket is not null and registrasi_by in ('1','2') and kdfak = case when @kdfak = '' then kdfak else @kdfak end and kdjur = case when @kdjur = '' then kdjur else @kdjur end )b on a.priod = b.priod and a.kdsem = b.kdsem and a.nimhs = b.nimhs left join (--untuk ambil yang real manual select distinct nimhs, priod, kdsem from fact_registrasi where paket is not null and registrasi_by = '3' and kdfak = case when @kdfak = '' then kdfak else @kdfak end and kdjur = case when @kdjur = '' then kdjur else @kdjur end )c on c.priod = coalesce (b.priod, a.priod) and c.kdsem = coalesce (b.kdsem, a.kdsem) and c.nimhs = coalesce (b.nimhs, a.nimhs)
group by a.kdfak + ' - ' + a.nmfak, a.kdjur + ' - ' + a.nmjur, a.kdstu + ' - ' + a.nmstu, a.priod, case when a.kdsem = '1' or b.kdsem = '1' then 'Ganjil' when a.kdsem = '2' or b.kdsem = '2' then 'Genap' when a.kdsem = '3' or b.kdsem = '3' then 'Pendek'
B-10 end, a.thang
order by a.kdfak + ' - ' + a.nmfak, a.kdjur + ' - ' + a.nmjur, a.kdstu + ' - ' + a.nmstu, a.priod, case when a.kdsem = '1' or b.kdsem = '1' then 'Ganjil' when a.kdsem = '2' or b.kdsem = '2' then 'Genap' when a.kdsem = '3' or b.kdsem = '3' then 'Pendek' end, a.thang
2.2. spr_dwh_mhs_krss create proc dbo.spr_dwh_mhs_krss @kdfak char (2) = '', @kdjur char (2) = '' as select Fakultas = ltrim(rtrim(a.kdfak + ' - ' + a.nmfak)), Jurusan = ltrim(rtrim (a.kdjur + ' - ' + a.nmjur)), Jenjang_Studi = ltrim (rtrim(a.kdstu + ' - ' + a.nmstu)), a.priod, Semester = case when a.kdsem = '1' then 'Ganjil'
B-11 when a.kdsem = '2' then 'Genap' when a.kdsem = '3' then 'Pendek' end, thang, Gugus_Mtk = a.kdggs + ' - ' + a.nmggs, Matakuliah = a.kdmtk + ' - ' + a.nmmtk, a.shift, Status = case when a.status_perkuliahan = '1' then 'Sedang Skripsi' when a.status_perkuliahan = '2' then 'Kuliah Reguler' when a.status_perkuliahan = '3' then 'Lulus Skripsi' end, jml_mhs = count (a.nimhs), D = sum (case when a.grade = 'D' then 1 else 0 end), EF = sum (case when a.grade in ('E', 'F') then 1 else 0 end), Blm_ambil = sum (case when a.grade is null then 1 else 0 end) from (select distinct kdfak, nmfak, kdjur, nmjur, kdstu, nmstu, priod, kdsem, thang, kdggs, nmggs, kdmtk, nmmtk, shift, nimhs, status_perkuliahan, grade from fact_registrasi where registrasi_by = '2' )a group by ltrim(rtrim(a.kdfak + ' - ' + a.nmfak)), ltrim(rtrim (a.kdjur + ' - ' + a.nmjur)), ltrim (rtrim(a.kdstu + ' - ' + a.nmstu)), a.priod, case when a.kdsem = '1' then 'Ganjil' when a.kdsem = '2' then 'Genap' when a.kdsem = '3' then 'Pendek' end, thang, a.kdggs + ' - ' + a.nmggs,
B-12 a.kdmtk + ' - ' + a.nmmtk, a.shift, case when a.status_perkuliahan = '1' then 'Sedang Skripsi' when a.status_perkuliahan = '2' then 'Kuliah Reguler' when a.status_perkuliahan = '3' then 'Lulus Skripsi' end
3. Query untuk tabel fakta Perkuliahan create proc dbo.spr_create_fact_perkuliahan @priod_awal char (4), @kdsem_awal char (1), @priod_akhir char (4), @kdsem_akhir char (1) as insert into fact_perkuliahan select --untuk mengambil info mhs aktif dan teregistrasi a.priod, a.kdsem, a.nimhs, a.kdmtk, a.kelas, --untuk mengambil jadwal kuliah b.kdhri, b.kdjam, b.kdrng, b.kddsn, b.sts_mcl, b.stmtk, --untuk melihat kehadiran dosen selama matakuliah tersebut Tanggal = c.tglab, stsab_dosen = c.stsab, c.tglpg, c.dsnpg, --untuk melihat kehadiran mahasiswa, jika hadir maka null --kalau A maka absen kalau I maka ijin .. kalau ga ada disini maka hadir stsab_mhs = d.stsab, kdrng_pg = e.kdrng,
B-13 --untuk melihat info matakuliah, darisini bisa diliat mana yang matakuliah skripsi g.nmmtk, matakuliah_skripsi = case when g.kdkom in ('LL0801', 'LL0803', 'LL0667') then 1 else 0 end, g.kdggs, --untuk melihat info dosen h.nmdsn, --untuk melihat info mhs i.nmmhs, i.kdjur, i.kdstu, i.kdfak, i.thang, --untuk melihat info gugus_matakuliah, fakultas, jurusan, dan jenjang studi j.nmggs, k.nmfak, l.nmjur, m.nmstu, --untuk melihat info jumlah pertemuan matakuliah target_pertemuan = n.jmlpert, --untuk melihat sks ajar jml_sks_ajar = o.jmlsks, --nama dosen pengganti dan informasi ikad nmdsn_pg = p.nmdsn, q.IkadQ, q.IkadD from (select priod, kdsem, nimhs, kdmtk, kelas from transaksi_nilai_mahasiswa where stsrc = 'A' and priod+kdsem >= @priod_awal+@kdsem_awal and priod+kdsem <= @priod_akhir+@kdsem_akhir --supaya lebih sedikit.. ini nanti dihilangkan aja and nimhs in (select nimhs from msmhs1 where kdjur = '24' and stsrc = 'A' and thang = '2001') )a
B-14 --dijoin (bukan left join) supaya matakuliah kerja praktek tidak diambil --karena tidak ada dosen --dan supaya kelas = xxxxx tidak diambil karena tidak ada juga di master_jadwal_kuliah join (select priod, kdsem, kdmtk, kelas, kdhri, kdjam, kdrng, sts_mcl, kddsn,stmtk from master_jadwal_kuliah where coalesce (stsrc, '') <> 'D'and priod+kdsem >= @priod_awal+@kdsem_awal and priod+kdsem <= @priod_akhir+@kdsem_akhir )b on a.priod = b.priod and a.kdsem = b.kdsem and a.kdmtk = b.kdmtk and a.kelas = b.kelas --dijoin (bukan left join)karena ternyata matakuliah kerja praktek ada yang didatakan di --master_jadwal_kuliah tapi tidak ada perkuliahan (tidak ada di --transaksi_jadwal_absensi) join (select distinct priod, kdsem, kdmtk, kelas, tglab, kdhri, kdjam, kddsn, kdrng, tglpg, stsab, dsnpg, noabs from transaksi_jadwal_absensi where stsrc = 'A' )c on b.priod = c.priod and b.kdsem = c.kdsem and b.kdmtk = c.kdmtk and b.kelas = c.kelas and b.kddsn = c.kddsn and b.kdhri = c.kdhri and b.kdjam = c.kdjam and b.kdrng = c.kdrng left join (select priod, kdsem, noabs, nimhs, stsab from transaksi_absensi_mahasiswa
B-15 where stsrc = 'A' and priod+kdsem >= @priod_awal+@kdsem_awal and priod+kdsem <= @priod_akhir+@kdsem_akhir )d on c.priod = d.priod and c.kdsem = d.kdsem and c.noabs = d.noabs and a.nimhs = d.nimhs left join (select priod, kdsem, noabs, kdrng, tgpmj from transaksi_peminjaman_ruang where stsrc <> 'D' and jnpmj = '4' and status = 'H' and priod+kdsem >= @priod_awal+@kdsem_awal and priod+kdsem <= @priod_akhir+@kdsem_akhir )e on d.priod = e.priod and d.kdsem = e.kdsem and d.noabs = e.noabs and c.tglpg = e.tgpmj --*mau join kdmtk di transaksi_nilai_mhs dengan matakuliah yang skripsi --*untuk menandakan bahwa kdmtk itu kdmtk skripsi left join master_matakuliah g on g.stsrc = 'A' and a.kdmtk = g.kdmtk left join master_dosen h on h.stsrc = 'A' and b.kddsn = h.kddsn left join msmhs1 i on i.stsrc = 'A' and a.nimhs = i.nimhs left join tabel_gugus j on j.stsrc = 'A' and g.kdggs = j.kdggs left join tbfak k
B-16 on k.stsrc = 'A' and i.kdfak = k.kdfak left join tbjur l on l.stsrc = 'A' and i.kdjur = l.kdjur left join tbstu m on m.stsrc = 'A' and i.kdstu = m.kdstu left join (select priod, kdsem, kdmtk, stmtk, jmlpert from tabel_pertemuan_matakuliah where stsrc = 'A' and priod+kdsem >= @priod_awal+@kdsem_awal and priod+kdsem <= @priod_akhir+@kdsem_akhir )n on b.priod = n.priod and b.kdsem = n.kdsem and b.kdmtk = n.kdmtk and b.stmtk = n.stmtk left join (select priod, kdsem, kddsn, kdmtk, kelas, stmtk, jmlsks from vp_sihon_jumlah_sks_mengajar where priod+kdsem >= @priod_awal+@kdsem_awal and priod+kdsem <= @priod_akhir+@kdsem_akhir )o on b.priod = o.priod and b.kdsem = o.kdsem and b.kddsn = o.kddsn and b.kdmtk = o.kdmtk and b.kelas = o.kelas and b.stmtk = o.stmtk left join master_dosen p on p.stsrc = 'A' and c.dsnpg = p.kddsn --ditambahkan dengan tabel_ikad ini untuk melihat ikad dosen --ambil yang jenis nya max
B-17 left join ( select priod, kdsem, kdmtk, kelas, kddsn, ikadq = eval, ikadd from tabel_evaluasi_dosen_ikad_2 where stsrc = 'A' )q on b.priod = q.priod and b.kdsem = q.kdsem and b.kdmtk = q.kdmtk and b.kelas = q.kelas and b.kddsn = q.kddsn
4. Query View untuk perkuliahan View untuk perkuliahan dibagi 3 dengan menggunakan stored procedure yaitu view untuk
melihat
data
–
data
mengenai
perkuliahan
berdasarkan
dosen
(spr_dwh_perkuliahan), view untuk melihat data – data perkuliahan berdasarkan mahasiswa (spr_dwh_perkuliahan_mhs), dan view untuk melihat jumlah mahasiswa aktif dan skripsi (spr_dwh_mhs)
4.1. spr_dwh_perkuliahan alter proc dbo.spr_dwh_perkuliahan @kdfak char (2) = '', @kdjur char (2) = '' as select a.priod, Semester = case when a.kdsem = '1' then 'Ganjil' when a.kdsem = '2' then 'Genap' when a.kdsem = '3' then 'Pendek' end, a.thang,
B-18 Fakultas = ltrim(rtrim(a.kdfak + ' - ' +a.nmfak)), Jurusan = ltrim(rtrim (a.kdjur + ' - ' + a.nmjur)), Jenjang_Studi = ltrim (rtrim(a.kdstu + ' - ' + a.nmstu)), Gugus_Mtk = ltrim (rtrim(a.kdggs + ' - ' + a.nmggs)), Dosen = ltrim(rtrim(a.kddsn + ' - ' + a.nmdsn)), Matakuliah = ltrim(rtrim(a.kdmtk + ' - ' + a.nmmtk)), a.kelas, Mcl = coalesce (a.sts_mcl, '0') + ' - ' + case when a.sts_mcl = '1' then 'Ya' else 'Tidak' end, Stmtk = a.stmtk + ' - ' + case when a.stmtk = 'P' then 'Asistensi - Tdk Ada Absensi' when a.stmtk = 'Y' then 'Praktikum' when a.stmtk = 'X' then 'Praktikum - Ada Absensi' when a.stmtk = 'Z' then 'Praktikum - Ada Absensi' when a.stmtk = 'T' then 'Dosen - Ada Absensi' when a.stmtk = 'A' then 'Asistensi - Ada Absensi' end, ikadQ, ikadD, target_pertemuan = min (coalesce (a.target_pertemuan, 0)), real_pertemuan = sum (case when a.stsab_dosen = 'H'or stsab_dosen = 'D' then 1 else 0 end), dosen_H = sum (case when a.stsab_dosen = 'H' then 1 else 0 end), dosen_D = sum (case when a.stsab_dosen = 'D' then 1 else 0 end),
B-19 dosen_A = sum (case when a.stsab_dosen = 'A' or stsab_dosen is null then 1 else 0 end), dosen_I = sum (case when a.stsab_dosen = 'I' then 1 else 0 end), digantikan = sum (case when a.dsnpg is not null then 1 else 0 end), dimajukan = sum (case when a.tanggal > a.tglpg then 1 else 0 end), jml_sks_ajar = min (coalesce (jml_sks_ajar , 0)), jml_mcl = min (case when a.sts_mcl = '1' then 1 else 0 end), jml_kelas = 1, jml_dsn_ikadQ = min (case when coalesce (a.ikadQ, '') <> '' then 1 else 0 end), jml_dsn_ikadD = min (case when coalesce (a.ikadD, '') <> '' then 1 else 0 end) from (select distinct priod, kdsem, thang, kdfak, nmfak, kdjur, nmjur, kdstu, nmstu, kdggs, nmggs, kdmtk, nmmtk, kddsn, nmdsn, kelas, stsab_dosen, target_pertemuan, dsnpg, tanggal, kdjam, tglpg, sts_mcl, jml_sks_ajar, ikadQ, ikadD, stmtk from fact_perkuliahan where kdfak = case when @kdfak = '' then kdfak else @kdfak end and kdjur = case when @kdjur = '' then kdjur else @kdjur end )a group by a.priod, a.kdsem, a.thang, a.kdfak, a.nmfak, a.kdjur, a.nmjur, a.kdstu, a.nmstu, a.kdggs, a.nmggs, a.kdmtk, a.nmmtk, a.kddsn, a.nmdsn, a.kelas, coalesce (a.sts_mcl, '0') + ' - ' + case when a.sts_mcl = '1' then 'Ya' else 'Tidak' end, a.stmtk + ' - ' +
B-20 case when a.stmtk = 'P' then 'Asistensi - Tdk Ada Absensi' when a.stmtk = 'Y' then 'Praktikum' when a.stmtk = 'X' then 'Praktikum - Ada Absensi' when a.stmtk = 'Z' then 'Praktikum - Ada Absensi' when a.stmtk = 'T' then 'Dosen - Ada Absensi' when a.stmtk = 'A' then 'Asistensi - Ada Absensi' end, ikadQ, ikadD
4.2. spr_dwh_perkuliahan_mhs alter proc dbo.spr_dwh_mhs @kdfak char (2) = '', @kdjur char (2) = '' as select Fakultas = a.kdfak + ' - '+ a.nmfak, Jurusan = a.kdjur + ' - '+ a.nmjur, Jenjang_Studi = a.kdstu + ' - ' + a.nmstu, a.thang, a.priod, Semester = case when a.kdsem = '1' then 'Ganjil' when a.kdsem = '2' then 'Genap' when a.kdsem = '3' then 'Pendek' end, jml_mhs_aktif = count (a.nimhs), jml_mhs_skripsi = count (b.nimhs) from (select distinct kdfak, nmfak, kdjur, nmjur, kdstu, nmstu, thang, priod, kdsem, nimhs from fact_perkuliahan
B-21 where kdfak = case when @kdfak = '' then kdfak else @kdfak end and kdjur = case when @kdjur = '' then kdjur else @kdjur end )a left join (select distinct kdfak, nmfak, kdjur, nmjur, kdstu, nmstu, thang, priod, kdsem, nimhs from fact_perkuliahan where kdfak = case when @kdfak = '' then kdfak else @kdfak end and kdjur = case when @kdjur = '' then kdjur else @kdjur end and matakuliah_skripsi = 1 )b on a.priod = b.priod and a.kdsem = b.kdsem and a.nimhs = b.nimhs group by a.kdfak, a.nmfak, a.kdjur, a.nmjur, a.kdstu, a.nmstu, a.thang, a.priod, a.kdsem
4.3. spr_dwh_mhs create proc dbo.spr_dwh_mhs @kdfak char (2) = '', @kdjur char (2) = '' as select Fakultas = a.kdfak + ' - '+ a.nmfak, Jurusan = a.kdjur + ' - '+ a.nmjur, Jenjang_Studi = a.kdstu + ' - ' + a.nmstu, a.thang, a.priod, Semester = case when a.kdsem = '1' then 'Ganjil' when a.kdsem = '2' then 'Genap'
B-22 when a.kdsem = '3' then 'Pendek' end, jml_mhs_aktif = count (a.nimhs), jml_mhs_skripsi = count (b.nimhs) from (select distinct kdfak, nmfak, kdjur, nmjur, kdstu, nmstu, thang, priod, kdsem, nimhs from fact_perkuliahan where kdfak = case when @kdfak = '' then kdfak else @kdfak end and kdjur = case when @kdjur = '' then kdjur else @kdjur end )a left join (select distinct kdfak, nmfak, kdjur, nmjur, kdstu, nmstu, thang, priod, kdsem, nimhs from fact_perkuliahan where kdfak = case when @kdfak = '' then kdfak else @kdfak end and kdjur = case when @kdjur = '' then kdjur else @kdjur end and matakuliah_skripsi = 1 )b on a.priod = b.priod and a.kdsem = b.kdsem and a.nimhs = b.nimhs group by a.kdfak, a.nmfak, a.kdjur, a.nmjur, a.kdstu, a.nmstu, a.thang, a.priod, a.kdsem
B-23 5. Query untuk tabel fakta ujian create proc dbo.sp_create_fact_ujian @priod_awal char (4), @kdsem_awal char (1), @priod_akhir char (4), @kdsem_akhir char (1) as insert into fact_ujian select distinct --info tentang matakuliah yang diambil mahasiswa pada satu semester a.priod, a.kdsem, a.nimhs, a.kdmtk, a.kelas --info ujian ,kduji = coalesce (b.kduji, 'x'), Tanggal = b.tgluj, b.jamml, b.jamsl --info ketidak hadiran mahasiswa dalam ujian , status_absensi = c.status --info master ujian ada pengawas nya disini-- pgaws1 = kddsn, pgaws2 = nopeg --kalau pgaws2 in ('X', 'P', 'G') maka dia outsource , d.pgaws1, d.pgaws2 --info mengenai jadwal sidang skripsi jika mengambil skripsi ,Tanggal_sidang = e.tanggal, e.shift, ujike = coalesce (e.ujike, 0) --status kehadiran sidang pendadaran ,mhs_absen_sidang = case when f.status_abs is not null then 1 else 0 end --keterangan mahasiswa ,g.nmmhs, g.kdjur, g.kdfak, g.kdstu, g.thang
B-24 --keterangan matakuliah ,h.nmmtk, h.kdggs, i.nmggs --info pengawas ,nm_pgaws1 = j.nm_pgaws, nm_pgaws2 = k.nm_pgaws --info jurusan, fakultas ,l.nmjur, m.nmfak --info tentang ruang ,kdrng = case when b.kdrng is null then e.kdrng else b.kdrng end --info tentang ujian, jenjang studi ,n.nmuji, o.nmstu from ( select distinct priod, kdsem, nimhs, kdmtk, kelas from transaksi_nilai_mahasiswa where stsrc='A' and priod+kdsem >= @priod_awal+@kdsem_awal and priod+kdsem <= @priod_akhir+@kdsem_akhir )a left join transaksi_ujian_mahasiswa b on b.stsrc = 'A' and a.priod = b.priod and a.kdsem = b.kdsem and a.kdmtk = b.kdmtk and a.kelas = b.kelas and a.nimhs = b.nimhs left join ( select c1.priod, c1.kdsem, c1.kduji, c1.kdmtk, c1.kelas, c1.nimhs, c1.kdrng, c1.status from (select distinct priod, kdsem, kduji, kdmtk,kelas, nimhs, kdrng, status
B-25 from transaksi_ketidakhadiran_dan_kecurangan_ujian where stsrc = 'A' ) c1 left join ( select priod, kdsem, nimhs, kdmtk, kelas, kduji from transaksi_ketidakhadiran_dan_kecurangan_ujian where stsrc = 'A' group by priod, kdsem, nimhs, kdmtk, kelas, kduji having count(status ) > 1 ) c2 on c1.priod = c2.priod and c1.kdsem = c2.kdsem and c1.kduji = c2.kduji and c1.kdmtk = c2.kdmtk and c1.kelas = c2.kelas and c1.nimhs = c2.nimhs where c1.status = case when c2.priod is not null then 'T' else c1.status end )c on b.priod = c.priod and b.kdsem = c.kdsem and b.kduji = c.kduji and b.kdmtk = c.kdmtk and b.kelas = c.kelas and b.nimhs = c.nimhs and b.kdrng = c.kdrng left join (select priod, kdsem, kdmtk, kelas, kdrng, kduji, jamuj, pgaws1 = case when ltrim(rtrim(pgaws1)) = '' then null else pgaws1 end, pgaws2 = case when ltrim(rtrim(pgaws2)) = '' then null else pgaws2 end from master_jadwal_ujian where stsrc ='A' )d on b.priod = d.priod and b.kdsem = d.kdsem and b.kdmtk = d.kdmtk and b.kelas = d.kelas and b.kdrng = d.kdrng and b.kduji = d.kduji and b.jamml = d.jamuj left join (
B-26 select distinct f1.tanggal, f1.shift, kdrng = f1.ruang, f1.nourt_Ujian, f5.kdmtk, f2.nimhs, f4.kelas, f4.priod, f4.kdsem, f2.ujike from transaksi_nourut_Ujian_skripsi f1 join transaksi_ujian_mahasiswa_skripsi f2 on f2.stsrc = 'A' and f1.priod = f2.priod and f1.kdsem = f2.kdsem and f1.nourt_ujian = f2.nourt_Ujian join transaksi_mahasiswa_skripsi f3 on f3.stsrc = 'A' and f2.priod = f3.priod and f2.kdsem = f3.kdsem and f2.nimhs = f3.nimhs join master_mahasiswa_skripsi f4 on f4.stsrc = 'A' and f3.priod = f4.priod and f3.kdsem = f4.kdsem and f3.no_skripsi = f4.no_skripsi join Tabel_Kdmtk_Topik_Skripsi f5 on f5.stsrc = 'A' and f4.nourt_topik = f5.nourut_topik where f1.stsrc = 'A' and f4.priod+f4.kdsem >= @priod_awal+@kdsem_awal and f4.priod+f4.kdsem <= @priod_akhir+@kdsem_akhir )e on a.priod = e.priod and a.kdsem = e.kdsem and a.kdmtk = e.kdmtk and a.kelas = e.kelas and a.nimhs = e.nimhs left join transaksi_ketidakhadiran_mahasiswa_ujian_pdd f on f.stsrc = 'A' and e.priod = f.priod and e.kdsem = f.kdsem and e.nourt_ujian = f.nourt_Ujian and e.nimhs = f.nimhs
B-27 left join msmhs1 g on g.stsrc = 'A' and a.nimhs = g.nimhs left join master_matakuliah h on h.stsrc = 'A' and a.kdmtk = h.kdmtk left join tabel_gugus i on i.stsrc = 'A' and h.kdggs = i.kdggs left join ( select pgaws = kddsn, nm_pgaws = nmdsn from master_dosen where stsrc = 'A' union select nopeg, nmpeg from master_pegawai1 where stsrc = 'A' )j on d.pgaws1 = j.pgaws left join ( select pgaws = kddsn, nm_pgaws = nmdsn from master_dosen where stsrc = 'A' union select nopeg, nmpeg from master_pegawai1 where stsrc = 'A'
B-28 )k on d.pgaws2 = k.pgaws left join tbjur l on l.stsrc = 'A' and g.kdjur = l.kdjur left join tbfak m on m.stsrc = 'A' and g.kdfak = m.kdfak left join tabel_jenis_ujian n on n.stsrc = 'A' and b.kduji = n.kduji left join tbstu o on o.stsrc = 'A' and o.kdstu = g.kdstu where b.priod is not null or e.priod is not null
6. Query view untuk ujian View untuk ujian hanya menggunakan satu stored procedure yaitu view untuk melihat data – data mengenai pengawas dosen / karyawan dan ruang terpakai (spr_dwh_ujian)
8.1. spr_dwh_ujian create proc dbo.spr_dwh_ujian @kdfak char (2) = '', @kdjur char (2) = '' as select Fakultas = a.kdfak + ' - ' + a.nmfak, Jurusan = a.kdjur + ' - ' + a.nmjur , Jenjang_Studi = a.kdstu + ' - ' + a.nmstu
B-29 , a.priod , Semester = case when a.kdsem = '1' then 'Ganjil' when a.kdsem = '2' then 'Genap' when a.kdsem = '3' then 'Pendek' end , Jenis_Ujian = a.kduji + ' - ' + a.nmuji , Gugus_Mtk = a.kdggs + ' - ' + a.nmggs, Matakuliah = a.kdmtk + ' - ' + a.nmmtk , a.tanggal, a.Jamml , jml_pengawas_dosen = coalesce (count (distinct case when left (a.pgaws1, 1) = 'D' then a.pgaws1 else null end), 0) + coalesce (count (distinct case when left (a.pgaws2, 1) = 'D' then a.pgaws2 else null end), 0) , jml_pengawas_karyawan = coalesce (count (distinct case when left (a.pgaws1, 1) not in ('X', 'P', 'G', 'D') and a.pgaws1 <> '' then a.pgaws1 else null end), 0) + coalesce (count (distinct case when left (a.pgaws2, 1) not in ('X', 'P', 'G', 'D') and a.pgaws2 <> '' then a.pgaws2 else null end), 0) , jml_pengawas_outsource = coalesce (count (distinct case when left (a.pgaws1, 1) in ('X', 'P', 'G') and a.pgaws1 <> ''
B-30 then a.pgaws1 else null end), 0) + coalesce (count (distinct case when left (a.pgaws2, 1) in ('X', 'P', 'G') and a.pgaws2 <> '' then a.pgaws2 else null end), 0) , jml_ruang_terpakai = coalesce (count (distinct a.kdrng), 0) , jml_mhs_tdk_berhak_ujian = coalesce (sum (case when status_absensi = 'T' then 1 else 0 end), 0) , jml_mhs_dispensasi = coalesce (sum (case when status_absensi = 'D' then 1 else 0 end), 0) , jml_mhs_curang = coalesce (sum (case when status_absensi = 'C' then 1 else 0 end), 0) , jml_mhs_susulan = coalesce (sum (case when status_absensi = 'U' then 1 else 0 end), 0) , jml_mhs_absen = coalesce (sum (case when status_absensi = 'A' then 1 else 0 end), 0) , jml_mhs = count (distinct a.nimhs) , jml_pengawas = coalesce (count (distinct case when a.pgaws1 <> '' then a.pgaws1 else null end), 0) + coalesce (count (distinct case when a.pgaws2 <> '' then a.pgaws2 else null end), 0) from
B-31 (select distinct kdfak, nmfak, kdjur, nmjur, kdstu, nmstu, priod, kdsem, kduji, nmuji, kdggs, nmggs, kdmtk, nmmtk, tanggal, jamml, pgaws1, pgaws2, nimhs, status_absensi, kdrng from fact_ujian where kdfak = case when @kdfak = '' then kdfak else @kdfak end and kdjur = case when @kdjur = '' then kdjur else @kdjur end and kduji <> 'x' -- bukan sidang skripsi )a group by kdfak, nmfak, kdjur, nmjur, kdstu, nmstu, priod, case when kdsem = '1' then 'Ganjil' when kdsem = '2' then 'Genap' when kdsem = '3' then 'Pendek' end , kduji, nmuji, kdggs, nmggs, kdmtk, nmmtk, tanggal, jamml
7. Query untuk tabel fakta nilai create proc dbo.sp_create_fact_nilai @priod_awal char (4), @kdsem_awal char (1), @priod_akhir char (4), @kdsem_akhir char (1) as insert into fact_nilai
B-32 select --informasi tentang matakuliah dan nilai mahasiswa pada satu semester distinct a.priod, a.kdsem, a.nimhs, a.kdmtk, a.kelas, a.nilak, a.grade --ips ipk pada satu semester ,b.ipsem, b.ipkum, b.sksem, b.skkum --ambil dosen yang ngajar ,kddsn = coalesce (c.kddsn, 'A0001') --info tentang nilai ujian skripsi ,d.ujike, grade_skripsi = d.grade --info tentang nimhs 1 -- laki | 2 --Perempuan ,e.nmmhs, e.thang, e.jnkel, e.kdjur, e.kdfak, e.kdstu --info tentang jurusan ,f.nmjur --info tentang fakultas ,g.nmfak --info tentang dosen yang mengajar matakuliah ,h.nmdsn --info tentang matakuliah ,i.nmmtk, i.kdggs --info tentang gugus ,j.nmggs --info tentang jenjang studi ,k.nmstu from
B-33 (select priod, kdsem, nimhs, kdmtk, kelas, nilak, grade from transaksi_nilai_mahasiswa where stsrc='A' and priod+kdsem >= @priod_awal+@kdsem_awal and priod+kdsem <= @priod_akhir+@kdsem_akhir )a left join transaksi_ips_ipk b on b.stsrc = 'A' and a.priod = b.priod and a.kdsem = b.kdsem and a.nimhs = b.nimhs left join
( select distinct priod, kdsem, kdmtk, kelas, kddsn = coalesce (kddsn,
'A0001') from master_jadwal_Kuliah where coalesce (stsrc, '') <> 'D' and priod+kdsem >= @priod_awal+@kdsem_awal and priod+kdsem <= @priod_akhir+@kdsem_akhir )c on a.priod = c.priod and a.kdsem = c.kdsem and a.kdmtk = c.kdmtk and a.kelas = c.kelas left join ( select d1.priod, d1.kdsem, d6.kdmtk, d5.kelas, d1.nimhs, d3.grade, d1.ujike from ( select r.priod, r.kdsem, r.nimhs, r.nourt_Ujian, r.ujiKe from transaksi_ujian_mahasiswa_skripsi r
B-34 join (select priod, kdsem, nimhs, ujike = max (ujiKe) from transaksi_ujian_mahasiswa_skripsi where stsrc = 'A' group by priod, kdsem, nimhs )s on r.priod = s.priod and r.kdsem = s.kdsem and r.nimhs = s.nimhs and r.ujike = s.ujike where r.priod+r.kdsem >= @priod_awal+@kdsem_awal and r.priod+r.kdsem <= @priod_akhir+@kdsem_akhir ) d1 join ( select priod, kdsem, nimhs, nourt_Ujian, grade from transaksi_nilai_skripsi where stsrc = 'A' and grade is not null and priod+kdsem >= @priod_awal+@kdsem_awal and priod+kdsem <= @priod_akhir+@kdsem_akhir )d3 on d1.priod = d3.priod and d1.kdsem = d3.kdsem and d1.nimhs = d3.nimhs and d1.nourt_Ujian = d3.nourt_Ujian join transaksi_mahasiswa_skripsi d4 on d4.stsrc = 'A' and d3.priod = d4.priod and d3.kdsem = d4.kdsem and d3.nimhs = d4.nimhs join master_mahasiswa_skripsi d5
B-35 on d5.stsrc = 'A' and d4.priod = d5.priod and d4.kdsem = d5.kdsem and d4.no_skripsi = d5.no_skripsi join tabel_kdmtk_topik_skripsi d6 on d6.stsrc = 'A' and d5.nourt_topik = d6.nourut_topik )d on a.priod = d.priod and a.kdsem = d.kdsem and a.kdmtk = d.kdmtk and a.kelas = d.kelas and a.nimhs = d.nimhs left join msmhs1 e on e.stsrc = 'A' and a.nimhs = e.nimhs left join tbjur f on f.stsrc = 'A' and e.kdjur = f.kdjur left join tbfak g on g.stsrc = 'A' and e.kdfak = g.kdfak left join master_dosen h on h.stsrc = 'A' and coalesce (c.kddsn, 'A0001') = h.kddsn left join master_matakuliah i on i.stsrc = 'A' and a.kdmtk = i.kdmtk left join tabel_gugus j on j.stsrc = 'A' and i.kdggs = j.kdggs left join tbstu k on k.stsrc = 'A' and e.kdstu = k.kdstu
B-36 8. Query view untuk nilai View untuk nilai dibagi 3 dengan menggunakan stored procedure yaitu view untuk melihat distribusi grade dan nilai akhir tanpa dimensi dosen (spr_dwh_distribusi_grade), view untuk melihat distribusi grade dan nilai akhir dengan dimensi dosen (spr_dwh_distribusi_grade_dosen), dan view untuk melihat distribusi IP dan lulus sidang (spr_dwh_distribusi_Ip_average).
8.1. spr_dwh_distribusi_grade create proc dbo.spr_dwh_distribusi_grade @kdfak char (2) = '', @kdjur char (2) = '' as select distinct a.priod, Semester = case when a.kdsem = '1' then 'Ganjil' when a.kdsem = '2' then 'Genap' when a.kdsem = '3' then 'Pendek' end , a.thang, Fakultas = ltrim(rtrim(a.kdfak + ' - ' + a.nmfak)), Jurusan = ltrim(rtrim (a.kdjur + ' - ' + a.nmjur)), Jenjang_Studi = ltrim (rtrim(a.kdstu + ' - ' + a.nmstu)), Gugus_Mtk = ltrim (rtrim(a.kdggs + ' - ' + a.nmggs)), Matakuliah = ltrim(rtrim(a.kdmtk + ' - ' + a.nmmtk)), --Dosen = ltrim(rtrim(kddsn + ' - ' + nmdsn)), a.kelas, a.jnkel, Gender = case when jnkel = '1' then 'Laki-laki' else 'Perempuan' end,
B-37 grade_A = sum (case when a.grade = 'A' then 1 else 0 end), grade_B = sum (case when a.grade = 'B' then 1 else 0 end), grade_C = sum (case when a.grade = 'C' then 1 else 0 end), grade_D = sum (case when a.grade = 'D' then 1 else 0 end), grade_L = sum (case when a.grade = 'L' then 1 else 0 end), grade_E = sum (case when a.grade = 'E' then 1 else 0 end), grade_F = sum (case when a.grade = 'F' then 1 else 0 end), grade_G = sum (case when a.grade = 'G' then 1 else 0 end), jml_mhs = coalesce (count (nimhs), 0), jml_nilak = coalesce (sum(nilak), 0) from (select distinct priod, kdsem, thang, kdfak, nmfak, kdjur, nmjur, kdstu, nmstu, kdggs, nmggs, kdmtk, nmmtk, kelas, jnkel, nimhs, grade, nilak, ipsem, ipkum, sksem, skkum from fact_nilai where kdfak = case when @kdfak = '' then kdfak else @kdfak end and kdjur = case when @kdjur = '' then kdjur else @kdjur end )a group by a.priod, case when a.kdsem = '1' then 'Ganjil' when a.kdsem = '2' then 'Genap' when a.kdsem = '3' then 'Pendek' end, a.thang, a.kdfak, a.nmfak, a.kdjur, a.nmjur, a.kdstu, a.nmstu, a.kdggs, a.nmggs, a.kdmtk, a.nmmtk,-- kddsn, nmdsn,
B-38 a.kelas, a.thang, a.jnkel, case when jnkel = '1' then 'Laki-laki' else 'Perempuan' end
8.2. spr_dwh_distribusi_grade_dosen create proc dbo.spr_dwh_distribusi_grade_dosen @kdfak char (2) = '', @kdjur char (2) = '' as select distinct priod, Semester = case when kdsem = '1' then 'Ganjil' when kdsem = '2' then 'Genap' when kdsem = '3' then 'Pendek' end , thang, Fakultas = ltrim(rtrim(kdfak + ' - ' + nmfak)), Jurusan = ltrim(rtrim (kdjur + ' - ' + nmjur)), Jenjang_Studi = ltrim (rtrim(kdstu + ' - ' + nmstu)), Gugus_Mtk = ltrim (rtrim(kdggs + ' - ' + nmggs)), Matakuliah = ltrim(rtrim(kdmtk + ' - ' + nmmtk)), Dosen = ltrim(rtrim(kddsn + ' - ' + nmdsn)), kelas, jnkel, Gender = case when jnkel = '1' then 'Laki-laki' else 'Perempuan' end, grade_A = sum (case when grade = 'A' then 1 else 0 end), grade_B = sum (case when grade = 'B' then 1 else 0 end), grade_C = sum (case when grade = 'C' then 1 else 0 end), grade_D = sum (case when grade = 'D' then 1 else 0 end),
B-39 grade_L = sum (case when grade = 'L' then 1 else 0 end), grade_E = sum (case when grade = 'E' then 1 else 0 end), grade_F = sum (case when grade = 'F' then 1 else 0 end), grade_G = sum (case when grade = 'G' then 1 else 0 end), jml_mhs = coalesce (count (nimhs), 0), jml_nilak = coalesce (sum(nilak), 0) from fact_nilai where kdfak = case when @kdfak = '' then kdfak else @kdfak end and kdjur = case when @kdjur = '' then kdjur else @kdjur end group by priod, case when kdsem = '1' then 'Ganjil' when kdsem = '2' then 'Genap' when kdsem = '3' then 'Pendek' end, thang, kdfak, nmfak, kdjur, nmjur, kdstu, nmstu, kdggs, nmggs, kdmtk, nmmtk, kddsn, nmdsn, kelas, thang, jnkel, case when jnkel = '1' then 'Laki-laki' else 'Perempuan' end
8.3. spr_dwh_distribusi_Ip_average create proc dbo.spr_dwh_distribusi_Ip_average @kdfak char (2) ='', @kdjur char (2)='' as select Fakultas = a.kdfak + ' - ' + a.nmfak , Jurusan = a.kdjur + ' - ' + a.nmjur,
B-40 Jenjang_studi = a.kdstu + ' - ' + a.nmstu, a.priod, Semester = case when a.kdsem = '1' then 'Ganjil' when a.kdsem = '2' then 'Genap' when a.kdsem = '3' then 'Pendek' end, a.thang, jml_mhs = coalesce(count (a.nimhs), 0), jml_ips = coalesce (sum(a.ipsem), 0), jml_ipk = coalesce (sum(a.ipkum), 0), jml_sksem= coalesce (sum(a.sksem), 0), jml_skkum = coalesce (sum(a.skkum), 0), ips_min = coalesce (min(a.ipsem), 0), ips_max = coalesce (max(a.ipsem),0), ipk_min = coalesce (min(a.ipkum), 0), ipk_max = coalesce (max(a.ipkum),0),
jml_1_ips = coalesce (sum (case when a.ipsem = 0 then 1 else 0 end), 0), jml_2_ips = coalesce (sum (case when a.ipsem >= 0.01 and a.ipsem <= 0.49 then 1 else 0 end), 0), jml_3_ips = coalesce (sum (case when a.ipsem >= 0.50 and a.ipsem <= 0.99 then 1 else 0 end), 0), jml_4_ips = coalesce (sum (case when a.ipsem >= 1.00 and a.ipsem <= 1.49 then 1 else 0 end), 0), jml_5_ips = coalesce (sum (case when a.ipsem >= 1.50 and a.ipsem <= 1.74 then 1 else 0 end), 0),
B-41 jml_6_ips = coalesce (sum (case when a.ipsem >= 1.75 and a.ipsem <= 1.99 then 1 else 0 end), 0), jml_7_ips = coalesce (sum (case when a.ipsem >= 2.00 and a.ipsem <= 2.49 then 1 else 0 end), 0), jml_8_ips = coalesce (sum (case when a.ipsem >= 2.50 and a.ipsem <= 2.99 then 1 else 0 end), 0), jml_9_ips = coalesce (sum (case when a.ipsem >= 3.00 and a.ipsem <= 3.49 then 1 else 0 end), 0), jml_10_ips = coalesce (sum (case when a.ipsem >= 3.50 and a.ipsem <= 4.00 then 1 else 0 end), 0),
jml_1_ipk = coalesce (sum (case when a.ipkum = 0 then 1 else 0 end), 0), jml_2_ipk = coalesce (sum (case when a.ipkum >= 0.01 and a.ipkum <= 0.49 then 1 else 0 end), 0), jml_3_ipk = coalesce (sum (case when a.ipkum >= 0.50 and a.ipkum <= 0.99 then 1 else 0 end), 0), jml_4_ipk = coalesce (sum (case when a.ipkum >= 1.00 and a.ipkum <= 1.49 then 1 else 0 end), 0), jml_5_ipk = coalesce (sum (case when a.ipkum >= 1.50 and a.ipkum <= 1.74 then 1 else 0 end), 0), jml_6_ipk = coalesce (sum (case when a.ipkum >= 1.75 and a.ipkum <= 1.99 then 1 else 0 end), 0), jml_7_ipk = coalesce (sum (case when a.ipkum >= 2.00 and a.ipkum <= 2.49 then 1 else 0 end), 0),
B-42 jml_8_ipk = coalesce (sum (case when a.ipkum >= 2.50 and a.ipkum <= 2.99 then 1 else 0 end), 0), jml_9_ipk = coalesce (sum (case when a.ipkum >= 3.00 and a.ipkum <= 3.49 then 1 else 0 end), 0), jml_10_ipk = coalesce (sum (case when a.ipkum >= 3.50 and a.ipkum <= 4.00 then 1 else 0 end), 0),
jml_1_skkum = coalesce (sum (case when a.skkum = 0 then 1 else 0 end), 0), jml_2_skkum = coalesce (sum (case when a.skkum >= 1 and a.skkum <= 14 then 1 else 0 end), 0), jml_3_skkum = coalesce (sum (case when a.skkum >= 15 and a.skkum <= 29 then 1 else 0 end), 0), jml_4_skkum = coalesce (sum (case when a.skkum >= 30 and a.skkum <= 44 then 1 else 0 end), 0), jml_5_skkum = coalesce (sum (case when a.skkum >= 45 and a.skkum <= 59 then 1 else 0 end), 0), jml_6_skkum = coalesce (sum (case when a.skkum >= 60 and a.skkum <= 74 then 1 else 0 end), 0), jml_7_skkum = coalesce (sum (case when a.skkum >= 75 and a.skkum <= 99 then 1 else 0 end), 0), jml_8_skkum = coalesce (sum (case when a.skkum >= 100 and a.skkum <= 114 then 1 else 0 end), 0), jml_9_skkum = coalesce (sum (case when a.skkum >= 115 and a.skkum <= 129 then 1 else 0 end), 0),
B-43 jml_10_skkum = coalesce (sum (case when a.skkum >= 130 and a.skkum <= 144 then 1 else 0 end), 0), jml_11_skkum = coalesce (sum (case when a.skkum >= 145 then 1 else 0 end), 0),
uji1 = sum (case when b.ujike = '1' then 1 else 0 end), uji2 = sum (case when b.ujike = '2' then 1 else 0 end), uji3 = sum (case when b.ujike = '3' then 1 else 0 end) from (select distinct kdfak, nmfak, kdjur, nmjur, kdstu, nmstu, priod, kdsem, thang, nimhs, ipsem, ipkum, sksem, skkum from fact_nilai where kdfak = case when @kdfak = '' then kdfak else @kdfak end and kdjur = case when @kdjur = '' then kdjur else @kdjur end )a left join (select distinct priod, kdsem, thang, nimhs,ujike from fact_nilai where ujike is not null )b on a.priod = b.priod and a.kdsem = b.kdsem and a.thang = b.thang and a.nimhs = b.nimhs group by a.kdfak + ' - ' + a.nmfak , a.kdjur + ' - ' + a.nmjur, a.kdstu + ' - ' + a.nmstu, a.priod, case when a.kdsem = '1' then 'Ganjil' when a.kdsem = '2' then 'Genap' when a.kdsem = '3' then 'Pendek' end, a.thang
