SQL DB2 Max Date Needed for Date Generated with exception

Question

I have payments data and I need to find latest date for payment which is 'Normal', so it cant be Fee or Fee pay and I am stuck. Could you please assist? so in this case I need data to bring me '12-07-2018' only as this is the latest normal payments. I cannot filter by 'Normal' as it is actually coded with randomly assigned numbers as well as Fee pay.

Original data

I am stuck how my case when should look like and how can I filter this data out..

SELECT   T.Date,  T.Amount, T.Text  
FROM (SELECT  a.RTVLDT AS Date,  a.BLPS AS Amount, a.TXFT AS Text,
            CASE WHEN a.RTVLDT=a.RTVLDT AND A.TXFT in ('Fee') THEN A.RTVLDT as Last_Transaction
        FROM data a 
       ) as T
GROUP BY T.Date,  T.Amount, T.Text

score 0 · Answer 1 · answered Nov 19 '19 at 11:37

0

How about aggregation?

SELECT MAX(Date)
FROM data d
WHERE d.TXFT LIKE 'Normal%';

Or, if you want the complete row:

SELECT d.*
FROM data d
WHERE d.TXFT LIKE 'Normal%'
ORDER BY d.date DESC
FETCH FIRST 1 ROW ONLY;

The filtering on "Normal" uses LIKE.

answered Nov 19 '19 at 11:37

Gordon Linoff

1,242,037
58
646
786

I am not able to use a word Normal, I wrote it here only for security reasons. It is usually just a number as well as Fee pay. Word 'Fee' is always the same though – marijapt Nov 19 '19 at 11:44
@MarijaPotapova . . . It is only possible to answer the question that you actually ask, which this does. If you cannot adapt this answer, then ask a *new* question with better representative data. – Gordon Linoff Nov 19 '19 at 13:35

score 0 · Answer 2 · answered Nov 19 '19 at 12:23

Please use regular expression. If you know that Fee Pay is something that a normal transaction would have with random number, below code works

SELECT MAX(d.date)
FROM Source_data d
WHERE UPPER(d.Text) LIKE '%FEE PAY[0-9]'
GROUP BY d.date

ORDER BY d.date DESC

This will give you the latest date. you can replace FEE PAY into something else that is you keyword.

Hope it helps.

score 0 · Answer 3 · answered Nov 19 '19 at 19:14

To filter out try

  Where ucase(A.TXFT) not like '%FEE%'

Here is your revised code

SELECT   T.Date,  T.Amount, T.Text  
FROM (SELECT  a.RTVLDT AS Date,  a.BLPS AS Amount, a.TXFT AS Text,
            CASE WHEN a.RTVLDT=a.RTVLDT AND A.TXFT in ('Fee') THEN A.RTVLDT as Last_Transaction
        FROM data a 
    Where ucase(A.TXFT) not like '%FEE%'
       ) as T
GROUP BY T.Date,  T.Amount, T.Text

To get the max date, use the max function and group by For example,

SELECT  max(T.Date) FROM 
        (SELECT * FROM data a  Where ucase(A.TXFT) not like '%FEE%') as T
GROUP BY T.Date,  T.Amount, T.Text

SQL DB2 Max Date Needed for Date Generated with exception

3 Answers3