Background:

After rate sheets applied in the billing system provided by the partners/carriers, executive likes to sort out the destination and carrier with their latest applied rates along with their very previous rates with the below format:
Carrier Name | Destination | Prefix | Applied Date | Applied Rate | Previous Date | Previous Rate

Steps:

• Select the fields(applied date, rate, application id) provided with the parameter current rates applied date and destination

SELECT insert_date, rate, application_id 
 FROM core_rate_transit_in
 WHERE UPPER (dest_name) LIKE '&&DEST%' --Input for destination
 AND TO_CHAR (insert_date, 'yyyymmdd') = '&&Insert_Date'

• Select the fields(previous applied date, application id) provided with the parameter current rates applied date and destination

 SELECT MAX (insert_date) AS odate, application_id 
 FROM core_rate_transit_in
 WHERE TO_CHAR (insert_date, 'yyyymmdd') < '&&Insert_Date'
 AND UPPER (dest_name) LIKE '&&DEST%'
 GROUP BY application_id
 ORDER BY 1 DESC

• Construct the SELECT statement of the desired output fields from the related tables by using the WITH clause in Oracle with the help of the above SELECTs

/* Formatted on 9/14/2017 11:56:41 AM (QP5 v5.256.13226.35538) */
WITH a
 AS (SELECT insert_date, rate, application_id --Selection of data with the current applied rates
 FROM core_rate_transit_in
 WHERE UPPER (dest_name) LIKE '&&DEST%'
 AND TO_CHAR (insert_date, 'yyyymmdd') = '&&Insert_Date'),
 b
 AS ( SELECT MAX (insert_date) AS odate, application_id --Selection of data with the previous applied rates
 FROM core_rate_transit_in
 WHERE TO_CHAR (insert_date, 'yyyymmdd') < '&&Insert_Date'
 AND UPPER (dest_name) LIKE '&&DEST%'
 GROUP BY application_id
 ORDER BY 1 DESC)
 SELECT DISTINCT d.app_name AS "Carrier Name",
 dest_name AS "Destination",
 prefix AS "Prefix",
 a.insert_date AS "Applied Date",
 a.rate "Applied Rate",
 b.odate AS "Previous Date",
 c.rate AS "Previous Rate"
 FROM a
 JOIN b ON (a.application_id = b.application_id)
 JOIN core_rate_transit_in c ON (b.application_id = c.application_id)
 JOIN core_applicaton d
 ON (c.application_id = d.app_id) AND c.insert_date = b.odate
ORDER BY 1;

Tables: 

1. core_rate_transit_in contains the rate sheets information
2. core_applicaton contains the carriers information

Note: The above SQLs and WITH is applicable to Oracle Database