longest sql queries #3

longest sql queries #3.txt

### 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;