Untitled

SELECT 
  * 
FROM 
  (
    SELECT 
      'posda' as type, 
      lunas_tgl, 
      lunas_jam, 
      'W001DS56' as display, 
      nosr, 
      thn, 
      bln, 
      idx, 
      SUM(
        byadm + hargaair + abt + bebanalat + danameter + ppn + retribusi + denda + materai
      ) AS bayar, 
      inam 
    FROM 
      posda a 
    WHERE 
      idpdam = '406' 
      AND (
        NOSR = '0101030081' 
        OR NOSRLAMA = '0101030081'
      ) 
      AND substring(inam, 1, 10) = concat(
        substring('20241114', 1, 4), 
        '-', 
        substring('20241114', 5, 2), 
        '-', 
        substring('20241114', 7, 2)
      ) 
      AND CONCAT(idpdam, session_id) NOT IN (
        SELECT 
          CONCAT(idpdam, session_id) 
        FROM 
          rev
      ) 
    GROUP BY 
      ID 
    union 
    select 
      'possb' as type, 
      lunas_tgl, 
      lunas_jam, 
      'W001DS56' as display, 
      nosr, 
      substring(tgljt, 1, 4), 
      substring(tgljt, 6, 2), 
      '1' AS IDX, 
      sum(tagihan + denda + materai) as bayar, 
      inam 
    from 
      possb 
    WHERE 
      idpdam = '406' 
      AND (
        NOSR = '0101030081' 
        OR NOSRLAMA = '0101030081'
      ) 
      AND substring(inam, 1, 10) = concat(
        substring('20241114', 1, 4), 
        '-', 
        substring('20241114', 5, 2), 
        '-', 
        substring('20241114', 7, 2)
      ) 
      AND CONCAT(idpdam, session_id) NOT IN (
        SELECT 
          CONCAT(idpdam, session_id) 
        FROM 
          rev
      ) 
    union 
    select 
      'posbs' as type, 
      lunas_tgl, 
      lunas_jam, 
      'W001DS56' as display, 
      nosr, 
      substring(tgltutup, 1, 4), 
      substring(tgltutup, 6, 2), 
      '1' AS IDX, 
      sum(tagihan) as bayar, 
      inam 
    from 
      posbs 
    WHERE 
      idpdam = '406' 
      AND (
        NOSR = '0101030081' 
        OR NOSRLAMA = '0101030081'
      ) 
      AND substring(inam, 1, 10) = concat(
        substring('20241114', 1, 4), 
        '-', 
        substring('20241114', 5, 2), 
        '-', 
        substring('20241114', 7, 2)
      ) 
      AND CONCAT(idpdam, session_id) NOT IN (
        SELECT 
          CONCAT(idpdam, session_id) 
        FROM 
          rev
      ) 
    GROUP BY 
      ID
  ) AS results 
where 
  lunas_tgl != ""