MySQL Forums
Forum List  »  General

Re: Dynamic assignment of month based on current date
Posted by: Arun Chandramouli
Date: April 11, 2022 03:57PM

Hi Peter,

Thanks for your response!...Please find some of the answers below

I understand` `FC1` becomes `apr22` &c, but I do not understand "based on the current date" at all.

Answer: So every day the input table should be compared with the current date and based on the reference table brackets (From and to ) FC1 should be changed to the specific fiscal month with regard to the reference table and the subsequent labels should be added 1 month increments (For example if FC1 becomes May 2022 then FC2 would become June 2022 and then so on)...

"The column names" of what "should change every fiscal month according to the reference table dynamically"?

Answer: So the column names FC1,FC2,FC3 should be changed to CURRENT FISCAL MONTH (example Apr 2022), Current fiscal month +1 (May 2022), Current fiscal month +2 (Jun 2022)..

create table input
(segment varchar(40),
model varchar (40),
FC1 int,
FC2 int,
FC3 int,
FC4 int,
FC5 int,
FC6 int,
FC7 int,
FC8 int,
FC9 int,
FC10 int,
FC11 int,
FC12 int)

insert into input values
('HRX','P3412','9','14','11','22','17','23','18','15','23','12','12','19'),
('SRX','O321','11','8','8','9','9','16','19','7','22','12','11','15'),
('SRX','LD12','14','10','20','22','18','19','10','17','21','16','10','21'),
('HRX','M421','17','18','16','12','14','17','10','16','8','8','7','23'),
('MRX','N342','3','23','16','13','20','9','16','14','16','17','10','11'),
('HRX','J231','4','10','20','20','21','23','17','22','14','15','8','22')

create table output
(segment varchar(40),
model varchar(40),
Apr2022 int,
May2022 int,
Jun2022 int,
jul2022 int,
aug2022 int,
sep2022 int,
oct2022 int,
nov2022 int,
dec2022 int,
Jan2023 int,
feb2023 int,
mar2023 int)

insert into output values
('HRX','P3412','9','14','11','22','17','23','18','15','23','12','12','19'),
('SRX','O321','11','8','8','9','9','16','19','7','22','12','11','15'),
('SRX','LD12','14','10','20','22','18','19','10','17','21','16','10','21'),
('HRX','M421','17','18','16','12','14','17','10','16','8','8','7','23'),
('MRX','N342','3','23','16','13','20','9','16','14','16','17','10','11'),
('HRX','J231','4','10','20','20','21','23','17','22','14','15','8','22')

create table reference
(fiscalmonth varchar(40),
from date,
to date
)

insert into reference values
('Jan 2022','Dec 26 2021 ','Jan 22 2022'),
('Feb 2022','Jan 23 2022','Feb 19 2022'),
('March 2022','feb 20 2022','Mar 26 2022'),
('April 2022','Mar 27 2022','Apr 23 2022'),
('May 2022','Apr 24 2022','May 21 2022'),
('June 2022','May 22 2022','Jun 25 2022'),
('July 2022','June 26 2022','Jul 23 2022'),
('Aug 2022','Jul 24 2022','Aug 20 2022'),
('Sep 2022','Aug 21 2022','Sep 24 2022'),
('Oct 2022','Sep 25 2022','Oct 22 2022'),
('Nov 2022','Oct 23 2022','Nov 19 2022'),
('Dec 2022','Nov 20 2022','Dec 31 2022')

Options: ReplyQuote


Subject
Written By
Posted
Re: Dynamic assignment of month based on current date
April 11, 2022 03:57PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.