May 8, 2015 at 3:08 pm
I am struggling to identify where I need to alter my code in order to stop receiving the following error message:
Msg 8156, Level 16, State 1, Line 31
The column ': Score' was specified multiple times for 'piv'.
Msg 8156, Level 16, State 1, Line 5
The column ': Score' was specified multiple times for 'EVAL_PIV'.
Any help would be greatly appreciated.
~Ryan
-----------------------------------------------------------------------------------------------------------------------------
DECLARE @cols as NVARCHAR(MAX), @cols2 as NVARCHAR(MAX), @query as NVARCHAR(MAX),
@t as DATETIME, @evals as NVARCHAR(MAX),
@notescols as NVARCHAR(MAX),
@notescols2 as NVARCHAR(MAX),
@notesquery as NVARCHAR(MAX),
@queue as NVARCHAR(MAX),
@queue2 as NVARCHAR(MAX)
-----------------------------------------------------------------------------------------------------------------------------
DECLARE @Now DATETIME
DECLARE @Today DATETIME
DECLARE @FirstDayPreviousMonth DATETIME
DECLARE @LastDayPreviousMonth DATETIME
DECLARE @FirstDayCurrentMonth DATETIME
DECLARE @LastDayCurrentMonth DATETIME
DECLARE @FirstDayNextMonth DATETIME
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @StartDate2 as NVARCHAR(MAX)
DECLARE @EndDate2 as NVARCHAR(MAX)
SELECT @Now = GETDATE()
SELECT @Today = CONVERT(VARCHAR(25),GETDATE(),101)
SELECT @FirstDayPreviousMonth = CONVERT(VARCHAR(25),DATEADD(MONTH, DATEDIFF(MONTH, -1,GETDATE())-2,0))
SELECT @LastDayPreviousMonth = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())),GETDATE()),101)
SELECT @FirstDayCurrentMonth = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101)
SELECT @LastDayCurrentMonth = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101)
SELECT @FirstDayNextMonth = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,1,GETDATE())),101)
SELECT @StartDate= @FirstDayPreviousMonth
SELECT @EndDate= @Today
-----------------------------------------------------------------------------------------------------------------------------
--Select @StartDate= '2015-04-01'
--Select @EndDate= '2015-06-01'
--If another date range is need beyond Previous Month + MTD, change these dates
--select @startdate as START_DATE, @enddate as END_DATE;
--run the above select command with the prior declare and select statements to check start/end dates
-----------------------------------------------------------------------------------------------------------------------------
SELECT @StartDate2= QUOTENAME(CONVERT(VARCHAR(MAX),@StartDate,20),'''')
SELECT @EndDate2= QUOTENAME(CONVERT(VARCHAR(MAX),@EndDate, 20),'''')
-----------------------------------------------------------------------------------------------------------------------------
select @evals =
STUFF((SELECT ',' + QUOTENAME(ievalid,'''') from (
select distinct ievalid from vwNiceDBKitEvaluationQuestions
where DATEADD(hh,-5,dtCreationTime) between @StartDate and @EndDate
and nvccaption like '%queue is the associate supporting%'
and nvcquestionanswer = @queue
) b
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
select @cols =
STUFF((SELECT ',' + QUOTENAME(FORM_QUESTION) from (
select FORM_QUESTION from (
select top 100000000000 FORM_QUESTION
from (select distinct case when nvcSectionCaption is null then left(nvcCaption,100) else (nvcSectionCaption+': '+left(nvcCaption,100)) end FORM_QUESTION, vcHierarchy
from vwNiceDBKitEvaluationQuestions where nvcFormName like '%QA Data%'
and DATEADD(hh,-5,dtCreationTime) between @StartDate and @EndDate and nvcCaption <> 'Popup Note'
and nvcCaption is not null and nvcCaption <> ''and nvcQuestionAnswer is not null and nvcQuestionAnswer <> '') t
group by FORM_QUESTION, vcHierarchy
order by vcHierarchy, FORM_QUESTION) A
) b
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
select @cols2 = STUFF((SELECT ',' + QUOTENAME(FORM_QUESTION,'''') from (
select distinct FORM_QUESTION from (
select top 100000000000 FORM_QUESTION
from (select distinct case when nvcSectionCaption is null then left(nvcCaption,100) else (nvcSectionCaption+': '+left(nvcCaption,100)) end FORM_QUESTION, vcHierarchy
from vwNiceDBKitEvaluationQuestions where nvcFormName like '%QA Data%'
and DATEADD(hh,-5,dtCreationTime) between @StartDate and @EndDate and nvcCaption <> 'Popup Note'
and nvcCaption is not null and nvcCaption <> ''and nvcQuestionAnswer is not null and nvcQuestionAnswer <> '') t
group by FORM_QUESTION, vcHierarchy
order by vcHierarchy, FORM_QUESTION) A
) b
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
select @notescols =
STUFF((SELECT ',' + QUOTENAME(FORM_QUESTION) from (
select FORM_QUESTION from (
select top 100000000000 FORM_QUESTION
from (select distinct ('Notes - '+nvcSectionCaption+': '+left(ncvreportlabel,100)) as FORM_QUESTION, vcHierarchy
from vwNiceDBKitEvaluationQuestions where nvcFormName like '%QA Data%'
and DATEADD(hh,-5,dtCreationTime) between @StartDate and @EndDate
and
(nvcCaption = 'Popup Note')
and nvcCaption is not null and nvcCaption <> '' and nvcQuestionAnswer is not null and nvcQuestionAnswer <> '') t
group by FORM_QUESTION, vcHierarchy
order by vcHierarchy, FORM_QUESTION
) A
) b
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
select @notescols2 = STUFF((SELECT ',' + QUOTENAME(FORM_QUESTION,'''') from (
select FORM_QUESTION from (
select top 100000000000 FORM_QUESTION
from (select distinct ('Notes - '+nvcSectionCaption+': '+left(ncvreportlabel,100)) as FORM_QUESTION, vcHierarchy
from vwNiceDBKitEvaluationQuestions where nvcFormName like '%QA Data%'
and DATEADD(hh,-5,dtCreationTime) between @StartDate and @EndDate
and
(nvcCaption = 'Popup Note')
and nvcCaption is not null and nvcCaption <> '' and nvcQuestionAnswer is not null and nvcQuestionAnswer <> '') t
group by FORM_QUESTION, vcHierarchy
order by vcHierarchy, FORM_QUESTION
) A
) b
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')
--and iEvalID in (' + @evals + ')
set @query =
'with
EVAL_PIV as
(
select * from (
select ievalid, QUESTION_NAME, nvcQuestionAnswer
from
(select a.*,
case when nvcSectionCaption is null then left(nvcCaption,100) else (nvcSectionCaption+'': ''+left(nvcCaption,100)) end as QUESTION_NAME
from vwNiceDBKitEvaluationQuestions a) a
where nvcFormName like ''%QA Data%'' and
QUESTION_NAME
in (' + @cols2 + ')
and DATEADD(hh,-5,dtCreationTime) > ' + @StartDate2 + '
and DATEADD(hh,-5,dtCreationTime) < ' + @EndDate2 + '
and nvcCaption is not null and nvcCaption <> ''''
and nvcQuestionAnswer is not null and nvcQuestionAnswer <> '''' and nvcCaption <> ''Popup Note''
) a
pivot (max (nvcquestionanswer) for
QUESTION_NAME
in(' + @cols + '))piv),
EVAL_META as
(select distinct iEvalID, nvcFormName, iEvaluatorUserID, iAgentUserID, dtCreationTime, dtEvaluatedStartTime, iEvaluatedDuration, flScore, flmaxscore
from vwNiceDBKitEvaluationQuestions where nvcFormName like ''%QA Data%''
and DATEADD(hh,-5,dtCreationTime) between ' + @StartDate2 + ' and ' + @EndDate2 + '),
AGENTS as
(
select distinct
a.iuserid,
a.nvcSwitchAgentId as Associate_ID,
a.nvcLastName as Associate_Last_Name,
a.nvcFirstName as Associate_First_Name,
--a.TIER_1_GROUP,
--a.TIER_2_GROUP,
a.TIER_3_GROUP as Manager,
a.TIER_4_GROUP as Coach
from (
select c.iuserid, c.nvcSwitchAgentId, c.nvcLastName, c.nvcFirstName,
a.TIER_1_GROUP, a.TIER_2_GROUP, a.TIER_3_GROUP, a.TIER_4_GROUP,
DENSE_RANK() OVER(partition by c.nvcSwitchAgentId order by a.TIERS desc, a.LOWEST_ID asc) rk
from (select * from tblAgent where iagentid not in (''9999999'')) c
left outer join tblGroupMembers b on c.iUserId = b.iUserID
left outer join (select
case when d.iGroupID is not null then 4 when c.iGroupID is not null then 3 when b.iGroupID is not null then 2 when a.iGroupID is not null then 1 else 0 end as ''TIERS'',
case when d.iGroupID is not null then d.iGroupID when c.iGroupID is not null then c.iGroupID when b.iGroupID is not null then b.iGroupID when a.iGroupID is not null then a.iGroupID
else NULL end as ''LOWEST_ID'',
a.iGroupID TIER_1_GROUPID, a.nvcGroupName TIER_1_GROUP, b.iGroupID TIER_2_GROUPID, b.nvcGroupName TIER_2_GROUP, c.iGroupID TIER_3_GROUPID, c.nvcGroupName TIER_3_GROUP, d.iGroupID TIER_4_GROUPID, d.nvcGroupName TIER_4_GROUP
from (select * from vwNiceDBKitOrganizationGroup where iParentGroupID = ''7'') a
left outer join vwNiceDBKitOrganizationGroup b on a.iGroupID = b.iParentGroupID
left outer join vwNiceDBKitOrganizationGroup c on b.iGroupID = c.iParentGroupID
left outer join vwNiceDBKitOrganizationGroup d on c.iGroupID = d.iParentGroupID
) a on a.LOWEST_ID = b.iGroupID
) a where nvcSwitchAgentId is not null and rk = 1),
REGDATA as (
select
dateadd(m, datediff(m, 0, DATEADD(hh,-5,dtCreationTime)), 0) as EVAL_MONTH,
--dateadd(dd, 1 - datepart(dw, cast(floor(cast(DATEADD(hh,-5,A.dtCreationTime) as float)) as datetime)),
--cast(floor(cast(DATEADD(hh,-5,A.dtCreationTime) as float)) as datetime)) as EVAL_WEEK_STARTING,
cast(floor(cast(DATEADD(hh,-5,A.dtCreationTime)as float)) as datetime) as EVAL_DATE,
DATEADD(HH,-5,A.dtCreationTime) as EVAL_TIME,
dateadd(m, datediff(m, 0, DATEADD(hh,-5,dtEvaluatedStartTime)), 0) as CALL_MONTH,
--dateadd(dd, 1 - datepart(dw, cast(floor(cast(DATEADD(hh,-5,A.dtEvaluatedStartTime) as float)) as datetime)),
--cast(floor(cast(DATEADD(hh,-5,A.dtEvaluatedStartTime) as float)) as datetime)) as CALL_WEEK_STARTING,
cast(floor(cast(DATEADD(hh,-5,A.dtEvaluatedStartTime)as float)) as datetime) as CALL_DATE,
DATEADD(HH,-5,A.dtEvaluatedStartTime) as CALL_TIME,
C.*,
a.nvcFormName as Form_Name,
a.iEvaluatedDuration as Call_Duration,
--a.flScore as Score,
--a.flmaxscore as Max_Score,
B.*
from EVAL_META A LEFT OUTER JOIN EVAL_PIV B ON A.IEVALID = B.IEVALID
LEFT OUTER JOIN AGENTS C on A.IAGENTUSERID = C. IUSERID
),
NOTESDATA as (
select * from (
select ievalid, QUESTION_NAME, nvcQuestionAnswer
from
(select a.*,
(''Notes - ''+nvcSectionCaption+'': ''+left(ncvreportlabel,100)) as QUESTION_NAME
from vwNiceDBKitEvaluationQuestions a) a
where nvcFormName like ''%QA Data%'' and
QUESTION_NAME
in (' + @notescols2 + ')
and DATEADD(hh,-5,dtCreationTime) > ' + @StartDate2 + '
and DATEADD(hh,-5,dtCreationTime) < ' + @EndDate2 + '
and nvcCaption is not null and nvcCaption <> ''''
and nvcQuestionAnswer is not null and nvcQuestionAnswer <> ''''
and
(nvcCaption = ''Popup Note'' or
(nvcCaption in (''What queue is the associate supporting for this evaluation?'',''What center is the associate supporting for this evaluation?'') and tiQuestionType = ''4'')
)
) a
pivot (max (nvcquestionanswer) for
QUESTION_NAME
in(' + @notescols + '))piv)
select a.*, b.* from REGDATA a left outer join NOTESDATA b on a.ievalid = b.ievalid
;
'
--select @query
execute sp_executesql @query;
May 8, 2015 at 3:12 pm
It would help if we could see the actual query you are generating dynamically as I am assuming that it the query that is failing, not the query that generates the query.
May 8, 2015 at 3:16 pm
What I have posted above is exactly what I am running. Is there something specific that I am missing?
~Ryan
May 8, 2015 at 3:20 pm
rirons04 (5/8/2015)
What I have posted above is exactly what I am running. Is there something specific that I am missing?~Ryan
--select @query
execute sp_executesql @query; <<--- This runs the dynamic query that is built by the code you run.
That is most likely where your code is failing. The code you run doesn't tell us the code it runs.
May 8, 2015 at 3:28 pm
rirons04 (5/8/2015)
What I have posted above is exactly what I am running. Is there something specific that I am missing?~Ryan
Run your code again, but before you do uncomment this line.
--select @query
Then post the results of that variable. Then we can see the actual query you are running. Without this we are just guessing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply