Untitled
unknown
plain_text
a month ago
2.3 kB
2
Indexable
Never
-- SELECT * FROM t_journal_detail WHERE journal_id = 'BI.2001.001.BCA 8999.00003' SET @start_date = '2024-01-01'; SET @end_date = '2024-01-31'; SET @slip_name = 'BCA 6699'; SET @slip_name = CASE WHEN @slip_name = 'All' THEN '%%' ELSE @slip_name END; SELECT ms.slip_name, '01. Saldo Awal' as journal_id, @start_date as journal_date, '01. Saldo Awal' as journal_type, NULL as notes_header, 0 as `index`, si.nm_acc, CASE WHEN si.idr_value >= 0 THEN si.idr_value ELSE 0 END as debet, CASE WHEN si.idr_value < 0 THEN si.idr_value ELSE 0 END as credit, NULL as notes_detail FROM s_initial_coa as si INNER JOIN m_slip as ms on si.no_acc1 = ms.no_acc1 AND si.no_acc2 = ms.no_acc2 AND si.no_acc3 = ms.no_acc3 AND si.no_acc4 = ms.no_acc4 AND si.no_acc5 = ms.no_acc5 AND si.no_acc6 = ms.no_acc6 AND si.no_acc7 = ms.no_acc7 WHERE si.`year` = YEAR(@start_date) AND si.`month` = MONTH(@start_date) AND ms.slip_name LIKE @slip_name UNION ALL SELECT tmp.slip_name, jh.journal_id, jh.journal_date, jh.journal_type, REPLACE(jh.notes,'\r\n',' ') as notes_header, jd.`index`, jd.nm_acc, jd.credit as debet, jd.debet as kredit, REPLACE(jd.notes,'\r\n',' ') as notes_detail FROM t_journal_header as jh INNER JOIN t_journal_detail as jd on jd.journal_id = jh.journal_id INNER JOIN ( SELECT jd.journal_id, jd.`index`, ms.slip_name FROM t_journal_header as jh INNER JOIN t_journal_detail as jd on jd.journal_id = jh.journal_id INNER JOIN m_slip as ms on jd.no_acc1 = ms.no_acc1 AND jd.no_acc2 = ms.no_acc2 AND jd.no_acc3 = ms.no_acc3 AND jd.no_acc4 = ms.no_acc4 AND jd.no_acc5 = ms.no_acc5 AND jd.no_acc6 = ms.no_acc6 AND jd.no_acc7 = ms.no_acc7 WHERE jh.journal_date BETWEEN @start_date AND @end_date AND jh.journal_void = 0 AND ms.slip_name LIKE @slip_name ) as tmp on jd.journal_id = tmp.journal_id AND jd.`index` <> tmp.'index' ORDER BY slip_name, journal_date, journal_id, 'index'
Leave a Comment