SQLize | PHPize | SQLtest

SELECT HRTRNSTYPE, HRTRSOURCE, HRDRBANKCD, HRCRBANKCD, HRCLRCNTER, HRREFUSCD, COUNT(DISTINCT HRIZVREFNR) AS CNTIZVREF, SUM(HRAMOUNT) AS SUMAMOUNT, MAX(max_HRHREDATE) AS max_HRHREDATE FROM ( SELECT HRTRNSTYPE, HRTRSOURCE, HRDRBANKCD, HRCRBANKCD, HRCLRCNTER, HRREFUSCD, HRIZVREFNR, SUM(HRAMOUNT) AS SUMAMOUNT, MAX(HRHREDATE) OVER (PARTITION BY HRTRNSTYPE, HRTRSOURCE, HRDRBANKCD, HRCRBANKCD, HRCLRCNTER, HRREFUSCD, HRIZVREFNR) AS max_HRHREDATE from BCZVD02.IZHR00 base WHERE ( (HRTRNSTYPE IN ('PN','CHEQU') AND HRTRSOURCE='ACH' AND (HRCRTDATE BETWEEN 20230601000000000 AND 20230630999999999)) OR (HRTRNSTYPE IN ('PN','CHEQU') AND HRTRSOURCE='KOF' AND (HRCRVALDAT BETWEEN 20230601 AND 20230630) AND HRDRBANKCD<>'BACX') OR (HRTRNSTYPE IN ('PN','CHEQU') AND HRTRSOURCE='KOF' AND (HRSCDRUNDT BETWEEN 20230601 AND 20230630) AND HRDRBANKCD='BACX')) UNION SELECT HRTRNSTYPE, HRTRSOURCE, HRDRBANKCD, HRCRBANKCD, HRCLRCNTER, HRREFUSCD, HRIZVREFNR, SUM(HRAMOUNT) AS SUMAMOUNT, MAX(HRHREDATE) OVER (PARTITION BY HRTRNSTYPE, HRTRSOURCE, HRDRBANKCD, HRCRBANKCD, HRCLRCNTER, HRREFUSCD, HRIZVREFNR) AS max_HRHREDATE from BCZVD02.IZHR00ARC base WHERE ( (HRTRNSTYPE IN ('PN','CHEQU') AND HRTRSOURCE='ACH' AND (HRCRTDATE BETWEEN 20230601000000000 AND 20230630999999999)) OR (HRTRNSTYPE IN ('PN','CHEQU') AND HRTRSOURCE='KOF' AND (HRCRVALDAT BETWEEN 20230601 AND 20230630) AND HRDRBANKCD<>'BACX') OR (HRTRNSTYPE IN ('PN','CHEQU') AND HRTRSOURCE='KOF' AND (HRSCDRUNDT BETWEEN 20230601 AND 20230630) AND HRDRBANKCD='BACX') ) ) AS combined_results GROUP BY HRTRNSTYPE, HRTRSOURCE, HRDRBANKCD, HRCRBANKCD, HRCLRCNTER, HRREFUSCD;