the longest sql queries I’ve ever made

here they are :

nb: change the * symbol with com word

	### the longest sql queries I've ever made in 2016
	### QUERY 1

	select __is_kegiatan.description as nmitem, table_data.*
	from __is_kegiatan
	join
	(
	select table_percentage.kdgiat, table_percentage.kdoutput, table_percentage.kdsoutput,
	((percentage_kdsoutput_by_kdoutput*lembaga_keuangan_jumlah_perkdoutput)/100) as jumlah_pagu,
	((percentage_kdsoutput_by_kdoutput*lembaga_keuangan_realisasi_perkdoutput)/100) as jumlah_realisasi
	from (
	select table_detail.kdgiat, table_detail.kdoutput, table_detail.kdsoutput,
	((sum_perkdsoutput/sum_perkdoutput)*100) as percentage_kdsoutput_by_kdoutput
	from (
	SELECT __is_items.kdgiat, __is_items.kdoutput, __is_items.kdsoutput, sum(jumlah) as sum_perkdsoutput
	FROM __is_items
	join subkomponens
	on __is_items.thang = subkomponens.thang
	and __is_items.kdgiat = subkomponens.kdgiat
	and __is_items.kdoutput = subkomponens.kdoutput
	and __is_items.kdsoutput = subkomponens.kdsoutput
	and __is_items.kdsatker = subkomponens.kdsatker
	and __is_items.kddekon = subkomponens.kddekon
	and __is_items.kdlokasi = subkomponens.kdlokasi
	where __is_items.thang='".$thang."' and subkomponens.thang='".$thang."'
	and __is_items.kdsatker='".$kdsatker."' and __is_items.kdlokasi='".$user->kdlokasi."'
	and subkomponens.kdsatker='".$kdsatker."' and subkomponens.kdlokasi='".$user->kdlokasi."'
	".$q_kab."
	group by __is_items.kdgiat, __is_items.kdoutput, __is_items.kdsoutput
	) as table_detail
	join
	(
	SELECT __is_items.kdgiat, __is_items.kdoutput, sum(jumlah) as sum_perkdoutput
	FROM __is_items
	where __is_items.thang='".$thang."'
	group by __is_items.kdgiat, __is_items.kdoutput
	) as table_header
	on
	table_detail.kdgiat=table_header.kdgiat and table_detail.kdoutput=table_header.kdoutput
	) as table_percentage
	join
	(
	SELECT keuangan_lembaga_keuangan.kdgiat, keuangan_lembaga_keuangan.kdoutput, sum(jumlah) as lembaga_keuangan_jumlah_perkdoutput,
	sum(realisasi) as lembaga_keuangan_realisasi_perkdoutput
	FROM keuangan_lembaga_keuangan
	join subkomponens
	on keuangan_lembaga_keuangan.thang = subkomponens.thang
	and keuangan_lembaga_keuangan.kdgiat = subkomponens.kdgiat
	and keuangan_lembaga_keuangan.kdoutput = subkomponens.kdoutput
	and keuangan_lembaga_keuangan.kdsatker = subkomponens.kdsatker
	where lembaga_keuangan_realisasi_date = '".$this->__d."'
	and keuangan_lembaga_keuangan.thang='".$thang."' and subkomponens.thang='".$thang."'
	and keuangan_lembaga_keuangan.kdsatker='".$kdsatker."' and subkomponens.kdsatker='".$kdsatker."'
	and subkomponens.kdlokasi='".$user->kdlokasi."'
	".$q_kab."
	group by keuangan_lembaga_keuangan.kdgiat, keuangan_lembaga_keuangan.kdoutput
	) as table_realisasi
	on
	table_percentage.kdgiat=table_realisasi.kdgiat
	and table_percentage.kdoutput=table_realisasi.kdoutput
	) as table_data
	on __is_kegiatan.kdgiat=table_data.kdgiat
	and __is_kegiatan.kdoutput=table_data.kdoutput
	and __is_kegiatan.kdsoutput=table_data.kdsoutput
	where __is_kegiatan.is_kegiatan_utama='yes';

view raw

longest sql queries #1.txt

hosted with ❤ by GitHub

	### the longest sql queries I've ever made in 2016
	### QUERY 2

	select table_keuangan.*, dzohir_target,dzohir_realisasi,dzohir_percentage,dzohir_satkeg
	from
	(
	select __is_kegiatan.description as nmitem, table_data.*,
	((anggaran_realisasi/anggaran_pagu)*100) as anggaran_percentage
	from __is_kegiatan
	join (
	select table_percentage.kdgiat, table_percentage.kdoutput, table_percentage.kdsoutput,
	((percentage_kdsoutput_by_kdoutput*lembaga_keuangan_jumlah_perkdoutput)/100) as anggaran_pagu,
	((percentage_kdsoutput_by_kdoutput*lembaga_keuangan_realisasi_perkdoutput)/100) as anggaran_realisasi
	from (
	select table_detail.kdgiat, table_detail.kdoutput, table_detail.kdsoutput,
	((sum_perkdsoutput/sum_perkdoutput)*100) as percentage_kdsoutput_by_kdoutput
	from (
	SELECT __is_items.kdgiat, __is_items.kdoutput, __is_items.kdsoutput, sum(jumlah) as sum_perkdsoutput
	FROM __is_items
	join subkomponens
	on __is_items.thang = subkomponens.thang
	and __is_items.kdgiat = subkomponens.kdgiat
	and __is_items.kdoutput = subkomponens.kdoutput
	and __is_items.kdsoutput = subkomponens.kdsoutput
	and __is_items.kdsatker = subkomponens.kdsatker
	and __is_items.kddekon = subkomponens.kddekon
	and __is_items.kdlokasi = subkomponens.kdlokasi
	where __is_items.thang='".$thang."' and subkomponens.thang='".$thang."'
	and __is_items.kdsatker='".$kdsatker."' and __is_items.kdlokasi='".$user->kdlokasi."'
	and subkomponens.kdsatker='".$kdsatker."' and subkomponens.kdlokasi='".$user->kdlokasi."'
	".$q_kab_on_subkomponen."
	group by __is_items.kdgiat, __is_items.kdoutput, __is_items.kdsoutput
	) as table_detail
	join (
	SELECT __is_items.kdgiat, __is_items.kdoutput, sum(jumlah) as sum_perkdoutput
	FROM
	__is_items
	join subkomponens
	on __is_items.thang = subkomponens.thang
	and __is_items.kdgiat = subkomponens.kdgiat
	and __is_items.kdoutput = subkomponens.kdoutput
	and __is_items.kdsoutput = subkomponens.kdsoutput
	and __is_items.kdsatker = subkomponens.kdsatker
	and __is_items.kddekon = subkomponens.kddekon
	and __is_items.kdlokasi = subkomponens.kdlokasi
	where __is_items.thang='".$thang."' and subkomponens.thang='".$thang."'
	and __is_items.kdsatker='".$kdsatker."' and __is_items.kdlokasi='".$user->kdlokasi."'
	and subkomponens.kdsatker='".$kdsatker."' and subkomponens.kdlokasi='".$user->kdlokasi."'
	".$q_kab_on_subkomponen."
	group by __is_items.kdgiat, __is_items.kdoutput
	) as table_header
	on table_detail.kdgiat=table_header.kdgiat and table_detail.kdoutput=table_header.kdoutput
	) as table_percentage
	join
	(
	SELECT keuangan_lembaga_keuangan.kdgiat, keuangan_lembaga_keuangan.kdoutput,
	sum(jumlah) as lembaga_keuangan_jumlah_perkdoutput, sum(realisasi) as lembaga_keuangan_realisasi_perkdoutput
	FROM keuangan_lembaga_keuangan
	join subkomponens
	on keuangan_lembaga_keuangan.thang = subkomponens.thang
	and keuangan_lembaga_keuangan.kdgiat = subkomponens.kdgiat
	and keuangan_lembaga_keuangan.kdoutput = subkomponens.kdoutput
	and keuangan_lembaga_keuangan.kdsatker = subkomponens.kdsatker
	where lembaga_keuangan_realisasi_date = '".$this->__d."'
	and keuangan_lembaga_keuangan.thang='".$thang."' and subkomponens.thang='".$thang."'
	and keuangan_lembaga_keuangan.kdsatker='".$kdsatker."' and subkomponens.kdsatker='229108'
	and subkomponens.kdlokasi='".$user->kdlokasi."'
	".$q_kab_on_subkomponen."
	group by keuangan_lembaga_keuangan.kdgiat, keuangan_lembaga_keuangan.kdoutput
	) as table_realisasi
	on table_percentage.kdgiat=table_realisasi.kdgiat
	and table_percentage.kdoutput=table_realisasi.kdoutput
	) as table_data
	on __is_kegiatan.kdgiat=table_data.kdgiat
	and __is_kegiatan.kdoutput=table_data.kdoutput and __is_kegiatan.kdsoutput=table_data.kdsoutput
	where __is_kegiatan.is_kegiatan_utama='yes'
	) as table_keuangan
	left join
	(
	select description as nmitem, table_data.kdgiat, table_data.kdoutput, table_data.kdsoutput,
	table_data.dzohir_target, table_data.dzohir_realisasi, table_data.dzohir_percentage, table_data.dzohir_satkeg
	from (
	SELECT __is_kegiatan.kdgiat, __is_kegiatan.kdoutput, __is_kegiatan.kdsoutput,
	description, acronym, satkeg as dzohir_satkeg,
	sum(target) as dzohir_target, avg(realisasi) as dzohir_realisasi, avg(realisasi) as dzohir_percentage
	FROM __is_kegiatan
	join dzohirs on __is_kegiatan.kdgiat=dzohirs.kdgiat and __is_kegiatan.kdoutput=dzohirs.kdoutput
	and __is_kegiatan.kdsoutput=dzohirs.kdsoutput
	join dzohir_kelompok_penerima on dzohir_kelompok_penerima.dzohir_id=dzohirs.id
	WHERE is_kegiatan_utama = 'yes' and dzohirs.thang='".$thang."'
	and dzohirs.kdsatker='".$kdsatker."'
	and dzohirs.kdlokasi='".$user->kdlokasi."'
	".$q_kab_on_dzohir."
	group by __is_kegiatan.kdgiat, __is_kegiatan.kdoutput, __is_kegiatan.kdsoutput, description, acronym, satkeg
	) as table_data
	) as table_dzohir
	on table_dzohir.kdgiat=table_keuangan.kdgiat
	and table_dzohir.kdoutput=table_keuangan.kdoutput
	and table_dzohir.kdsoutput=table_keuangan.kdsoutput;

view raw

longest sql queries #2.txt

hosted with ❤ by GitHub

	### the longest sql queries I've ever made in 2016
	### QUERY 3

	select nmlokasi, anggaran_pagu, anggaran_realisasi,
	((anggaran_realisasi/anggaran_pagu)*100) as anggaran_percentage,
	table_dzohir.dzohir_target, table_dzohir.dzohir_realisasi, table_dzohir.dzohir_percentage
	from
	lokasis
	left join
	(
	select table_percentage.kdsatker, table_percentage.kdlokasi, table_percentage.kdgiat,
	table_percentage.kdoutput, table_percentage.kdsoutput,
	((percentage_kdsoutput_by_kdoutput*lembaga_keuangan_jumlah_perkdoutput)/100) as anggaran_pagu,
	((percentage_kdsoutput_by_kdoutput*lembaga_keuangan_realisasi_perkdoutput)/100) as anggaran_realisasi
	from
	(
	select table_detail.kdsatker, table_detail.kdlokasi, table_detail.kdgiat, table_detail.kdoutput,
	table_detail.kdsoutput,
	((sum_perkdsoutput/sum_perkdoutput)*100) as percentage_kdsoutput_by_kdoutput
	from (
	SELECT __is_items.kdsatker, __is_items.kdlokasi, __is_items.kdgiat, __is_items.kdoutput, __is_items.kdsoutput,
	sum(jumlah) as sum_perkdsoutput
	FROM __is_items
	where __is_items.thang='".$thang."' and __is_items.kdgiat='".$kdgiat."'
	and __is_items.kdoutput='".$kdoutput."' and __is_items.kdsoutput='".$kdsoutput."'
	group by __is_items.kdsatker, __is_items.kdlokasi, __is_items.kdgiat, __is_items.kdoutput, __is_items.kdsoutput
	) as table_detail
	join
	(
	SELECT __is_items.kdsatker, __is_items.kdlokasi, __is_items.kdgiat, __is_items.kdoutput, sum(jumlah) as sum_perkdoutput
	FROM __is_items
	where __is_items.thang='".$thang."' and __is_items.kdgiat='".$kdgiat."' and __is_items.kdoutput='".$kdoutput."'
	group by __is_items.kdsatker, __is_items.kdlokasi, __is_items.kdgiat, __is_items.kdoutput
	) as table_header
	on
	table_detail.kdsatker=table_header.kdsatker and
	table_detail.kdlokasi=table_header.kdlokasi and
	table_detail.kdgiat=table_header.kdgiat and
	table_detail.kdoutput=table_header.kdoutput
	) as table_percentage
	join
	(
	SELECT keuangan_lembaga_keuangan.kdsatker, keuangan_lembaga_keuangan.kdgiat, keuangan_lembaga_keuangan.kdoutput,
	sum(jumlah) as lembaga_keuangan_jumlah_perkdoutput,
	sum(realisasi) as lembaga_keuangan_realisasi_perkdoutput
	FROM keuangan_lembaga_keuangan
	where lembaga_keuangan_realisasi_date = '".$this->__d."'
	and keuangan_lembaga_keuangan.thang='".$thang."'
	and keuangan_lembaga_keuangan.kdgiat='".$kdgiat."' and keuangan_lembaga_keuangan.kdoutput='".$kdoutput."'
	group by keuangan_lembaga_keuangan.kdsatker, keuangan_lembaga_keuangan.kdgiat, keuangan_lembaga_keuangan.kdoutput
	)
	as table_realisasi
	on table_percentage.kdsatker=table_realisasi.kdsatker
	and table_percentage.kdgiat=table_realisasi.kdgiat
	and table_percentage.kdoutput=table_realisasi.kdoutput
	)
	as table_keuangan
	on lokasis.kdlokasi=table_keuangan.kdlokasi
	left join
	(
	select kdsatker, kdlokasi, table_data.kdgiat, table_data.kdoutput, table_data.kdsoutput,
	table_data.dzohir_target, table_data.dzohir_realisasi, table_data.dzohir_percentage, table_data.dzohir_satkeg
	from (
	SELECT dzohirs.kdsatker, dzohirs.kdlokasi, __is_kegiatan.kdgiat, __is_kegiatan.kdoutput, __is_kegiatan.kdsoutput,
	description, acronym, satkeg as dzohir_satkeg,
	sum(target) as dzohir_target, avg(realisasi) as dzohir_realisasi, avg(realisasi) as dzohir_percentage
	FROM __is_kegiatan
	join dzohirs on __is_kegiatan.kdgiat=dzohirs.kdgiat and __is_kegiatan.kdoutput=dzohirs.kdoutput
	and __is_kegiatan.kdsoutput=dzohirs.kdsoutput
	join dzohir_kelompok_penerima on dzohir_kelompok_penerima.dzohir_id=dzohirs.id
	WHERE is_kegiatan_utama = 'yes' and dzohirs.thang='".$thang."'
	and dzohirs.kdgiat='".$kdgiat."' and dzohirs.kdoutput='".$kdoutput."'
	and dzohirs.kdsoutput='".$kdsoutput."'
	group by dzohirs.kdsatker, dzohirs.kdlokasi,
	__is_kegiatan.kdgiat, __is_kegiatan.kdoutput, __is_kegiatan.kdsoutput, description, acronym, satkeg
	) as table_data
	)
	as table_dzohir
	on table_keuangan.kdsatker=table_dzohir.kdsatker
	and table_keuangan.kdlokasi=table_dzohir.kdlokasi
	and table_keuangan.kdgiat=table_dzohir.kdgiat
	and table_keuangan.kdoutput=table_dzohir.kdoutput
	and table_keuangan.kdsoutput=table_dzohir.kdsoutput
	".$q_prov."
	and table_keuangan.kdgiat='".$kdgiat."'
	and table_keuangan.kdoutput='".$kdoutput."'
	and table_keuangan.kdsoutput='".$kdsoutput."'
	order by nmlokasi asc;