| View previous topic :: View next topic |
| Author |
Message |
koolspark
Beginner
Joined: 27 May 2010
Posts: 29
Topics: 13
|
 Posted: Wed Jul 13, 2022 4:03 pm Post subject: Read Unique rows from a DB2 table |
 |
|
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? |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
|
| Posted: Wed Jul 13, 2022 7:14 pm Post subject: |
|
|
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#)
;
|
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
koolspark
Beginner
Joined: 27 May 2010
Posts: 29
Topics: 13
|
| Posted: Thu Jul 14, 2022 10:31 am Post subject: |
|
|
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. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
|
| Posted: Thu Jul 14, 2022 11:34 am Post subject: |
|
|
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#
; |
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
koolspark
Beginner
Joined: 27 May 2010
Posts: 29
Topics: 13
|
| Posted: Fri Jul 15, 2022 8:38 am Post subject: |
|
|
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. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12367
Topics: 75
Location: San Jose
|
| Posted: Fri Jul 15, 2022 9:25 am Post subject: |
|
|
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#)
; |
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
