Dynamic assignment of month based on current date

Transact-SQL
1

Hi All,

Hope you are doing well!...I am trying to convert the plan table as below

Input

Segment Model FC1 FC2 FC3 FC4 FC5 FC6 FC7 FC8 FC9 FC10 FC11 FC12
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

into the table below based on the current date and the reference table

Segment Model Apr-22 May-22 Jun-22 Jul-22 Aug-22 Sep-22 Oct-22 Nov-22 Dec-22 Jan-23 Feb-23 Mar-23
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

Reference table:

Fiscal Month From to
Jan-22 Dec 26 2021 Jan 22 2022
Feb-22 Jan 23 2022 19-Feb-22
Mar-22 20-Feb-22 26-Mar-22
Apr-22 27-Mar-22 23-Apr-22
May-22 24-Apr-22 21-May-22
Jun-22 22-May-22 25-Jun-22
Jul-22 26-Jun-22 23-Jul-22
Aug-22 24-Jul-22 20-Aug-22
Sep-22 21-Aug-22 24-Sep-22
Oct-22 25-Sep-22 22-Oct-22
Nov-22 23-Oct-22 19-Nov-22
Dec-22 20-Nov-22 31-Dec-22

So I need to basically map the column names (FC1,FC2,FC3...input table) to fiscal month based on the current date and looking up the reference table for the fiscal month... I have attached the excel data for the above too...Can you please help me with the same..

The column names should change every fiscal month according to the reference table dynamically ..For example FC1 should be renamed to May 2022 and FC2 should be renamed to June 2022 from 24th April 2022...Similarly from 22nd may 2022 FC1 should be renamed to June 2022, FC2 should be renamed to July 2022...

Please find the DDL for the tables

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')

2

You need to provide directly usable data -- CREATE TABLE and INSERT statement(s) -- for the Reference table, at least.

3

@ScottPletcher : Yes I have provided the DDL statements...Sorry for the delay...

4 

DECLARE @sql varchar(max)

;WITH cte_find_current_month AS (
    SELECT TOP (1) *
    FROM reference
    WHERE CAST(GETDATE() AS date) BETWEEN [from] AND [to]
),
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally100 AS (
    SELECT 0 AS number UNION ALL
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2
)

SELECT @sql = STUFF((SELECT '; ' +
    'EXEC sys.sp_rename ''dbo.Input.FC' + CAST(t.number + 1 AS varchar(2)) + ''', ' + 
        '''' + STUFF(CONVERT(varchar(20), DATEADD(MONTH, t.number, fcm.[to]), 7), 4, 5, '-') + ''', ' +
        '''COLUMN''' 
    FROM cte_find_current_month fcm 
    INNER JOIN cte_tally100 t ON t.number BETWEEN 0 AND 11
    ORDER BY t.number
    FOR XML PATH(''), TYPE).value('.', 'varchar(8000)'), 1, 2, '')

PRINT @sql
--EXEC(@sql)
5

Can you explain why you need the column names to change? If this is going to be consumed by SSRS (for example) - it would be much easier to change the column names in the actual report instead of trying to change them in SQL.

As Scott has shows - you can use some dynamic SQL to rename the columns, or you could use dynamic SQL to cross-tab the data but it can be quite complicated.

6

Hi @ScottPletcher , Thank you so much for your kind help!!...Really appreciate it :smiley: ...I am trying to understand the following statements ..Can you please help me there...

SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)

STUFF((SELECT '; ' +
'EXEC sys.sp_rename ''dbo.Input.FC' + CAST(t.number + 1 AS varchar(2)) + ''', ' +
'''' + STUFF(CONVERT(varchar(20), DATEADD(MONTH, t.number, fcm.[to]), 7), 4, 5, '-') + ''', ' +
'''COLUMN'''

7

The first part creates a "tally table". A tally table contains a single numeric column, incrementing in values from 0 or 1 to the highest value needed.

It's a technique for preventing looping. Rather than loop a certain number of times, a tally table JOINs that number of times.

In the code above, the tally table is used to be able to go thru "FC1", "FC2", ..., "FC12" and generate and concatenate code to rename those columns without having to do a WHILE loop or other type of loop to do it.

Look at the SQL that is generated and you will see what I mean. It's a string of rename commands all concatenated into one long string so it can be executed.

8

Thank you so much!..Appreciate your response!!