I appreciate all your help. Below is my query and request.
Here is my staging table
CREATE TABLE [Table] (
[WC] nvarchar(255),
[Requestor] nvarchar(255),
[MTR#] nvarchar (255),
[Date Added] nvarchar(255),
[Use Case] nvarchar(255),
[Prog Account] nvarchar(255),
[SB#] nvarchar(255),
[CO#] nvarchar(255),
[Job #] nvarchar(255),
[Need Date] nvarchar(255),
[Totals] nvarchar,
[221] nvarchar(255),
[7-Oct-22] nvarchar(255),
[14-Oct-22] nvarchar(255),
[21-Oct-22] nvarchar(255),
[28-Oct-22] nvarchar(255),
[4-Nov-22] nvarchar(255),
[11-Nov-22] nvarchar(255),
[18-Nov-22] nvarchar(255),
[25-Nov-22] nvarchar(255),
[2-Dec-22] nvarchar(255),
[9-Dec-22] nvarchar(255),
[16-Dec-22] nvarchar(255),
[23-Dec-22] nvarchar(255),
[30-DEC-22] nvarchar(255),
[6-Jan-23] nvarchar(255),
[13-Jan-23] nvarchar(255),
[20-Jan-23] nvarchar(255),
[27-Jan-23] nvarchar(255),
[3-Feb-23] nvarchar(255),
[10-Feb-23] nvarchar(255),
[17-Feb-23] nvarchar(255),
[24-Feb-23] nvarchar(255),
[3-Mar-23] nvarchar(255),
[10-Mar-23] nvarchar(255),
[17-Mar-23] nvarchar(255),
[24-Mar-23] nvarchar(255),
[31-Mar-23] nvarchar(255),
[7-Apr-23] nvarchar(255),
[14-Apr-23] nvarchar(255),
[21-Apr-23] nvarchar(255),
[28-Apr-23] nvarchar(255),
[5-May-23] nvarchar(255),
[12-May-23] nvarchar(255),
[19-May-23] nvarchar(255),
[26-May-23] nvarchar(255))
INSERT INTO [dbo].[Table]
([WC]
,[Requestor]
,[MTR#]
,[Date Added]
,[Use Case]
,[Prog Account]
,[SB#]
,[CO#]
,[Job #]
,[Need Date]
,[Totals]
,[221]
,[7-Oct-22]
,[14-Oct-22]
,[21-Oct-22]
,[28-Oct-22]
,[4-Nov-22]
,[11-Nov-22]
,[18-Nov-22]
,[25-Nov-22]
,[2-Dec-22]
,[9-Dec-22]
,[16-Dec-22]
,[23-Dec-22]
,[30-Dec-22]
,[6-Jan-23]
,[13-Jan-23]
,[20-Jan-23]
,[27-Jan-23]
,[3-Feb-23]
,[10-Feb-23]
,[17-Feb-23]
,[24-Feb-23]
,[3-Mar-23]
,[10-Mar-23]
,[17-Mar-23]
,[24-Mar-23]
,[31-Mar-23]
,[7-Apr-23]
,[14-Apr-23]
,[21-Apr-23]
,[28-Apr-23]
,[5-May-23]
,[12-May-23]
,[19-May-23]
,[26-May-23]
)
VALUES
('TL','XUpgrade','4523XXXX','4/1/2022', 'Road Kit', 'KKK, 1.1, BA.111','','','','', 0,'123456',1,1,1,2,2,3,3,4,4,5,5,6,6,6,1,7,8,8,1,9,4,9,2,0,9,9,0,8,8,8,7,6,6,6),
('TA','XUpgrade-1','14523XXXX','4/11/2022', 'Tool Kit', 'AAA, 1.10, BA.222','90909','SQAre12','meta0909','4/19/2023', 0,'123456',1,1,1,2,2,3,3,4,4,5,5,6,6,6,7,7,4,3,8,9,2,1,9,9,9,9,0,8,8,8,7,6,6,6),
('TK','XUpgrade-22','24523XXXX','4/12/2022', 'Safe Kit', 'QQQ, 1.11, BA.989','909079','Spare12','metaop0909','9/19/2023', 0,'123456',1,1,1,2,2,3,3,4,4,5,5,6,6,6,7,7,8,8,8,2,2,2,2,2,2,2,0,8,8,8,7,6,6,6),
('TG','XUpgrade-22','34523XXXX','4/13/2022', 'No Kit', 'ASD, 1.19, BA.909','909079','parkre12','metaIns0909','8/19/2023', 0,'123456',1,1,1,2,2,3,3,4,4,5,5,6,6,6,7,7,8,8,8,1,2,3,4,9,9,9,0,8,8,8,7,6,6,6),
('AP','XUpgrade-33','44523XXXX','4/4/2022', 'Yes Kit', 'DFG, 1.881, BA.987','909079','kkkre12','metaFB0909','7/19/2023', 0,'123456',1,1,1,2,2,3,3,4,4,5,5,6,6,0,7,7,8,2,8,1,9,2,1,9,1,9,0,8,8,8,7,6,6,6),
('RL','XUpgrade-00','94523XXXX','4/15/2022', 'Car Kit', 'MNB, 1.991, BA.0909','909079','zzAre12','FB0909','4/19/2023', 0,'123456',1,1,1,2,2,0,3,4,4,5,5,6,6,6,7,7,1,8,8,1,1,1,3,0,1,9,0,8,8,8,7,6,6,6)
GO
In the staging table, the first 12 columns are constant every time I load the file into the staging table. The other columns are every Friday of each month from the current month. For example, this month is October 2022, so it will be columns of four Fridays.
I want to sum the data of each row for each month and should populate the data into the destination table.
Below is the expected output in the Destination table.
[WC]|[Requestor]|[MTR#]|[Date Added]|[Use Case]|[Prog Account]|[SB#]|[CO#]|[Job #]|[Need Date]|[Totals]|[221]|Month1|Month2|Month3|Month4|Month5|Month6|Month7|Month8
TL|XUpgrade|4523XXXX|4/1/2022| Road Kit| KKK| 1.1| BA.111||||| 0|123456|5|12|26|22|22|29|24|25
TA|XUpgrade-1|14523XXXX|4/11/2022| Tool Kit| AAA| 1.10| BA.222|90909|SQAre12|meta0909|4/19/2023|0|123456|5|12|26|24|22|37|24|25
TK|XUpgrade-22|24523XXXX|4/12/2022| Safe Kit| QQQ| 1.11| BA.989|909079|Spare12|metaop0909|9/19/2023|0|123456|5|12|26|28|20|10|24|25
TG|XUpgrade-22|34523XXXX|4/13/2022| No Kit| ASD| 1.19| BA.909|909079|parkre12|metaIns0909|8/19/2023|0|123456|5|12|26|28|19|34|24|25
AP|XUpgrade-33|44523XXXX|4/4/2022| Yes Kit| DFG| 1.881| BA.987|909079|kkkre12|metaFB0909|7/19/2023|0|123456|5|12|26|22|20|22|24|25
RL|XUpgrade-00|94523XXXX|4/15/2022| Car Kit| MNB| 1.991| BA.0909|909079|zzAre12|FB0909|4/19/2023|0|123456|5|9|26|21|18|14|24|25
For example if you see the first 4 columns of week for first row: [7-Oct-22], [14-Oct-22], [21-Oct-22], [28-Oct-22] the values are 1,1,1,2 so in the output it should be 5 and the column should be Month0.
In the above example, I have just shared only 7 months, but the actual data I have is rolling 25 months up to Dec 2024. My staging table in November will start from November 2022 columns.
SQL Version : Microsoft SQL Server 2017 (64-bit) on Windows Server 2016 Standard 10.0
Could you please help me and assist on how to build the query for this?