I have two tables prod_replay_in
and prod_replay_out
as below.
For msg_type
as CDST010 in prod_replay_in
table has it's confirmation in prod_replay_out
table as CDST01C
. Same for msg_type
as CDST100 has it's confirmation message in prod_replay_out
table as CDST10C
. In below tables, cdsx_time
matters. For example, second CDST100 message will have second CDST10C
message based on cdsx_time
.
Note: JOIN between 2 tables is based on cdsx_id
as it is common between two tables. CDST010 and CDST01C
will have only one entry for same cdsx_id
in prod_replay_in
and prod_replay_out
table respectively. Also, number of CDST100 and CDST10C
messages varies based on cdsx_id
.
What I want to search?
I want to search number of CDST100 messages in prod_replay_in
table as per following criteria:
- Get 1st CDST100 if fourth character in
bancs_msg
for CDST100 is matched with fourth character inmsg_body
for CDST01C - Get 2nd CDST100 if fourth character in
bancs_msg
for CDST100 is matched with fourth character inmsg_body
for 1st CDST10C - Get 3rd CDST100 if fourth character in bancs_msg for CDST100 is matched with fourth character
in
msg_body
for 2nd CDST10C
The above pattern is like checking 2nd CDST100 with first CDST10C, 3rd CDST100 with 2nd CDST10C, etc.
prod_replay_in
id(serial) | msg_type(varchar) | cdsx_time(timestamp) | cdsx_id(varchar) | bancs_msg(text)
------------------------------------------------------------------------------------------
8334698 | CDST010 | 2020-02-24 14:23:01.0 | T202005518525 | ABCD
8341809 | CDST100 | 2020-02-24 14:47:38.0 | T202005518525 | ANOC
8342732 | CDST100 | 2020-02-24 14:51:53.0 | T202005518525 | PHLM
8344890 | CDST100 | 2020-02-24 15:15:14.0 | T202005518525 | JKQO
prod_replay_out
id(serial) | msg_type(varchar) | cdsx_time(timestamp) | cdsx_id(varchar) | msg_body(text)
------------------------------------------------------------------------------------------
42164527 | CDST01C | 2020-02-24 14:23:08.016 | T202005518525 | AQRS
42176068 | CDST10C | 2020-02-24 14:47:47.056 | T202005518525 | STUM
42177522 | CDST10C | 2020-02-24 14:52:00.031 | T202005518525 | XYZK
42245814 | CDST10C | 2020-02-24 15:30:00.045 | T202005518525 | ASQO
What I have tried?
I have tried creating sql query for matching 1st CDST100 and CDST01C but not sure how can I compare with subsequent CDST100 with CDST10C?
SELECT count(T.id) FROM prod_replay_in T JOIN prod_replay_out O ON T.CDSX_ID = O.CDSX_ID
JOIN prod_replay_out K ON K.CDSX_ID = O.CDSX_ID
WHERE T.MSG_TYPE = 'CDST100'
and O.msg_type = 'CDST01C'
and K.msg_type = 'CDST10C'
and ( (
min(T.cdsx_time) > O.cdsx_time
and substr(T.bancs_msg,4,1) = substr(O.msg_body,4,1)
)
);