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.
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)
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.
There is PIVOT XML option in Oracle that allows you to avoid hard coding. I'm not sure if there is such option in SQL Server. You can write smth lk this - also Oracle query:
SELECT count(decode(state,'FL',custid)) "FL"
, count(decode(state,'NY',custid)) "NY"
, count(decode(state,'CA',custid)) "CA"
FROM scott.customer
GROUP BY state
/
Replace state with month etc...
Output:
FL NY CA
--- --- ---
0 2 0
5 0 8
Oracle example:
http://www.oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php
SELECT * FROM
(
SELECT product_code, quantity
FROM pivot_test
)
PIVOT XML
(
SUM(quantity) AS sum_quantity
FOR (product_code) IN (SELECT DISTINCT product_code
FROM pivot_test
WHERE id < 10)
)
/
