Hi Every one,
I want to write the select logic to get below desired output bases on some rule on some data set
- All the records in the claim Authorization Table divided into two groups Group 1 and Group 2
2)Group 1 -All codes with claim rejection status as cancel comes in group 2 and for these the latest version among all the cancel response for Authcode should be considered (see output AUTH003 among claim id 2008 and 2009 latest version of cancellation 2009 is selected
3)All other Claim Rejection status comes in Group 1
a) when the claim rejection status is Send_response then select the previous version of that entry (row just above the send_response )entry -- for Ex Claim ID 2017 and 2004 is selected
b) if there are two or there consecutive Send_reponse status then select the previous version of the first entry of response code -- for ex AUTH127 there are three send_response so first version with claim ID 2013 is selected
- if there are single version of Authcode is available (only 1 record with claim rejection status as null) will not be passed for Ex : AUTH001,AUTH002 is rejected because these have only 1 version (single entry)
claim Authorization source Table
Please help,Apologies for the big rule set