Identify current and prev details of a customer

 

create table customer_rating(

id char,

name char,

irrating char,

jcrrating char,

procDate date,

flag char

);

insert into customer_rating values('A','D','X','Y','28-Jul-21','M');

insert into customer_rating values('A','D','M','L','27-Jul-21','M');

Select cr.id,

cr.name,

cr_prev.irrating pcob_rating,

cr.irrating cob_rating,

cr_prev.jcrrating pcob_jcr_rating,

cr.jcrrating cob_jcr_rating

From Customer_Rating cr join

Customer_Rating cr_Prev  ON

cr.procdate = '28-Jul-21' AND

cr_prev.id = cr.id AND

cr_prev.procdate = ( 

Select max(procdate) from Customer_Rating 

Where id=cr.id and procdate < cr.procdate

) ;