Examples of Year-Over-Year Difference in PL/SQL
In this, we will calculate the year-over-year difference in the table ‘inventory’. We will use this table to perform our examples.
Example 1: Calculating the YOY Difference of Inventory Cost in the Inventory Table ( in Ascending Order of Year)
In this example, we will calculate the year-over-year difference in inventory cost for the table ‘inventory‘. We will use the extract() function to separate the year from the date. We will store the current year and inventory cost in separate variables. Then we will find the inventory cost of the previous year and again store it in a separate variable. Thus we will calculate their difference. Let’s move to the query.
Query:
DECLARE
v_year NUMBER;
v_inventoryCost NUMBER;
v_prev_year NUMBER;
v_cost_diff NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('Year| Inventory Cost| I.C. Previous Year| Cost Difference');
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------');
FOR i IN (SELECT EXTRACT(YEAR FROM order_date) AS year, inventory_cost FROM inventory ORDER BY order_date)
LOOP
v_year := i.year;
v_inventoryCost := i.inventory_cost;
v_prev_year := NULL;
BEGIN
SELECT inventory_cost INTO v_prev_year
FROM inventory
WHERE EXTRACT(YEAR FROM order_date) = v_year - 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE((v_year - 1)||' | NULL | NULL | NULL');
END;
IF v_prev_year IS NOT NULL THEN
v_cost_diff := v_inventoryCost - v_prev_year;
DBMS_OUTPUT.PUT_LINE(v_year || ' | ' || v_inventoryCost || ' | ' || v_prev_year || ' | ' || v_cost_diff);
ELSE
v_cost_diff := NULL;
DBMS_OUTPUT.PUT_LINE(v_year || ' | ' || v_inventoryCost || ' | NULL | NULL' );
END IF;
END LOOP;
END;
Output:
Year | Inventory Cost | I.C. Previous Year | Cost Difference |
---|---|---|---|
2002 | NULL | NULL | NULL |
2003 | 40000 | NULL | NULL |
2004 | 42000 | 40000 | 2000 |
2005 | 45000 | 42000 | 3000 |
2006 | 50000 | 45000 | 5000 |
2007 | 53000 | 50000 | 3000 |
Explanation: We loop through each record and store the year and inventory cost. Then, we query the previous year’s inventory cost (year = year – 1) and handle errors. If the previous year isn’t empty, we display the difference between inventory cost and the previous year’s inventory cost.
Example 2: Calculating YOY Difference of Loss in Inventory Table ( in Descending Order of Year)
In this example, we are going to calculate the year-over-year difference between the loss column in the inventory table. We are going to use the same approach as we did in the previous example. The only difference is that we will sort our results in descending order of year. There may be a few possibilities where we need to explore the recent year’s analysis first. In such cases, data sorted in descending order of year can be found useful. Let’s move to the query.
Query:
DECLARE
v_year NUMBER;
v_loss NUMBER;
v_prev_year NUMBER;
v_diff NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('Year | Loss | Loss for Previous Year| Difference');
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------');
FOR i IN (SELECT EXTRACT(YEAR FROM order_date) AS year, loss FROM inventory ORDER BY order_date desc)
LOOP
v_year := i.year;
v_loss := i.loss;
v_prev_year := NULL;
BEGIN
SELECT loss INTO v_prev_year
FROM inventory
WHERE EXTRACT(YEAR FROM order_date) = v_year - 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('');
END;
IF v_prev_year IS NOT NULL THEN
v_diff := v_loss - v_prev_year;
DBMS_OUTPUT.PUT_LINE(v_year || ' | ' || v_loss || ' | ' || v_prev_year || ' | ' || v_diff);
ELSE
v_diff := NULL;
DBMS_OUTPUT.PUT_LINE(v_year || ' | ' || v_loss || ' | NULL | NULL' );
END IF;
END LOOP;
END;
Output:
Year | Loss | Loss for the Previous Year | Difference |
---|---|---|---|
2007 | 17000 | 22000 | -5000 |
2006 | 22000 | 20000 | 2000 |
2005 | 20000 | 15000 | 5000 |
2004 | 15000 | 25000 | -10000 |
2003 | 25000 | NULL | NULL |
Explanation: In the above query, we have followed a similar approach as we did in the previous example. But in this approach, we have displayed our results in descending order concerning year. We can see that when the loss is less than the previous year’s loss, a negative value is displayed and when the loss is increased from the previous year, a positive value is shown. As 2003 is the starting year of the table, there is no data present for the previous year.
Compute a Year-Over-Year Difference in PL/SQL
The year-over-year (YOY) difference calculation is fundamental across multiple sectors, including finance, business analysis, budget forecasting, and stock market analysis. It enables analysts to assess growth or loss trends by comparing data from consecutive years. This metric aids in decision-making processes, such as budget estimation and investment strategies, making it a valuable tool for data analysis and informed decision-making in various industries.
In this article, we are going to “compute a year-over-year difference” in PL/SQL. We will cover its general method to compute a YOY difference easily. We will see its various real-world examples with clear and concise examples.
Contact Us