sql查询以使用数据透视表动态添加会计月

鹤立鸡群的球王

ALTER PROCEDURE [dbo].[_sp_GetDMActivityTrackerReport]@CoachId VARCHAR(7),@Month INT,@FiscalYear INTAS BEGININSERT @FiscalMonth (ID,Month,NbHolidays,MonthDate,TotalDays)EXECUTE dbo._sp_GetFiscalMonths @Month,@FiscalYearSELECT PreparationID,CoachId,UserID,MemberID,[Rep Name],isnull(April,0) April,isnull(May,0) May,isnull(June,0)June,isnull(July,0) July,isnull(August,0) August,isnull(September,0) September,isnull(October,0) October,isnull(November,0) November,isnull(December,0) December,isnull(January,0) January,isnull(February,0) February,isnull(March,0) March,isnull((isnull(November,0)   isnull(December,0)   isnull(January,0)   isnull(February,0)   isnull(March,0)   isnull(April,0)  isnull(May,0)   isnull(June,0)   isnull(July,0)   isnull(August,0)  isnull(September,0)   isnull(October,0)),0) as [Total Field TIME]FROM(SELECT up.PreparationID,tt.UserId [CoachId],up.UserID,utm.MemberID,(ui.FirstName        ui.LastName) AS [Rep Name],DateName(Month,nft.MonthPeriodStart) [Month],sum(nft.Quantity) [Days]FROM TransferedTime ttINNER JOIN UPreparation up ON tt.PreparationID = up.PreparationID RIGHT JOIN UTeamMembers utm ON tt.UserId = utm.CoachID AND utm.MemberID = up.UserIDINNER JOIN UserInfo ui ON utm.MemberID = ui.UserIDLEFT JOIN NonFieldTime nft ON nft.UserId = tt.UserId AND tt.MonthPeriodFrom = nft.MonthPeriodStartAND datename(Month,nft.MonthPeriodStart)   '-'  substring(datename(Year,nft.MonthPeriodStart),3,2) IN (SELECT Month  '-'  substring(datename(Year,MonthDate),3,2) [Months] FROM @FiscalMonth)WHERE utm.MemberID IN (SELECT MemberID FROM UTeamMembers WHERE CoachID = @CoachId)GROUP BY up.PreparationID,tt.UserId,up.UserID,utm.MemberID,(ui.FirstName        ui.LastName),DateName(Month,nft.MonthPeriodStart)) src pivot (sum(Days)for Month in (April,May,June,July,August,September,October,November,December,January,February,March))piv@Fiscalmonth returns:Id,Month,NbHolidays,MonthDate1   April  May 2012年5/1/2012年 3  June  July  August  September October November 8 1/1/20129 December January 1/1/2013111     10 February 2/1/201312  March   12    3/1/20133我有一个存储过程，可以根据会计年度生成报告。这里的会计年度和月份来自数据库。现在我在动态生成报告时遇到了问题，因为你可以看到我已经修改了月份，这不是一个好习惯。我希望它能以某种方式改变我，然后数据库会相应地反映我的报告。
* c, O. a. J) \1 }  H; U, J4 y                                                               
    解决方案: