sql查询以使用数据透视表动态添加会计月 [英] sql query to dynamically add fiscal month using pivot
问题描述
ALTER PROCEDURE [dbo].[_sp_GetDMActivityTrackerReport]
@CoachId VARCHAR(7),
@Month INT,
@FiscalYear INT
AS
BEGIN
INSERT @FiscalMonth (ID,Month,NbHolidays,MonthDate,TotalDays)
EXECUTE dbo._sp_GetFiscalMonths @Month, @FiscalYear
SELECT 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 tt
INNER JOIN UPreparation up ON tt.PreparationID = up.PreparationID
RIGHT JOIN UTeamMembers utm ON tt.UserId = utm.CoachID AND utm.MemberID = up.UserID
INNER JOIN UserInfo ui ON utm.MemberID = ui.UserID
LEFT JOIN NonFieldTime nft ON nft.UserId = tt.UserId
AND tt.MonthPeriodFrom = nft.MonthPeriodStart
AND 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, MonthDate
1 April 1 4/1/2012
2 May 2 5/1/2012
3 June 3 6/1/2012
4 July 4 7/1/2012
5 August 5 8/1/2012
6 September 6 9/1/2012
7 October 7 10/1/2012
8 November 8 11/1/2012
9 December 9 12/1/2012
10 January 10 1/1/2013
11 February 11 2/1/2013
12 March 12 3/1/2013
我有一个存储过程,可以根据会计年度生成报告. 这里的会计年度和会计月份来自数据库,现在我面临着动态生成此报告的问题,因为您可以看到我已经修正了月份年份,这不是一个好习惯,我希望它以某种方式在我更改了会计年度的情况下生效数据库,然后我的报告将相应地反映出来.
I have a stored procedure which generate report according to the fiscal year. here fiscal year and fiscal month comes from the database now I am facing problem in generating this report dynamically as you can see i had fixed the months year which is not a good practice i want it to some way that if i changed the fiscal month in the database then my report reflect accordingly.
推荐答案
您将需要使用动态SQL来执行此操作. rough 代码将与此类似:
You will need to use dynamic SQL to do this. The rough code is going to be similar to this:
ALTER PROCEDURE [dbo].[_sp_GetDMActivityTrackerReport]
@CoachId VARCHAR(7),
@Month INT,
@FiscalYear INT
AS
BEGIN
INSERT @FiscalMonth (ID,Month,NbHolidays,MonthDate,TotalDays)
EXECUTE dbo._sp_GetFiscalMonths @Month, @FiscalYear
DECLARE @cols AS NVARCHAR(MAX),
@colsNull AS NVARCHAR(MAX),
@colsSum AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(DateName(Month,nft.MonthPeriodStart))
from NonFieldTime nft
where datename(Month,nft.MonthPeriodStart) + '-'+ substring(datename(Year,nft.MonthPeriodStart),3,2)
IN (SELECT Month +'-' +substring(datename(Year,MonthDate),3,2) [Months]
FROM +@FiscalMonth)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
select @colsNull = STUFF((SELECT distinct ', IsNull(' + QUOTENAME(DateName(Month,nft.MonthPeriodStart))+', 0) as '+DateName(Month,nft.MonthPeriodStart)
from NonFieldTime nft
where datename(Month,nft.MonthPeriodStart) + '-'+ substring(datename(Year,nft.MonthPeriodStart),3,2)
IN (SELECT Month +'-' +substring(datename(Year,MonthDate),3,2) [Months]
FROM +@FiscalMonth)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
select @colsSum = STUFF((SELECT distinct '+ IsNull(' + QUOTENAME(DateName(Month,nft.MonthPeriodStart))+', 0)'
from NonFieldTime nft
where datename(Month,nft.MonthPeriodStart) + '-'+ substring(datename(Year,nft.MonthPeriodStart),3,2)
IN (SELECT Month +'-' +substring(datename(Year,MonthDate),3,2) [Months]
FROM +@FiscalMonth)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')+' as [Total Field TIME] '
set @query = 'SELECT PreparationID,CoachId,UserID, MemberID,
[Rep Name], ' + @colsNull + ', '+ @colsSum+'
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 tt
INNER JOIN UPreparation up
ON tt.PreparationID = up.PreparationID
RIGHT JOIN UTeamMembers utm
ON tt.UserId = utm.CoachID AND utm.MemberID = up.UserID
INNER JOIN UserInfo ui
ON utm.MemberID = ui.UserID
LEFT JOIN NonFieldTime nft
ON nft.UserId = tt.UserId
AND tt.MonthPeriodFrom = nft.MonthPeriodStart
AND 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)
) x
pivot
(
sum(Days)
for Month in (' + @cols + ')
) p '
execute(@query)
我的建议而不是使用临时表@FiscalMonth
,而是为此创建一个永久表.使用动态sql时,对一个烫发表而不是临时表进行查询会容易得多.临时表可能超出了动态查询的范围.
My suggestion instead of using the temp table @FiscalMonth
is to create a table that is permanent for this. It will be much simpler to query against a perm table rather than the temp table when using dynamic sql. The temp table might be out of scope for the dynamic query.
这篇关于sql查询以使用数据透视表动态添加会计月的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!