How to find values that appear more than twice per day in a UNION ALL select in Db2

Question

I need to check the account number which came in more than 2 times in any one day.

Below is my query, I'm getting the results where accounts number which came in more than 2 only from FAILURE not from SUCCESS. I need to get from both. Any helps are appreciated.

Sample data:

04/28/2020 123345 BERLIN 5645756768 SUCCESS
04/28/2020 123346 BERLIN 5645756768 FAILURE
04/28/2020 123344 BERLIN 5645756768 SUCCESS
04/28/2020 123344 BERLIN 5645756761 FAULTED
04/28/2020 123345 BERLIN 5645756763 FAILURE
04/28/2020 123346 BERLIN 5645756764 SUCCESS
04/28/2020 123347 BERLIN 5645756766 FAILURE
04/28/2020 123344 BERLIN 5645756763 TIME OUT
04/28/2020 123344 BERLIN 5645756760 PENDING

Query:

SELECT
    DATE,
    ID,
    NAME,
    ACCOUNT,
    STATUS
FROM
(
SELECT
    T1.DATE AS DATE, 
    T1.ID AS ID,
    T1.NAME AS NAME,
    T2.ACCOUNT_NUMBER AS ACCOUNT,
    T2.STATUS AS STATUS,
    COUNT(1) OVER (PARTITION BY T2.ACCOUNT_NUMBER, DATE(DATE) CNT
FROM TABLE1 AS T1
JOIN TABLE2 AS T2
ON T2.ID = T1.ID 
WHERE STATUS = 'SUCCESS'
GROUP BY T1.ID, T1.NAME,T2.ACCOUNT_NUMBER,T2.STATUS

UNION ALL

SELECT
    T1.DATE AS DATE,
    T1.ID AS ID,
    T1.NAME AS NAME,
    T2.ACCOUNT_NUMBER AS ACCOUNT,
    T2.STATUS AS STATUS,
    COUNT(1) OVER (PARTITION BY T2.ACCOUNT_NUMBER, DATE(DATE) CNT
FROM TABLE1 AS T1
JOIN TABLE2 AS T2
ON T2.ID = T1.ID 
WHERE STATUS = 'FAILED'
GROUP BY T1.ID, T1.NAME,T2.ACCOUNT_NUMBER,T2.STATUS
)
WHERE CNT > 2;'

Expected Output:

04/28/2020 123345 BERLIN 5645756768 SUCCESS
04/28/2020 123346 BERLIN 5645756768 FAILURE
04/28/2020 123344 BERLIN 5645756768 SUCCESS

Answer 1

I have no idea why you need UNION ALL here, but let's presume, that you have some more complex checks with your real case.

WITH TAB (DATE, ID, NAME, ACCOUNT, STATUS) AS 
(
VALUES
  ('04/28/2020', 123345, 'BERLIN', '5645756768', 'SUCCESS')
, ('04/28/2020', 123346, 'BERLIN', '5645756768', 'FAILURE')
, ('04/28/2020', 123344, 'BERLIN', '5645756768', 'SUCCESS')
, ('04/28/2020', 123344, 'BERLIN', '5645756761', 'FAULTED')
, ('04/28/2020', 123345, 'BERLIN', '5645756763', 'FAILURE')
, ('04/28/2020', 123346, 'BERLIN', '5645756764', 'SUCCESS')
, ('04/28/2020', 123347, 'BERLIN', '5645756766', 'FAILURE')
, ('04/28/2020', 123344, 'BERLIN', '5645756763', 'TIME OUT')
, ('04/28/2020', 123344, 'BERLIN', '5645756760', 'PENDING')
)
SELECT DATE, ID, NAME, ACCOUNT, STATUS
FROM
(
SELECT 
  DATE, ID, NAME, ACCOUNT, STATUS
, COUNT(1) OVER (PARTITION BY DATE, ACCOUNT) CNT
--FROM TAB
--WHERE STATUS IN ('SUCCESS', 'FAILURE')
FROM
(
SELECT DATE, ID, NAME, ACCOUNT, STATUS
FROM TAB
WHERE STATUS = 'SUCCESS'
  UNION ALL
SELECT DATE, ID, NAME, ACCOUNT, STATUS
FROM TAB
WHERE STATUS = 'FAILURE'
)
) 
WHERE CNT>2;

The same result you get, if you just comment out whole FROM (...) and uncomment the commented out lines. The result it is:

|DATE      |ID         |NAME  |ACCOUNT   |STATUS |
|----------|-----------|------|----------|-------|
|04/28/2020|123345     |BERLIN|5645756768|SUCCESS|
|04/28/2020|123344     |BERLIN|5645756768|SUCCESS|
|04/28/2020|123346     |BERLIN|5645756768|FAILURE|