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

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?

CodePudding user response：

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

CodePudding user response：

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, in the calculation of the new column, you will look for add_months(sysdate, -6) and you will find the wanted value with the respective id of the current person.