longest sql queries #1

longest sql queries #1.txt

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