July 21, 2011 at 1:48 am
Im trying to create a pivot table by week for users based on activity and cannot get the pivot table to return one row per consultant per activity and hoping someone can tell me where im going wrong please!
I have the following tables:
CalendarWeek[/u]
SalesCalendarWeekPeriodId CalendarWeek Zero Column
2167 WK29 15/7/20110
2165 WK27 1/7/20110
2168 WK30 22/7/20110
2166 WK28 8/7/20110
2164 WK26 24/6/20110
KPITasks2[/u]
UserName Indicator Tasks Period
Suzie Smith 1st Interview with Client1WK28 8/7/2011
Suzie Smith 1st Interview with Client2WK29 15/7/2011
Charlotte Brown 1st Interview with Client1WK28 8/7/2011
Catherine Jones 1st Interview with Client4WK28 8/7/2011
and am running the following code to put this into a pivot table pivoting on the calendar week:
DECLARE @cols NVARCHAR(2000)
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + CW.CalendarWeek
FROM CalendarWeek AS cw
ORDER BY '],[' + CW.CalendarWeek
FOR XML PATH('')
), 1, 2, '') + ']'
DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT DISTINCT Indicator,UserName, '+
@cols +'
INTO KPITasks3
FROM
(SELECT CW.SalesCalenderWeekPeriodId
, KP.Indicator
, KP.UserName
, CW.CalendarWeek
, SUM(Tasks) AS Tasks
FROM KPITasks2 AS KP INNER JOIN CalendarWeek AS CW ON KP.Period = CW.CalendarWeek
GROUP BY SalesCalenderWeekPeriodId,ZeroColumn,Indicator,UserName,CalendarWeek) p
PIVOT
(
SUM([Tasks])
FOR CalendarWeek IN
( '+
@cols +' )
) AS pvt
'
EXECUTE(@query)
however this is created multiple lines for each user :s
Indicator UserName WK26 24/6/2011 WK27 1/7/2011 WK29 15/7/2011 WK30 22/7/2011
Client Call - Had ConversationSuzie SmithNULLNULLNULL9NULL
Client Call - Had ConversationSuzie SmithNULL3NULLNULLNULL
Client Call - Had ConversationSuzie Smith12NULLNULLNULLNULL
Client Call - Left MessageSuzie SmithNULLNULLNULL6NULL
Client Call - Left MessageSuzie Smith2NULLNULLNULLNULL
What I would like is one line per User with 0 in any NULL columns, but cannot see how to get this?
Indicator UserName WK26 24/6/2011 WK27 1/7/2011 WK29 15/7/2011 WK30 22/7/2011
Client Call - Had ConversationSuzie Smith123090
Client Call - Left Message Suzie Smith20060
Can anyone help please? Thanks a lot 🙂
July 21, 2011 at 4:12 am
is ok solved it with SUM(ISNULL)) in the DECLARE @cols statement 🙂
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply