MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Read Unique rows from a DB2 table

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
koolspark
Beginner


Joined: 27 May 2010
Posts: 29
Topics: 13

PostPosted: Wed Jul 13, 2022 4:03 pm    Post subject: Read Unique rows from a DB2 table Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Wed Jul 13, 2022 7:14 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
koolspark
Beginner


Joined: 27 May 2010
Posts: 29
Topics: 13

PostPosted: Thu Jul 14, 2022 10:31 am    Post subject: Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Thu Jul 14, 2022 11:34 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
koolspark
Beginner


Joined: 27 May 2010
Posts: 29
Topics: 13

PostPosted: Fri Jul 15, 2022 8:38 am    Post subject: Reply with quote

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
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12354
Topics: 75
Location: San Jose

PostPosted: Fri Jul 15, 2022 9:25 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group