SELECT
CASE
	WHEN TRIM(KODE_GROUP1) = '' THEN CONCAT('TAB',DATE_FORMAT(CURDATE() - INTERVAL 0 DAY,'%Y%m%d'),'XXX')
    ELSE CONCAT('TAB',DATE_FORMAT(CURDATE() - INTERVAL 0 DAY,'%Y%m%d'),TRIM(KODE_GROUP1))
END as id
,'Tabungan' AS product
,CURDATE() - INTERVAL 0 DAY AS tanggal
,CASE
	WHEN TRIM(KODE_GROUP1) = '' THEN 'XXX'
    ELSE TRIM(KODE_GROUP1)
END AS kode_marketing
,SUM(SALDO_AKHIR) AS os
FROM tabung
WHERE STATUS_AKTIF NOT IN ('3')
GROUP BY TRIM(KODE_GROUP1)
UNION ALL
SELECT
CASE
	WHEN TRIM(KODE_GROUP1) = '' THEN CONCAT('DEP',DATE_FORMAT(CURDATE() - INTERVAL 0 DAY,'%Y%m%d'),'XXX')
    ELSE CONCAT('DEP',DATE_FORMAT(CURDATE() - INTERVAL 0 DAY,'%Y%m%d'),TRIM(KODE_GROUP1))
END as id
,'Deposito' AS product
,CURDATE() - INTERVAL 0 DAY AS tanggal
,CASE
    WHEN TRIM(KODE_GROUP1) = '' THEN 'XXX'
    ELSE TRIM(KODE_GROUP1)
END AS kode_marketing
,SUM(JML_DEPOSITO) AS os
FROM deposito
WHERE STATUS_AKTIF NOT IN ('3')
GROUP BY TRIM(KODE_GROUP1)
UNION ALL
SELECT
CASE
	WHEN TRIM(KODE_GROUP1) = '' THEN CONCAT('KRE',DATE_FORMAT(CURDATE() - INTERVAL 0 DAY,'%Y%m%d'),'XXX')
    ELSE CONCAT('KRE',DATE_FORMAT(CURDATE() - INTERVAL 0 DAY,'%Y%m%d'),TRIM(KODE_GROUP1))
END as id
,'Kredit' AS product
,CURDATE() - INTERVAL 0 DAY AS tanggal
,TRIM(KODE_GROUP1) AS kode_marketing
,SUM(POKOK_SALDO_AKHIR) AS os
FROM kredit
WHERE STATUS_AKTIF NOT IN ('3')
GROUP BY TRIM(KODE_GROUP1)