SQL Server Make SQL column names dynamic to change with month to month date changes?

fykwrbwg 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(99)

I am trying to improve a legacy code that is calculating monthly collections. Currently I delete the oldest month and create a new month to reflect the most recent month (e.g. cut june 2022 and make the new column june 2023). The current relevant segment of code looks like this:

SUM(CASE WHEN dd.YearMonth = '2022-08' THEN [Collections]   ELSE 0 END) AS Aug22_Collections,
    SUM(CASE WHEN dd.YearMonth = '2022-09' THEN [Collections]   ELSE 0 END) AS Sep22_Collections,
    SUM(CASE WHEN dd.YearMonth = '2022-10' THEN [Collections]   ELSE 0 END) AS Oct22_Collections,
    SUM(CASE WHEN dd.YearMonth = '2022-11' THEN [Collections]   ELSE 0 END) AS Nov22_Collections,
    SUM(CASE WHEN dd.YearMonth = '2022-12' THEN [Collections]   ELSE 0 END) AS Dec22_Collections,
    SUM(CASE WHEN dd.YearMonth = '2023-01' THEN [Collections]   ELSE 0 END) AS Jan23_Collections,
    SUM(CASE WHEN dd.YearMonth = '2023-02' THEN [Collections]   ELSE 0 END) AS Feb23_Collections,
    SUM(CASE WHEN dd.YearMonth = '2023-03' THEN [Collections]   ELSE 0 END) AS Mar23_Collections,
    SUM(CASE WHEN dd.YearMonth = '2023-04' THEN [Collections]   ELSE 0 END) AS Apr23_Collections,
    SUM(CASE WHEN dd.YearMonth = '2023-05' THEN [Collections]   ELSE 0 END) AS May23_Collections,
    SUM(CASE WHEN dd.YearMonth = '2023-06' THEN [Collections]   ELSE 0 END) AS Jun23_Collections,
    SUM(CASE WHEN dd.YearMonth = '2023-07' THEN [Collections]   ELSE 0 END) AS Jul23_Collections,

This sets up columns for my temp table with each column corresponding to a new month.

My new goal is for the column names to change names dynamically so that I no longer need to cut and paste and re-label this section. I know I'll need to change the "dd.YearMonth = '2023-07'" section and the "AS Jul23_Collections" part, but I'm unsure how to do so.

I assume that I could set variables like:

DECLARE @Month1 INT = YEAR(DATEADD(MM,-1,GETDATE())) * 100 + MONTH(DATEADD(MM,-1,GETDATE()))

and

DECLARE @Month2 INT = YEAR(DATEADD(MM,-2,GETDATE())) * 100 + MONTH(DATEADD(MM,-2,GETDATE()))

But I don't know if that can be used for the actual column name itself. That's my biggest struggle. Ideally I want each column name to be the correct corresponding month and then "_Collectons".

I tried to set start and end date variables, but this didn't change column names, which is my bigger problem currently.

sql-server

来源：https://stackoverflow.com/questions/76763314/make-sql-column-names-dynamic-to-change-with-month-to-month-date-changes

1条答案

按热度按时间

r7xajy2e1#

Declare variables for the start and end date for your dynamic column names:

DECLARE @StartDate DATE = DATEADD(MONTH, -11, GETDATE()); -- 12 months ago from 
the current date
DECLARE @EndDate DATE = GETDATE(); -- Current date

Create a variable to hold the dynamic SQL statement:

DECLARE @DynamicSQL NVARCHAR(MAX);
 SET @DynamicSQL = N'SELECT ';

Use a loop to generate the dynamic column names and corresponding sums for each month:

DECLARE @CurrentDate DATE = @StartDate;
WHILE @CurrentDate <= @EndDate
BEGIN
SET @DynamicSQL += N'SUM(CASE WHEN dd.YearMonth = ''' + CONVERT(NVARCHAR(7), @CurrentDate, 126) + N''' THEN [Collections] ELSE 0 END) AS ' +
UPPER(FORMAT(@CurrentDate, 'MMMyy')) + N'_Collections, ';

SET @CurrentDate = DATEADD(MONTH, 1, @CurrentDate); -- Move to the next month
END

Remove the trailing comma from the dynamic SQL statement:

SET @DynamicSQL = LEFT(@DynamicSQL, LEN(@DynamicSQL) - 1);

Append the rest of your SQL query to the dynamic SQL, Assuming the rest of your query looks like:

SET @DynamicSQL += N'
FROM YourTable dd
WHERE /* Your conditions */
GROUP BY /* Your grouping columns */
ORDER BY /* Your ordering */
';

Execute the dynamic SQL:

EXEC sp_executesql @DynamicSQL;

**Note:**The above code will generate a dynamic SQL query with columns for each month from 12 months ago up to the current month. The columns will be named dynamically based on the month and year, following the format "MMMyy_Collections". Be sure to adjust the table name (YourTable) and add the appropriate conditions, grouping, and ordering as needed for your specific query.

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Make SQL column names dynamic to change with month to month date changes?

1条答案

相关问题

热门标签

最新问答