You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
34 lines
1.8 KiB
SQL
34 lines
1.8 KiB
SQL
-- DELETE FROM bbtahunan WHERE tahun = ;
|
|
INSERT INTO bbtahunan
|
|
SELECT app,kode,main,nama,nama_main, nr, dk, tahun, sum(total) total FROM (
|
|
SELECT app, rek kode, main, nama, nama_main, nr, dk, 2024 tahun,sum(total) total FROM (
|
|
SELECT app, main, nama_main, dk, kode rek, nama, nr, total FROM bbtahunan WHERE tahun = '2023'
|
|
UNION ALL
|
|
SELECT acc.app, main,nama_main, dk, rekkredit rek, nama, acc.nr , sum(if(dk = 'D', kredit*-1,kredit)) total FROM lap_bb
|
|
LEFT JOIN acc ON acc.kode = lap_bb.rekkredit WHERE lap_bb.tgl LIKE "2024%"
|
|
GROUP BY rekkredit
|
|
UNION ALL
|
|
SELECT acc.app, main,nama_main, dk, rekdebit rek, nama, acc.nr, sum(if(dk <> 'D', debit *-1,debit)) total FROM lap_bb
|
|
LEFT JOIN acc ON acc.kode = lap_bb.rekdebit WHERE lap_bb.tgl LIKE "2024%"
|
|
GROUP BY rekdebit
|
|
|
|
) a GROUP BY rek, app HAVING app IS NOT NULL
|
|
UNION ALL
|
|
SELECT app, getKodeSHU(app) kode, left(getKodeSHU(app),4) main, concat('SHU belum dibagi', IF(app="acc","Induk","USP" )) nama, concat('SHU belum dibagi', IF(app="acc","Induk","USP" )) nama_main, 'N' nr, 'K' dk , 2024 tahun ,sum(if(dk = 'k', total * -1,total)) * -1 total FROM (
|
|
SELECT acc.app, nr, dk, rekkredit rek, nama, sum(if(dk = 'D', kredit*-1,kredit)) total FROM lap_bb
|
|
LEFT JOIN acc ON acc.kode = lap_bb.rekkredit WHERE lap_bb.tgl LIKE "2024%" AND lap_bb.tgl <= "2024-12-31"
|
|
GROUP BY rekkredit
|
|
UNION ALL
|
|
SELECT acc.app, nr, dk, rekdebit rek, nama, sum(if(dk <> 'D', debit *-1,debit)) total FROM lap_bb
|
|
LEFT JOIN acc ON acc.kode = lap_bb.rekdebit WHERE lap_bb.tgl LIKE "2024%" AND lap_bb.tgl <= "2024-12-31"
|
|
GROUP BY rekdebit
|
|
) a WHERE a.nr = 'L' GROUP BY app
|
|
) a
|
|
WHERE NOT EXISTS (
|
|
SELECT 1
|
|
FROM bbtahunan
|
|
WHERE bbtahunan.app = a.app
|
|
AND bbtahunan.kode = a.kode
|
|
AND bbtahunan.tahun = a.tahun
|
|
)
|
|
GROUP BY app, kode |