我在通过 SQL Server 发送电子邮件时遇到问题。我需要计算运行年度前几个月的分钟数。 问题是电子邮件应该每个月发送一次，并且应该是动态确定的（4 月应该是 1 月、2 月、3 月）。以三月为例，一月和二月。 所有这些都应该是表格形式。 我能够制作一个工作版本，但它不是动态的。

DeclARE
        
        @Message_Body  NVARCHAR(MAX),@subject VARCHAR(255),@recipients VARCHAR(MAX),@copy_recipients VARCHAR(MAX);
        


    SELECT
        @subject = 'Monthly Report',@recipients = 'test@email.com',@copy_recipients = 'test@email.com'

    SELECT
        @Message_Body =
        N'<H2 STYLE="COLOR:BLACK">'+'Monthly Report'+ '</H2>' +
        N'<table border="1">' +
        N'<tr style="background-color: #DCDCDC; color: #000000"><th>User</th>'+
+'<th>'+(SELECT (DATENAME (month,DATEADD(M,-2,getdate()))+' ('+CAST(DATEPART(YEAR,getdate())) AS VARCHAR(MAX))))+')</th>'
+'<th>'+(SELECT (DATENAME (month,-1,getdate())) AS VARCHAR(MAX))))+')</th>'
+N'<th>Total</th>'
+       CAST ( (
            
        SELECT IsnULL([User],'UNKNOWN') as td,'',replace(replace(convert(varchar(20),cast(IsnULL([2],0)/60 as money),1),','),'.00','') as [td],cast(IsnULL([1],''
FROM (
    SELECT SUM(Duration) as Duration,[Months],'Total Minutes' as [User]
    FROM Table_Test 
    GROUP BY [Months]
    ) AS Sourcetable
PIVOT(SUM(Duration) FOR [Months] IN ([1],[2],[3],[4],[5],[6],[7],[8])) AS PivotTable
                FOR XML PATH('tr'),TYPE 
        ) AS NVARCHAR(MAX) ) +
        N'</table>'+
        N'<H2 STYLE="COLOR:BLACK">Thanks</H2>';




        print @Message_Body

    EXEC msdb.dbo.sp_send_dbmail  
        @recipients = @recipients,@copy_recipients = @copy_recipients,@subject = @subject,@body = @Message_Body,@body_format = 'HTML'
END