insert semester in query-CodePudding

in my SQL query there is the column "data_solicitacao". I need to insert in SQL a semester rule where the column "semester 1" or "semester 2" will be automatically filled with the number 1 according to the information of the date "data_solicitacao". The completion of the semester must be automatic according to the current date.

HELP?

CodePudding user response：

You can use a generated column. For example:

create table t (
  registered date,
  semester int generated always as 
    (case when extract(month from registered) <= 6 then 1 else 2 end) stored
);

insert into t (registered) values (date '2022-01-01');
insert into t (registered) values (date '2022-06-30');
insert into t (registered) values (date '2022-07-01');
insert into t (registered) values (date '2022-12-31');

Result:

 registered  semester 
 ----------  -------- 
 2022-01-01  1        
 2022-06-30  1        
 2022-07-01  2        
 2022-12-31  2

See running example at DB Fiddle.

CodePudding user response：

this my code...

image because of sql size]enter image description here

enter image description here