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:
- core_rate_transit_in contains the rate sheets information
- core_applicaton contains the carriers information
Note: The above SQLs and WITH is applicable to Oracle Database