longest sql queries #2

longest sql queries #2.txt

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

This SQL expression retrieves data from multiple tables and calculates some values.

The select statement retrieves data from the "table_keuangan" table and columns: "dzohir_target", "dzohir_realisasi", "dzohir_percentage", "dzohir_satkeg".

The data retrieved is a result of multiple joins, subqueries and calculations. The innermost subquery calculates the sum of "jumlah" from the "__is_items" table, grouping by "kdgiat", "kdoutput", and "kdsoutput". Another subquery calculates the sum of "jumlah" from the "__is_items" table, grouping by "kdgiat" and "kdoutput". Both subqueries join with the "subkomponens" table with some conditions on the "thang", "kdsatker", and "kdlokasi" columns.

Then, the result of both subqueries is joined on "kdgiat" and "kdoutput" to calculate the percentage of "sum_perkdsoutput" by "sum_perkdoutput".

The result of the previous calculation is joined with the "lembaga_keuangan_jumlah_perkdoutput" and "lembaga_keuangan_realisasi_perkdoutput" tables to calculate "anggaran_pagu" and "anggaran_realisasi".

Finally, the result of the previous calculation is joined with the "__is_kegiatan" table and the expression "(anggaran_realisasi/anggaran_pagu)*100" is calculated to get the "anggaran_percentage".

Note: The query uses string concatenation to dynamically specify the value of variables such as "thang", "kdsatker", and "user->kdlokasi". The value of the "q_kab_on_subkomponen" variable is also used in the conditions of the subqueries.