How to get data of 12-month ago

user_17845435 0
Viewed 52

In Oracle database, I have this data in a table:

person category month profit
John A Jun-1-2022 100
Mary A May-1-2022 200
John B Jun-1-2021 230
John A Jun-1-2021 430

I need to add a new column into this table, called 'Same_month_last_year', which contains the data of same month last year. For example, John's data would be 430 for row 1.

I know a function in Oracle called ADD_MONTHS. but I'm new to programming (a finance student) and cannot seem to figure out how to use ADD_MONTHS to create this new column. Could you please advise?

oracle sql
2 Answers

Use the SUM analytic function with a range window:

SELECT t.*,
       SUM(profit) OVER (
         PARTITION BY person
         ORDER BY month
         RANGE BETWEEN INTERVAL '12' MONTH PRECEDING
                   AND INTERVAL '12' MONTH PRECEDING
       ) AS last_year_profit
FROM   table_name t

Which, for the sample data:

CREATE TABLE table_name (person, month, profit) AS
SELECT 'John', DATE '2022-06-01', 100 FROM DUAL UNION ALL
SELECT 'Mary', DATE '2022-05-01', 200 FROM DUAL UNION ALL
SELECT 'John', DATE '2021-06-01', 430 FROM DUAL;

Outputs:

PERSON MONTH PROFIT LAST_YEAR_PROFIT
John 01-JUN-21 430 null
John 01-JUN-22 100 430
Mary 01-MAY-22 200 null

db<>fiddle here

user_15092640

You can not have dynamically calculated columns in a table. You need to create a view and assign IDs to each person. Then in the view's script, the calculation of the new column will work like this:

(select profit
from table t
where
month = add_months(t.month, -6)
and id = t.id) as same_month_last_year
user_166393930
Related