Calculate Months separated (oracle 11G)-CodePudding

Dear Respectful Experts,

We have a table having Column TOTAL_MONTHS e.g. if

As shown below table need to Update using Update Statement.

TOTAL_MONTHS = 15 then Update the Column named "Months<=12" = 12 and Update the Column named "Months>12" = 3

another example in below table is TOTAL_MONTHS =8 then Update Column named "Months<=12" = 8 and Update Column named "Months>12" = 0

Title	Amount	Total_months	Months<=12	Months>12
10101288	28000	15	12	3
10101289	40000	13	12	1
10101290	2000000	10	10	0
10101291	50000	14	12	2
10101239	6000	11	11	0
10101240	50000	8	8	0
10121003	690	12	12	0

CREATE TABLE "TEST3"       
   (  TITLE VARCHAR2(100 BYTE),     
  AMOUNT NUMBER,     
  Total_Months NUMBER,     
  Months<=12 NUMBER,     
  Months>12 NUMBER    
   )  

REM INSERTING into TEST3
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months<=12","Months>12") values ('10101288',28000,15,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months<=12","Months>12") values ('10101289',40000,13,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months<=12","Months>12") values ('10101290',2000000,10,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months<=12","Months>12") values ('10101291',50000,14,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months<=12","Months>12") values ('10101239',6000,11,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months<=12","Months>12") values ('10101240',50000,8,null,null);
Insert into TEST3 (TITLE,AMOUNT,"Total_Months","Months<=12","Months>12") values ('10121003',690,12,null,null);

Somebody can help us please.

Thanks,

Regards,

Out Put Result as below

Title	Amount	Total_months	Months<=12	Months>12
10101288	28000	15	12	3
10101289	40000	13	12	1
10101290	2000000	10	10	0
10101291	50000	14	12	2
10101239	6000	11	11	0
10101240	50000	8	8	0
10121003	690	12	12	0

CodePudding user response：

Use LEAST and GREATEST:

update test3
set "Months<=12" = LEAST(total_months, 12),
    "Months>12" = GREATEST(total_months - 12, 0);

The same can be done with CASE expressions of course.

Demo: https://dbfiddle.uk/wB89f9eN

I don't consider it a good idea to store these calculated values in common table columns, by the way. Don't store data redundantly in a database. What would it mean, if some day you find a row containing total_months = 15, "Months<=12" = 12, "Months>12" = 10? Which value would be correct, which not? If you want to see the results like if they were table columns for convenience, use generated columns or a view instead.

CodePudding user response：

If you want to calculate the months as full-years and part-years or as up-to-one year and more-than-one-year then you can calculate those values and do not need to have separate columns in the database that run the risk of becoming out of sync.

If you did want to represent the values in the table then you can use virtual columns:

CREATE TABLE "TEST3"(
  TITLE            VARCHAR2(100 BYTE),
  AMOUNT           NUMBER,     
  Total_Months     NUMBER,     
  Full_Year_Months NUMBER
                   GENERATED ALWAYS AS (Total_months - MOD(total_months, 12)),
  Part_Year_Months NUMBER
                   GENERATED ALWAYS AS (MOD(total_months, 12)),
  "Month>12"       NUMBER
                   GENERATED ALWAYS AS (GREATEST(total_months-12,0)),
  "Month<=12"      NUMBER
                   GENERATED ALWAYS AS (LEAST(total_months,12))
);

Which, for the sample data:

Insert into TEST3 (TITLE,AMOUNT,Total_Months) values ('10101288',  28000,15);
Insert into TEST3 (TITLE,AMOUNT,Total_Months) values ('10101289',  40000,13);
Insert into TEST3 (TITLE,AMOUNT,Total_Months) values ('10101290',2000000,10);
Insert into TEST3 (TITLE,AMOUNT,Total_Months) values ('10101291',  50000,14);
Insert into TEST3 (TITLE,AMOUNT,Total_Months) values ('10101239',   6000,11);
Insert into TEST3 (TITLE,AMOUNT,Total_Months) values ('10101240',  50000, 8);
Insert into TEST3 (TITLE,AMOUNT,Total_Months) values ('10121003',    690,12);
Insert into TEST3 (TITLE,AMOUNT,Total_Months) values ('12345678',1234567,45);

Then the table contains:

TITLE	AMOUNT	TOTAL_MONTHS	FULL_YEAR_MONTHS	PART_YEAR_MONTHS	Month>12	Month<=12
10101288	28000	15	12	3	3	12
10101289	40000	13	12	1	1	12
10101290	2000000	10	0	10	0	10
10101291	50000	14	12	2	2	12
10101239	6000	11	0	11	0	11
10101240	50000	8	0	8	0	8
10121003	690	12	12	0	0	12
12345678	1234567	45	36	9	33	12

fiddle