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..

Sql DI 301 dan Luas

select d301.d301seq No_d301,
d301.d301yea Tahun301,
inde.masterpk1 desa,
gssu.gssuare Luas,
akta.aktavalue NilaiJualBeli
from d301,inde,akta,gssu,paht
where d301.indecod=akta.indecod
and d301.d301dat>="01/01/2007"
and d301.d301dat<="30/12/2007"
and d301.tindcod="31"
and d301.indecod=inde.indecod
and paht.desaco8 = inde.masterpk1
and paht.thakcod = inde.masterpk2
and paht.htanseq = inde.masterpk3
and gssu.desaco8 = paht.desaco8
and gssu.gssuyea = paht.gssuyea
and gssu.gssunum = paht.gssunum;

Baca Selengkapnya..

Sql Jumlah Buku Tanah Ada NIB

select count(*) from gspa where
exists(select * from paht where
paht.desaco8=gspa.desaco8 and
paht.gssuyea=gspa.gssuyea and
paht.gssunum=gspa.gssunum);

Baca Selengkapnya..

Sql Jumlah Buku Tanah Ada NIB

select count(*) from gspa where
exists(select * from paht where
paht.desaco8=gspa.desaco8 and
paht.gssuyea=gspa.gssuyea and
paht.gssunum=gspa.gssunum);

Baca Selengkapnya..