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.
Compute a Year-Over-Year Difference in PL/SQL
Our task is to compute a Year-Over-Year difference in PL/SQL. We will use the simplest approach. We will simply extract the year from the date. We will use the EXTRACT() function to separate the year from the date. EXTRACT() function gives us the flexibility to fetch year and month from the date. We will see more of its use and work in the examples later on. After extracting the year from the date, we will simply store it in a variable.
We will also store the corresponding column value in another variable. Now we will use a loop to find the column value for the previous year. Thus, we will calculate their difference. We need to handle some errors too for the starting year of the table as there is no data for its previous year.
Let’s set up an Environment
To understand how to Compute a Year-Over-Year Difference in PL/SQL we need a table on which we will perform various operations and queries. Here we will consider a table called ‘inventory’ which contains information such as order_date, inventory_cost, total_cost, and loss as Columns.
ORDER_DATE | INVENTORY_COST | TOTAL_COST | LOSS |
---|---|---|---|
24-FEB-03 | 40000 | 100000 | 25000 |
15-MAR-04 | 42000 | 105000 | 15000 |
26-APR-05 | 45000 | 107000 | 20000 |
05-MAY-06 | 50000 | 115000 | 22000 |
08-JUN-07 | 53000 | 117000 | 17000 |
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.
Conclusion
Overall, computing a year-over-year difference has lots of real-world use cases. PL/SQL being a procedural extension for SQL provides us the flexibility to write our custom scripts. We can easily compute YOY with the help of PL/SQL. Calculating year-over-year differences has lots of uses in various sectors such as finance, medical, strategic business, and many more.
It is used for budget forecasting, and calculating growth and loss in a stock. We have seen various real-world examples in our article such as loss growth, and inventory cost growth. We have also used the EXTRACT() function to extract the year from the date. We have covered all the basic topics with clear and concise examples. Now you can write queries related to it and get the desired output.
Contact Us