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.