SQL Laporan DE

Buat Kang Apek 'Ciamis' mudah-mudahan pulang proyek jadi beunghar ... Amien
Tong pho ka babaturan sa Karawang ...
Jo


-- rekap entri HAT per desa berdasarkan tanggal
select d.desaco8, k.kecanama, d.desanama, rh.tipehakid, count(rh.userentri)
from reghat rh, desa d, kecamatan k
where rh.desaid = d.desaid
and d.kecamatanid = k.kecamatanid
and flagdb = 'Y'
and rh.tanggalentri between to_date('01/17/2006')
and to_date('01/30/2007')
group by d.desaco8, k.kecanama, d.desanama, rh.tipehakid
order by 1,4;

-- rekap entri HAT per petugas berdasarkan tanggal
select rh.userentri, count(rh.userentri)
from reghat rh, desa d, kecamatan k
where rh.desaid = d.desaid
and d.kecamatanid = k.kecamatanid
and flagdb = 'Y'
and rh.tanggalentri between to_date('01/17/2006')
and to_date('01/30/2007')
group by rh.userentri
order by 1;


-- Kalo Pengen sama nama petugas entri tapi kok jumlahnya ngak sama ya ???
-- rekap entri HAT per petugas berdasarkan tanggal link berdasarkan employee
select rh.userentri, e.nama, count(rh.userentri)
from reghat rh, desa d, kecamatan k, employee e
where rh.desaid = d.desaid
and d.kecamatanid = k.kecamatanid
and e.employeenip = rh.userentri
and flagdb = 'Y'
and rh.tanggalentri between to_date('01/17/2006')
and to_date('01/30/2007')
group by rh.userentri, e.nama
order by 1;

-- rekap entri HAT per petugas berdasarkan tanggal link berdasarkan profile
select rh.userentri, p.keterangan, count(rh.userentri)
from reghat rh, desa d, kecamatan k, profile p
where rh.desaid = d.desaid
and d.kecamatanid = k.kecamatanid
and p.profileid = rh.userentri
and flagdb = 'Y'
and rh.tanggalentri between to_date('01/17/2006')
and to_date('01/30/2007')
group by rh.userentri, p.keterangan
order by 1;

-- rekap entri GD per petugas berdasarkan tanggal
select rg.gduserentri, count(rg.gduserentri)
from reggd rg, desa d, kecamatan k
where rg.desaid = d.desaid
and d.kecamatanid = k.kecamatanid
and rg.flagdb = 'Y'
and rg.gdtanggalentri between to_date('01/17/2006')
and to_date('01/30/2007')
group by rg.gduserentri;

-- rekap entri GD per desa berdasarkan tanggal
select d.desaco8, k.kecanama, d.desanama, 'GD', count(rg.gduserentri)
from reggd rg, desa d, kecamatan k
where rg.desaid = d.desaid
and d.kecamatanid = k.kecamatanid
and rg.flagdb = 'Y'
and rg.gdtanggalentri between to_date('01/17/2006')
and to_date('01/30/2007')
group by d.desaco8, k.kecanama, d.desanama
order by 1,4;

-- rekap entri SU per petugas berdasarkan tanggal
select rs.suuserentri, count(rs.suuserentri)
from regsu rs, desa d, kecamatan k
where rs.desaid = d.desaid
and d.kecamatanid = k.kecamatanid
and rs.flagdb = 'Y'
and rs.sutanggalentri between to_date('01/17/2006')
and to_date('01/30/2007')
group by rs.suuserentri;

-- rekap entri SU per desa berdasarkan tanggal
select d.desaco8, k.kecanama, d.desanama, rs.sutahun, rs.sutipe, count(rs.suuserentri)
from regsu rs, desa d, kecamatan k
where rs.desaid = d.desaid
and d.kecamatanid = k.kecamatanid
and rs.flagdb = 'Y'
and rs.sutanggalentri between to_date('01/17/2006')
and to_date('01/30/2007')
group by d.desaco8, k.kecanama, d.desanama, rs.sutahun, rs.sutipe
order by 1,4,5;


-- rekap entri HT per petugas berdasarkan tanggal
select rh.htuserentri, count(rh.htuserentri)
from reght rh, desa d, kecamatan k
where rh.flagdb = 'Y'
and rh.httanggalentri between to_date('01/17/2006')
and to_date('01/30/2007')
group by rh.htuserentri
order by 1;

-- rekap entri HT per tahun berdasarkan tanggal
select rh.httahun, count(rh.htuserentri)
from reght rh
where rh.flagdb = 'Y'
and rh.httanggalentri between to_date('01/17/2006')
and to_date('01/30/2007')
group by rh.httahun
order by 1;

-- rekap entri HMS per petugas berdasarkan tanggal
select rh.hmsuserentri, count(rh.hmsuserentri)
from reghms rh, desa d, kecamatan k
where rh.desaid = d.desaid
and d.kecamatanid = k.kecamatanid
and rh.flagdb = 'Y'
and rh.hmstanggalentri between to_date('01/17/2006')
and to_date('01/30/2007')
group by rh.hmsuserentri
order by 1;

-- rekap entri HMS per desa berdasarkan tanggal
select d.desaco8, k.kecanama, d.desanama, 'HMS', count(rh.hmsuserentri)
from reghms rh, desa d, kecamatan k
where rh.desaid = d.desaid
and d.kecamatanid = k.kecamatanid
and flagdb = 'Y'
and rh.hmstanggalentri between to_date('01/17/2006')
and to_date('01/30/2007')
group by d.desaco8, k.kecanama, d.desanama
order by 1;

Baca Selengkapnya..