Skip to main content

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

) ;

Comments

Post a Comment

Popular posts from this blog

SQL to return records in m,n sequence

I want my SQL to return only records from 101 to 200. See below options which would do the required.. Select * from (Select rownum rownumber,employee.* from employee) Where rownumber between 101 and 200; -- Using rownum label in oracle Select * from (Select employee.*, row_number() over (order by emp_id desc) Rownumber from employee) where Rownumber between 101 and 200; --Using the analytical function row_number Select * from (Select employee.*, row_number() over (order by emp_id desc) Rownumber from employee) where Rownumber between :Fromnumber and :tonumber; -- lets user decide the recordset chunk... Hope this is helpful!!!!!!
Post a Comment
Read more

PLSQL programming - Part 1

Pre-requisite : Basic SQL Knowledge in Oracle and programming knowledge in any language Browse my blog to read the overview of PLSQL if you need it. Anonymous block ............. ........... This is where you declare Variables that you are going to use it in PLSQL block ............ Data types are chosen based on requirement. For example, if you want to process strings you go for data types of characters and for numeric operations Int, float , double or number. Begins ..... ......... ......... .......... This is where you implement programming logic and get the desired outcome. .......... .......... Ends..... We call it anonymous block because we don't name it or reference it. (Unlike Package, Stored procedure,& Other objects) Learn from examples Example 1: declare var1_num number; --Declaring number type variable var2_str varchar2; begin var1_num := 10; --Assigning 'Number' Value to the variable var2_str := 'I am a string value...
1 comment
Read more

What happens when you create tables with the existing SYNONYM names

See below snippets: Select * from dba_synonyms where synonym_name='DUAL'; OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK PUBLIC DUAL SYS DUAL (NULL) We know that DUAL table is accessible by users. Lets create a table named DUAL under our schema (USER). CREATE TABLE dual AS SELECT * FROM dual; Select * from dual; insert into dual values('Y'); commit; Select * from dual; DUMMY ---------- X Y The Synonym is not referred unless the Synonym name is not used by any object under the USER/SCHEMA... So , When creating tables in your schema, take a look at SYNONYMs declared as PUBLIC and try not to use them.
1 comment
Read more