MVSFORUMS.com :: View topic - Read Unique rows from a DB2 table

through

#1: Read Unique rows from a DB2 table Author: koolspark,

Posted: Wed Jul 13, 2022 4:03 pm
—
I've a DB2 table. Whenever a customer pays their insurance premium through a credit Card, we make an entry to the DB2 table with a tracking number, policy#, Card#, Timestamp#, Amount paid.

Code:

TRK# Policy# Card# Timestamp Amount#
1 aaaa 1111 2017-04-07-19.45.08.515588 100.00
2 aaaa 1111 2017-05-07-19.45.08.515588 100.00
3 aaaa 1111 2017-06-07-19.45.08.515588 100.00
4 aaaa 2222 2017-07-07-19.45.08.515588 100.00
5 bbbb 3333 2017-04-07-19.45.08.515588 200.00
6 bbbb 3333 2017-05-07-19.45.08.515588 200.00
7 cccc 4444 2017-04-07-19.45.08.515588 300.00
8 cccc 5555 2017-05-07-19.45.08.515588 300.00

My requirement is to extract all the unique credit card rows on a particular policy. If the same credit card is used to pay the premium for more than once on a policy, We need to extract only the latest payment row from the DB2 table. Also, the query result should always be sorted on Timestamp in descending order. So, the query output should look like:
When query on policy# aaaa

Code:

TRK# Policy# Card# Timestamp Amount#
3 aaaa 1111 2017-06-07-19.45.08.515588 100.00
4 aaaa 2222 2017-07-07-19.45.08.515588 100.00

When query on policy#bbbb

Code:

TRK# Policy# Card# Timestamp Amount#
6 bbbb 3333 2017-05-07-19.45.08.515588 200.00

When query on policy#cccc

Code:

TRK# Policy# Card# Timestamp Amount#
7 cccc 4444 2017-04-07-19.45.08.515588 300.00
8 cccc 5555 2017-05-07-19.45.08.515588 300.00

Can you please help?

#2: Author: kolusu, Location: San Jose

Posted: Wed Jul 13, 2022 7:14 pm
—
koolspark,

hmm isn't it simple of getting the MAX of tracking number as you are incrementing the tracking number every time you insert a transaction?

Untested SQL

Code:

SELECT *
FROM your_tbl
WHERE TRK# IN (SELECT MAX(TRK#)
FROM SPARK0_TBL
GROUP BY POLICY#
,CARD#)
;

#3: Author: koolspark,

Posted: Thu Jul 14, 2022 10:31 am
—
Hi Kolusu,
Thank you very much for your quick response and really sorry for not putting my question right in the first place. I did some research on our data and found that if the customer didn't select any of the saved cards on the account's wallet to pay the premium, but manually enters one of the existing cards on the account, he may be assigned the same tracking number. Long story short, we may expect duplicates in the Tracking# column. So, the requirement is to extract all the distinct credit card rows on a policy with timestamp descending order. If a duplicate credit card row exists on a policy, we need to select only the most recent credit card payment row.
Much appreciate your help.
Thank you.

#4: Author: kolusu, Location: San Jose

Posted: Thu Jul 14, 2022 11:34 am
—
koolspark,

You need to show me examples of data how you would have trk# number being the same but it has a higher trk# with an older timestamp.

Alternatively you can get MAX of transaction timestamp which will also give you the desired results.

Code:

SELECT POLICY#
,CARD#
,MAX(TSTMP)
FROM your_tbl
GROUP BY POLICY#
,CARD#
;

#5: Author: koolspark,

Posted: Fri Jul 15, 2022 8:38 am
—
Hi Kolusu,
Thank you very much for your response. please find below the data from our database. I've ordered them on 'SYS_TMSTMP DESC'

Code:

---------+---------+---------+---------+---------+---------+---------+---------+
POL# TRK# CARD# SYS_TMSTMP
---------+---------+---------+---------+---------+---------+---------+---------+
939560000 9422142447956 4985XXXXXXXX9466 2022-07-15-05.33.39.855187
939560000 9422142447956 4985XXXXXXXX9466 2022-06-17-22.13.43.368966
939560000 9422142447956 4985XXXXXXXX9466 2022-05-22-20.34.48.979253
939560000 9221239951859 2230XXXXXXXX9629 2022-04-22-09.42.13.973582

Since first 3 payments are made with the same card, we need to select only the latest payment row (which is the first row after ordering the data in SYS_TMSTMP DESC) and the other payment made with different credit card(last payment in the data).
Thank you.

#6: Author: kolusu, Location: San Jose

Posted: Fri Jul 15, 2022 9:25 am
—
koolspark,

The option of getting the max timestamp should work for any case

Code:

SELECT *
FROM your_tbl
WHERE (POL#,SYS_TMSTP) IN (SELECT POL#
,MAX(SYS_TMSTP)
FROM your_tbl
GROUP BY POL#)
;

MVSFORUMS.com -> Database

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1