Gabungan Quey PostreSQL

-- delete history iduser
delete from "temporary".tmp_bukubesar where iduser = 123;


-- tampung data
insert into "temporary".tmp_bukubesar
select ROW_NUMBER () OVER (ORDER BY tanggal_jurnal, kodejurnal),
123, kodejurnal, tanggal_jurnal, debet, kredit, saldoakhir, nomor_rekening, keterangan from (
SELECT '' kodejurnal, (select CAST ( '2019-01-01' AS DATE ) - INTERVAL '1 day' ) tanggal_jurnal, null debet, null kredit,
    (
    SELECT COALESCE
        ( SUM ( acc_end_of_month_detail.end_balanace ), 0 ) AS saldoeom
    FROM
        acc_end_of_month_detail
        JOIN acc_account_number ON acc_end_of_month_detail.nomor_akun = acc_account_number.ID
    WHERE
        acc_account_number.nomor_akun LIKE'2.330%'
        AND acc_end_of_month_detail.ending_date = (
        SELECT COALESCE
            ( MAX ( acc_end_of_month.tgl_eom ), '2000-01-01' )
        FROM
            acc_end_of_month
        WHERE
            acc_end_of_month.tgl_eom < '2019-01-01'
            AND acc_end_of_month_detail.year_month = ( SELECT MAX ( year_month ) FROM acc_end_of_month_detail WHERE acc_end_of_month_detail.year_month < '201901' AND acc_end_of_month_detail.id_bpr = 3 AND acc_end_of_month_detail.id_bpr_cabang = 3 )
            AND acc_end_of_month.id_bpr = 3
            AND acc_end_of_month.id_bpr_cabang = 3
        )
        AND acc_end_of_month_detail.id_bpr = 3
        AND acc_end_of_month_detail.id_bpr_cabang = 3
        ) + (
    SELECT COALESCE
        (
            SUM (
            CASE
                   
                    WHEN acc_account_number.tipe_akun = 0
                    OR acc_account_number.tipe_akun = 3 THEN
                        ( acc_journal_detail.debet_amount - acc_journal_detail.credit_amount ) ELSE ( acc_journal_detail.credit_amount - acc_journal_detail.debet_amount )
                    END
                    ),
                    0
                )
            FROM
                acc_journal_detail
                JOIN acc_account_number ON acc_journal_detail.id_akun = acc_account_number.
                ID JOIN acc_journal ON acc_journal.ID = acc_journal_detail.id_jurnal
            WHERE
                CAST ( tanggal_jurnal AS DATE ) > (
                SELECT COALESCE
                    ( ( MAX ( acc_end_of_month_detail.ending_date ) ), '2000-01-01' )
                FROM
                    acc_end_of_month_detail
                    JOIN acc_account_number ON acc_end_of_month_detail.nomor_akun = acc_account_number.ID
                WHERE
                    acc_account_number.nomor_akun LIKE'2.330%'
                    AND acc_end_of_month_detail.ending_date <= '2019-01-01'
                    AND acc_end_of_month_detail.year_month = ( SELECT MAX ( year_month ) FROM acc_end_of_month_detail WHERE acc_end_of_month_detail.year_month < '201901' AND acc_end_of_month_detail.id_bpr = 3 AND acc_end_of_month_detail.id_bpr_cabang = 3 )
                    AND acc_end_of_month_detail.id_bpr = 3
                    AND acc_end_of_month_detail.id_bpr_cabang = 3
                )
                AND CAST ( tanggal_jurnal AS DATE ) < '2019-01-01'
                AND acc_account_number.nomor_akun LIKE'2.330%'
                AND acc_journal.id_bpr = 3
            AND acc_journal.id_bpr_cabang = 3
    ) saldoakhir,'-' nomor_rekening,'saldo awal' keterangan
    union
    SELECT
    aj.kode_jurnal,
    aj.tanggal_jurnal,
    ajd.debet_amount,
    ajd.credit_amount,
    0 saldoakhir,
    ajd.nomor_rekening,
    aj.deskripsi
FROM
    acc_journal_detail ajd
    JOIN acc_journal aj ON ajd.id_jurnal = aj."id"
    JOIN acc_account_number acn ON ajd.id_akun = acn."id"
WHERE
    aj.id_bpr = 3
    AND aj.tanggal_jurnal >= '2019-01-01'
    AND aj.tanggal_jurnal < CAST ( '2019-08-02' AS DATE ) + INTERVAL '1 day'
    AND acn.nomor_akun LIKE'2.330%'
    AND aj.id_bpr_cabang = 3
ORDER BY tanggal_jurnal) bukubesar
order by tanggal_jurnal, kodejurnal;



--hitung saldoakhir 
DO $$
  Declare rownumber bigint;   
    Declare debet1 bigint;   
        Declare kredit1 bigint;   
        Declare saldoakhir1 bigint;
        declare tipeakun INTEGER;

    cur1 CURSOR for select row_number, debet, kredit, saldoakhir from "temporary".tmp_bukubesar order by row_number;   
 begin 
OPEN Cur1 ; 
Loop     
    FETCH  next from Cur1 INTO rownumber, debet1, kredit1, saldoakhir1;    
    EXIT WHEN NOT FOUND;
   
    select tipe_akun into tipeakun from acc_account_number where nomor_akun='2.330';
   
    if tipeakun in (0,3) then
    update "temporary".tmp_bukubesar
    set saldoakhir=(select saldoakhir from "temporary".tmp_bukubesar where row_number < rownumber order by row_number desc limit 1) + debet1 - kredit1
    where row_number=rownumber and row_number >1 and iduser = 123;
    end if;
   
    if tipeakun in (1,2) then
    update "temporary".tmp_bukubesar
    set saldoakhir=(select saldoakhir from "temporary".tmp_bukubesar where row_number < rownumber order by row_number desc limit 1) - debet1 + kredit1
    where row_number=rownumber and row_number >1 and iduser = 123;
    end if;
   
end loop ;  
CLOSE Cur1; 
END $$;