FREE AI-Enhanced Online Oracle Compiler

Untitled*

You must execute the code again to save those values in the database.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

CREATE TABLE Product (
  Bill_Date         DATE,
  LGroup            VARCHAR2(250 BYTE),
  District          VARCHAR2(50 BYTE),
  MonthTot          NUMBER(15,4),
  Last12Tot         NUMBER(18,4)
);

INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('05/01/2022','MM/DD/YYYY'), 'Sam','West', 13);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('06/01/2022','MM/DD/YYYY'), 'Sam','West', 10);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('07/01/2022','MM/DD/YYYY'), 'Sam','West', 11);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('08/01/2022','MM/DD/YYYY'), 'Sam','West', 8);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('05/01/2022','MM/DD/YYYY'), 'Chi','West', 21);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('06/01/2022','MM/DD/YYYY'), 'Chi','West', 19);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('07/01/2022','MM/DD/YYYY'), 'Chi','West', 15);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('08/01/2022','MM/DD/YYYY'), 'Chi','West', 11);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('05/01/2022','MM/DD/YYYY'), 'Emi','East', 8);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('06/01/2022','MM/DD/YYYY'), 'Emi','East', 5);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('07/01/2022','MM/DD/YYYY'), 'Emi','East', 6);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('08/01/2022','MM/DD/YYYY'), 'Emi','East', 4);

-- this will do a running total for one group
SELECT bill_date,
       LGroup,
       District,
       MonthTot,
       sum(MonthTot) over (
         PARTITION BY LGroup
         ORDER BY bill_date
         RANGE BETWEEN INTERVAL '11' MONTH PRECEDING AND CURRENT ROW
       ) AS Last12Tot
FROM   Product
order by LGroup, District, bill_date;

BILL_DATE	LGROUP	DISTRICT	MONTHTOT	LAST12TOT
2022-05-01 00:00:00	Chi	West	21	21
2022-06-01 00:00:00	Chi	West	19	40
2022-07-01 00:00:00	Chi	West	15	55
2022-08-01 00:00:00	Chi	West	11	66
2022-05-01 00:00:00	Emi	East	8	8
2022-06-01 00:00:00	Emi	East	5	13
2022-07-01 00:00:00	Emi	East	6	19
2022-08-01 00:00:00	Emi	East	4	23
2022-05-01 00:00:00	Sam	West	13	13
2022-06-01 00:00:00	Sam	West	10	23
2022-07-01 00:00:00	Sam	West	11	34
2022-08-01 00:00:00	Sam	West	8	42