SQL JUMLAH TOTAL HAK PER TAHUN DAN PERBULAN

PERBULAN YG GS
------------------------------------------------------
select gsht.thakcod JENIS_HAK,
thak.thakdes JENIS_HAK,
count(*) JUMLAH_HAK,
sum(gssuold.gssuare) JUMLAH_LUAS
from gsht,gssuold,htan,thak
where month(htan.htanboodat)="02"
and year(htan.htanboodat)=2006
and thak.thakcod=gsht.thakcod
and gsht.desaco8=htan.desaco8
and gsht.thakcod=htan.thakcod
and gsht.htanseq=htan.htanseq
and gssuold.desaco8=gsht.desaco8
and gssuold.gssuyea=gsht.gssuyea
and gssuold.gssunum=gsht.gssunum
group by 1,2;

PERBULAN YG SU
--------------------------------------------------------
select paht.thakcod JENIS_HAK,
thak.thakdes JENIS_HAK,
count(*) JUMLAH_HAK,
sum(gssu.gssuare) JUMLAH_LUAS
from paht,gssu,htan,thak
where month(htan.htanboodat)="02"
and thak.thakcod=paht.thakcod
and year(htan.htanboodat)=2006
and paht.desaco8=htan.desaco8
and paht.thakcod=htan.thakcod
and paht.htanseq=htan.htanseq
and gssu.desaco8=paht.desaco8
and gssu.gssuyea=paht.gssuyea
and gssu.gssunum=paht.gssunum
group by 1,2;

PERTAHUN YG GS
--------------------------------------------------------
select gsht.thakcod KODE_HAK,
thak.thakdes JENIS_HAK,
count(*) JUMLAH_HAK,
sum(gssuold.gssuare) JUMLAH_LUAS
from gsht,gssuold,htan,thak
where year(htan.htanboodat)=2006
and thak.thakcod=gsht.thakcod
and gsht.desaco8=htan.desaco8
and gsht.thakcod=htan.thakcod
and gsht.htanseq=htan.htanseq
and gssuold.desaco8=gsht.desaco8
and gssuold.gssuyea=gsht.gssuyea
and gssuold.gssunum=gsht.gssunum
group by 1,2;

PERTAHUN YG SU
--------------------------------------------------------
select paht.thakcod KODE_HAK,
thak.thakdes JENIS_HAK,
count(*) JUMLAH_HAK,
sum(gssu.gssuare) JUMLAH_LUAS
from paht,gssu,htan,thak
where year(htan.htanboodat)=2006
and thak.thakcod=paht.thakcod
and paht.desaco8=htan.desaco8
and paht.thakcod=htan.thakcod
and paht.htanseq=htan.htanseq
and gssu.desaco8=paht.desaco8
and gssu.gssuyea=paht.gssuyea
and gssu.gssunum=paht.gssunum
group by 1,2;

Baca Selengkapnya..

Cari 302 sudah 307

Cari 302 sudah 307.
-----------------------------------------
select expe.expecod,
expe.expeyea,
d302.d302seq,
d302.d302yea,
d307.d307seq,
d307.d307yea
from d302,d307,expe
where d302.d302yea=2006
and d302.expeid=expe.expeid
and d307.indecod=d302.indecod;

302 belum 307 (COBA INI MAS HINU)
----------------------------------------------
select distinct
expe.expecod,
expe.expeyea,
d302.d302seq,
d302.d302yea
from d302,expe
where d302.d302yea=2006
and d302.expeid=expe.expeid
and d302.indecod not in
(select indecod from d307);

Baca Selengkapnya..

JUMLAH BPHTB PERTAHUN dan PERBULAN

JUMLAH NILAI BPHTB PERTAHUN
----------------------------------------------------
select year(aktadat),
sum(aktaceimgg)
from akta
where aktaceimgg is not null
and aktaceimgg>0
and indecod<>0
and indecod>0
group by 1;

JUMLAH NILAI BPHTB PERBULAN
----------------------------------------------------
select month(aktadat),
sum(aktaceimgg)
from akta
where aktaceimgg is not null
and aktaceimgg>0
and indecod<>0
and indecod>0
and year(aktadat)=2005
group by 1;

JUMLAH NILAI BPHTB DAN TOTAL BIDANG
----------------------------------------------------
select year(akta.aktadat) PERTAHUN_AKTA,
sum(akta.aktaceimgg) JUMLAH_BPHTB,
count(distinct inde.masterpk3) JUMLAH_BIDANG
from akta,inde
where akta.aktaceimgg is not null
and akta.aktaceimgg>0
and akta.indecod<>0
and akta.indecod>0
and inde.tindcod<>"06"
and inde.indecod=akta.indecod
group by 1;

-----------------------------------------------------

ANALISA
-----------------------
select akta.aktadat,
akta.aktaceimgg,
akta.aktavalue,
inde.expeid,
inde.tindcod,
expe.expecod,
expe.expeyea,
expe.stexcod
from akta,inde,expe
where akta.aktaceimgg is not null
and akta.aktaceimgg>0
and akta.indecod<>0
and akta.indecod>0
and expe.stexcod="OP"
and inde.tindcod<>"06"
and inde.indecod=akta.indecod
and expe.expeid=inde.expeid;

Baca Selengkapnya..

SQL JML HAK DAN LUAS per periode

1)--------------------------------------------------
unload to "c:\tmp\jml_hak1.txt"
select gsht.desaco8,
gsht.thakcod,
htan.htanflgact STATUS_AKTIF,
count(distinct gsht.htanseq) JUMLAH_HAK,
sum(gssuold.gssuare) JUMLAH_LUAS
from gsht,gssuold,htan
where gssuold.desaco8=gsht.desaco8
{and htan.htanflgact="Y"}
and gssuold.gssuyea=gsht.gssuyea
and gssuold.gssunum=gsht.gssunum
and gsht.desaco8=htan.desaco8
and gsht.htanseq=htan.htanseq
and htan.htanboodat>="01/08/2006" and htanboodat<="28/02/2007"
group by gsht.desaco8,
gsht.thakcod,htan.htanflgact;
2)--------------------------------------------------
unload to "c:\tmp\jml_hak2.txt"
select paht.desaco8,
paht.thakcod,
htan.htanflgact STATUS_AKTIF,
count(distinct paht.htanseq) JUMLAH_HAK,
sum(gssu.gssuare) JUMLAH_LUAS
from paht,gssu,htan
where gssu.desaco8=paht.desaco8
{and htan.htanflgact="Y"}
and gssu.gssuyea=paht.gssuyea
and gssu.gssunum=paht.gssunum
and paht.desaco8=htan.desaco8
and paht.htanseq=htan.htanseq
and htan.htanboodat>="01/08/2006" and htanboodat<="28/02/2007"
group by paht.desaco8,
paht.thakcod,htan.htanflgact;
3)--------------------------------------------------
select desaco8,
thakcod,
htanflgact,
count(distinct htanseq)
from htan
where htanboodat>="01/08/2006" and htanboodat<="28/02/2007"
{and htan.htanflgact="Y"}
group by desaco8,thakcod,htanflgact;
----------------------------------------------------
KETERANGAN :
Kalau BT yang diambil ingin yang aktif saja:
- Buka tanda remark { } di and htan.htanflgact="Y" untuk sql 1,2,3

Buka dg MSExcel, SQL1 dan SQL2 di gabung
SQL NO 1 JUMLAH HAK YANG SURAT UKURNYA GS
SQL NO 2 JUMLAH HAK YANG SURAT UKURNYA SU
SQL NO 3 JUMLAH HAK KESELURUHAN YG TERBIT
JIKA ADA SELISIH ANTARA SQL 3 DAN SQL1-2 DIKARENAKAN
1. ADA BUKU TANAH YG SUDAH ADA NOMORNYA TAPI BELUM DIENTRY
2. ADA BUKU TANAH YG SURAT UKURNYA BUKAN GS/SU TAPI SUS ATAU PLL

Baca Selengkapnya..

SQL JUMLAH TOTAL HAK PER TAHUN DAN PERBULAN PERKEGIATAN

'untuk berkas yang menggunakan gs

select gsht.thakcod KODE_HAK,
thak.thakdes JENIS_HAK,
inde.tindcod KODE_KEGIATAN,
tind.tinddes JENIS_KEGIATAN,
count(distinct gsht.htanseq) JUMLAH_HAK,
sum(gssuold.gssuare) JUMLAH_LUAS
from gsht,gssuold,inde,thak,tind
where month(inde.indedocdat)="02"
and year(inde.indedocdat)=2006
and inde.tindcod in ("11","27") ---> tindcod diganti berdasarkan kegiatannya
and inde.tdoccod="B1"
and inde.indetyp="R"
and thak.thakcod=gsht.thakcod
and gsht.desaco8=inde.masterpk1
and gsht.thakcod=inde.masterpk2
and gsht.htanseq=inde.masterpk3[1,8]
and gssuold.desaco8=gsht.desaco8
and gssuold.gssuyea=gsht.gssuyea
and gssuold.gssunum=gsht.gssunum
group by 1,2,3,4;



'untuk berkas yang menggunakan SU

select paht.thakcod KODE_HAK,
thak.thakdes JENIS_HAK,
inde.tindcod KODE_KEGIATAN,
tind.tinddes JENIS_KEGIATAN,
count(distinct paht.htanseq) JUMLAH_HAK,
sum(gssu.gssuare) JUMLAH_LUAS
from paht,gssu,inde,thak,tind
where month(inde.indedocdat)="02"
and year(inde.indedocdat)=2006
and inde.tindcod in ("11","27") ---> tindcod diganti berdasarkan kegiatannya
and inde.tdoccod="B1"
and inde.indetyp="R"
and thak.thakcod=gsht.thakcod
and paht.desaco8=inde.masterpk1
and paht.thakcod=inde.masterpk2
and paht.htanseq=inde.masterpk3[1,8]
and gssu.desaco8=paht.desaco8
and gssu.gssuyea=paht.gssuyea
and gssu.gssunum=paht.gssunum
group by 1,2,3,4;

Baca Selengkapnya..

Sql Berkas Uang DRK Nol

select expe.expecod,
expe.expeyea,
exco.excoamo,
puex.puexdsa
from expe,inde,exco,puex
where expe.expeyea=2004
{and inde.indetyp="G"}
and inde.expeid=expe.expeid
and exco.expeid=expe.expeid
and puex.expeid=expe.expeid
and exco.excoamo=0
group by expe.expecod,expe.expeyea,exco.excoamo,puex.puexdsa
order by 4;

Baca Selengkapnya..