1

I have two tables prod_replay_in and prod_replay_outas 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 in msg_body for CDST01C
  • Get 2nd CDST100 if fourth character in bancs_msg for CDST100 is matched with fourth character in msg_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)
        )
      );
Jayveer Parmar
  • 500
  • 7
  • 25

2 Answers2

1

I have tried following code. It should work as per my knowledge. Please feel free to suggest if any scenarios are missing.

Select count(T.*) from PROD_REPLAY_IN T, PROD_REPLAY_OUT O
Where T.msg_type = 'CDST100'
AND O.msg_type IN ('CDST01C', 'CDST10C')
AND T.cdsx_id = O.cdsx_id
AND substr(T.bancs_msg,4,1) = substr(O.msg_body,4,1)
AND (O.cdsx_id, O.cdsx_time) IN (Select Y.cdsx_id, Max(Y.cdsx_time) from PROD_REPLAY_OUT Y
        Where Y.msg_type IN ('CDST01C', 'CDST10C')
        AND O.cdsx_id = Y.cdsx_id
        AND T.cdsx_time > Y.cdsx_time
        GROUP BY Y.cdsx_id
        )
Joy Will
  • 33
  • 6
0

So long as you know that there will be a corresponding prod_replay_out row for every prod_replay_in row by timestamps, you can set this up to use window functions to sidestep the different message types at the beginning of a replay:

with interleave as (
  select msg_type, cdsx_time, cdsx_id, bancs_msg as msg, 'in' as direction
    from prod_replay_in
  union all
  select msg_type, cdsx_time, cdsx_id, msg_body as msg, 'out' as direction
    from prod_replay_out
), match_cdst100 as (
  select cdsx_id, msg,
         lag(msg) over (partition by cdsx_id
                            order by cdsx_time) as last_msg
    from interleave
   where msg_type = 'CDST100'
)
select cdsx_id, count(*) 
  from match_cdst100
 where substr(msg, 4, 1) = substr(last_msg, 4, 1)
 group by cdsx_id;      
          
Mike Organek
  • 11,647
  • 3
  • 11
  • 26