December 10, 2008 at 3:23 pm
Hello Experts,
I am new to SQL server programming. I have a Nested loop in the Stored procedure which runs fine but these nested loop will have to process arround 100 thousand record on daily basis the elapsed time of that loops is arround 6 to 7 hours. Can anybody help me how to optimize the performance of the loops please. here is my code
Declare @lblCount1 int
DEclare @lblCount2 int
Declare @lblCount3 int
Declare @cnt varchar(100)
DEclare @Qlbl varchar(100)
DEclare @Que varchar(100)
Declare @MaxQNumber int
Declare @QOrderMax int
Declare @RowDiff varchar(100)
Declare @totAnswerID int
Declare @MaxAnsCount int
Declare @AnsCount int
Declare @EventType int
Declare @totEventID int
SET @MaxAnsCount = (Select Max(ID) From #tmpAnswerID)
SET @AnsCount = 1
set @cnt = 1
SET @lblCount1 = (Select count(*) from #tmpQLabels with(nolock) where eventtypeid = 1 and question_number is not null and q_order is not null)
SET @lblCount2 = (Select count(*) from #tmpQLabels with(nolock) where eventtypeid = 2 and question_number is not null and q_order is not null)
SET @lblCount3 = (Select count(*) from #tmpQLabels with(nolock) where eventtypeid = 3 and question_number is not null and q_order is not null)
select * from #tmpAnswerID
WHILE @AnsCount <= @MaxAnsCount
BEGIN
SET @totAnswerID = (Select Answer_ID from #tmpAnswerID with(nolock) where ID = @AnsCount)
SET @totEventID = (Select EventType from #tmpAnswerID with(nolock) where ID = @AnsCount)
IF @totAnswerID is not null
BEGIN
if @totEventID = 1
BEGIN
WHILE @cnt <= @lblCount1
BEGIN
SET @Qlbl = 'Q'+@cnt
SET @Que = (SELECT position from tblPivotAnswers with(nolock) where event_type_id = 1 and Answer_ID = @totAnswerID and position = @Qlbl)
if @Que is null
BEGIN
SET @RowDiff = @cnt - 1
SET @MaxQNumber = (Select question_number from tblPivotAnswers with(nolock) where event_type_id = 1 and Answer_ID = @totAnswerID and position = 'Q'+@RowDiff)
SET @QOrderMax = (Select q_order from tblPivotAnswers with(nolock) where event_type_id = 1 and Answer_ID = @totAnswerID and position = 'Q'+@RowDiff)
INSERT INTO tblPivotAnswers
VALUES('', @totAnswerID, '', 9999, 0, @MaxQNumber + 1, 0, @Qlbl, 1)
-- print(@Qlbl)
END
SET @cnt = @cnt + 1
END
END
if @totEventID = 2
BEGIN
WHILE @cnt <= @lblCount2
BEGIN
SET @Qlbl = 'Q'+@cnt
SET @Que = (SELECT position from tblPivotAnswers with(nolock) where event_type_id = 2 and Answer_ID = @totAnswerID and position = @Qlbl)
if @Que is null
BEGIN
SET @RowDiff = @cnt - 1
SET @MaxQNumber = (Select question_number from tblPivotAnswers with(nolock) where event_type_id = 2 and Answer_ID = @totAnswerID and position = 'Q'+@RowDiff)
SET @QOrderMax = (Select q_order from tblPivotAnswers with(nolock) where event_type_id = 2 and Answer_ID = @totAnswerID and position = 'Q'+@RowDiff)
INSERT INTO tblPivotAnswers
VALUES('', @totAnswerID, '', 9999, 0, @MaxQNumber + 1, 0, @Qlbl, 2)
-- print(@Qlbl)
END
SET @cnt = @cnt + 1
END
END
if @totEventID = 3
BEGIN
WHILE @cnt <= @lblCount3
BEGIN
SET @Qlbl = 'Q'+@cnt
SET @Que = (SELECT position from tblPivotAnswers with(nolock) where event_type_id = 3 and Answer_ID = @totAnswerID and position = @Qlbl)
if @Que is null
BEGIN
SET @RowDiff = @cnt - 1
SET @MaxQNumber = (Select question_number from tblPivotAnswers with(nolock) where event_type_id = 3 and Answer_ID = @totAnswerID and position = 'Q'+@RowDiff)
SET @QOrderMax = (Select q_order from tblPivotAnswers with(nolock) where event_type_id = 3 and Answer_ID = @totAnswerID and position = 'Q'+@RowDiff)
INSERT INTO tblPivotAnswers
VALUES('', @totAnswerID, '', 9999, 0, @MaxQNumber + 1, 0, @Qlbl, 3)
-- print(@Qlbl)
END
SET @cnt = @cnt + 1
END
END
END
SET @cnt = 1
SET @AnsCount = @AnsCount + 1
END
December 10, 2008 at 3:49 pm
Boy that code is a bit of a mess. Is there any chance you could distill down what you are trying to do with the tables?
It would seem that you could attack this in a more set oriented fashion by joining tables together and then extracting the data you need.
you have a few things not defined there as well, tables, temp tables, so it's hard to know if this can be improved.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 10, 2008 at 3:56 pm
In SQL Server, the best way to optimize loops is to replace them with Set/Table operations.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 10, 2008 at 4:06 pm
Also, it would be helpful if you could supply the Table DDLs (just script them out, if necessary) and some sample data.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 10, 2008 at 4:08 pm
Hi Steve,
Thank you for your reply. Sorry i didnt copied the whole stored procedure because the stored procedure is bit lengthy. Please find the procedure below.
ALTER PROCEDURE [dbo].[fs_1_USP_Monthly_XML_LOADER]
AS
BEGIN
-- Extracting the values from Question/Answers XML and Pivoting to create a table QuestionsOutPut
Create Table #tmp_quewers
(
Answer_ID int,
survey_id int,
event_type_id int,
survey_date datetime,
Question_ID int,
QuestionValue_ID int,
Score int,
cust_comments nvarchar(max)
)
begin
with xmlnamespaces (default 'http://foresight8.vitalinsightgroup.com/Answers.xsd')
Insert #tmp_quewers
SELECT
Foresight_v8_Instance_BMW_DATA.dbo.fs_tbl_answer.Answer_ID as AnswerID,
Foresight_v8_Instance_BMW_DATA.dbo.fs_tbl_answer.survey_id,
Foresight_v8_Instance_BMW_DATA.dbo.fs_tbl_answer.event_type_id,
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey.created_date,
Colx.value( './@questionId', 'int' ) as Question_ID,
Colx.value( './@questionValueId', 'int' ) as QuestionValue_ID,
Colx.value( './@score', 'int' ) as Score,
Colx.value( './Comments', 'nvarchar(max)' ) as cust_comments
FROM
Foresight_v8_Instance_BMW_DATA.dbo.fs_tbl_answer with(nolock) INNER JOIN Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey with(nolock) ON
Foresight_v8_Instance_BMW_DATA.dbo.fs_tbl_answer.survey_id = Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey.survey_id
and Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey.status = 'A'
CROSS APPLY
answers.nodes('/Answers/Answer') AS Tabx(Colx)
end
Create Table #tmp_Questions
(
question_id int,
survey_id int,
q_order int,
question_number int,
event_type_id int,
survey_date datetime,
optional bit,
question nvarchar(max)
)
begin
with xmlnamespaces (default 'http://foresight8.vitalinsightgroup.com/MultiLanguage.xsd')
Insert #tmp_Questions
SELECT DISTINCT
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.question_id as question_id,
#tmp_quewers.survey_id as survey_id,
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.q_order as q_order,
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.question_number as question_number,
#tmp_quewers.event_type_id as event_type_id,
#tmp_quewers.survey_date,
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.optional as optional,
Colq.value( '/', 'nvarchar(max)' ) as question
FROM
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question with(nolock) left join #tmp_quewers with(nolock)
ON Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.question_id = #tmp_quewers.question_id and Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.deleted = 0 AND Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.optional = 0
CROSS APPLY
question.nodes('/MultiLanguageText/Language') AS Taby(Colq)
end
--select DISTINCT question_id, question, q_order, event_type_id, survey_id, survey_date from #tmp_Questions WHERE event_type_id = 3 order by survey_date desc
create table #tmp_survey(survey_id int, event_type_id int)
INSERT INTO #tmp_survey
select top 1 survey_id, event_type_id from #tmp_Questions with(nolock) where event_type_id = 1 order by survey_date desc
INSERT INTO #tmp_survey
select top 1 survey_id, event_type_id from #tmp_Questions with(nolock) where event_type_id = 2 order by survey_date desc
INSERT INTO #tmp_survey
select top 1 survey_id, event_type_id from #tmp_Questions with(nolock) where event_type_id = 3 order by survey_date desc
INSERT INTO #tmp_survey
select top 1 survey_id, event_type_id from #tmp_Questions with(nolock) where event_type_id = 4 order by survey_date desc
begin
with xmlnamespaces (default 'http://foresight8.vitalinsightgroup.com/MultiLanguage.xsd')
Insert #tmp_Questions
SELECT DISTINCT
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.question_id as question_id,
(select top 1 survey_id from #tmp_survey inn where event_type_id = 1) as survey_id,
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.q_order as q_order,
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.question_number as question_number,
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey.event_type_id as event_type_id,
'' as survey_date,
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.optional as optional,
Colq.value( '/', 'nvarchar(max)' ) as question
FROM
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question with(nolock)
INNER JOIN Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey_question with(nolock) ON Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.question_id = Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey_question.question_id
AND Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.optional = 1 and Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.deleted = 0
INNER JOIn
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey with(nolock) ON Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey_question.survey_id = Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey.survey_id AND Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey.event_type_id = 1
CROSS APPLY
question.nodes('/MultiLanguageText/Language') AS Taby(Colq)
end
begin
with xmlnamespaces (default 'http://foresight8.vitalinsightgroup.com/MultiLanguage.xsd')
Insert #tmp_Questions
SELECT DISTINCT
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.question_id as question_id,
(select top 1 survey_id from #tmp_survey where event_type_id = 2) as survey_id,
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.q_order as q_order,
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.question_number as question_number,
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey.event_type_id as event_type_id,
'' as survey_date,
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.optional as optional,
Colq.value( '/', 'nvarchar(max)' ) as question
FROM
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question with(nolock)
INNER JOIN Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey_question with(nolock) ON Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.question_id = Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey_question.question_id
AND Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.optional = 1
INNER JOIn
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey with(nolock) ON Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey_question.survey_id = Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey.survey_id AND Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey.event_type_id = 2
CROSS APPLY
question.nodes('/MultiLanguageText/Language') AS Taby(Colq)
end
begin
with xmlnamespaces (default 'http://foresight8.vitalinsightgroup.com/MultiLanguage.xsd')
Insert #tmp_Questions
SELECT DISTINCT
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.question_id as question_id,
(select top 1 survey_id from #tmp_survey where event_type_id = 3) as survey_id,
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.q_order as q_order,
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.question_number as question_number,
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey.event_type_id as event_type_id,
'' as survey_date,
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.optional as optional,
Colq.value( '/', 'nvarchar(max)' ) as question
FROM
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question with(nolock)
INNER JOIN Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey_question with(nolock) ON Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.question_id = Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey_question.question_id
AND Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question.optional = 1
INNER JOIn
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey with(nolock) ON Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey_question.survey_id = Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey.survey_id AND Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey.event_type_id = 3
CROSS APPLY
question.nodes('/MultiLanguageText/Language') AS Taby(Colq)
end
-- Extracting the values from Answer XML and Pivoting to create a table AnswersOutput
Create Table #tmp_Answers
(
Answer_ID int,
event_type_id int,
Contact_Corporate bit,
Question_ID int,
QuestionValue_ID int,
Score int,
cust_comments nvarchar(max)
)
begin
with xmlnamespaces (default 'http://foresight8.vitalinsightgroup.com/Answers.xsd')
Insert #tmp_Answers
SELECT
a.Answer_ID as AnswerID,
a.event_type_id,
a.Contact_Corporate as Contact_Corporate,
Colx.value( './@questionId', 'int' ) as Question_ID,
Colx.value( './@questionValueId', 'int' ) as QuestionValue_ID,
Colx.value( './@score', 'int' ) as Score,
Colx.value( './Comments', 'nvarchar(max)' ) as cust_comments
FROM
fs_tbl_answer a with(nolock) --INNER JOIN Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey s ON a.survey_id = s.survey_id and s.status = 'A' and s.survey_id in (select survey_id from #tmp_survey)
CROSS APPLY
answers.nodes('/Answers/Answer') AS Tabx(Colx)
--WHERE a.created_date between '2008-10-01' AND '2008-11-01'
WHERE a.created_date between '2008-11-01' AND '2008-11-30'
end
begin
with xmlnamespaces (default 'http://foresight8.vitalinsightgroup.com/Answers.xsd')
Insert #tmp_Answers
SELECT
a.Answer_ID as AnswerID,
a.event_type_id,
a.Contact_Corporate as Contact_Corporate,
Colx.value( './@questionId', 'int' ) as Question_ID,
Colx.value( './@questionValueId', 'int' ) as QuestionValue_ID,
Colx.value( './@score', 'int' ) as Score,
Colx.value( './Comments', 'nvarchar(max)' ) as cust_comments
FROM
fs_tbl_answer a with(nolock) INNER JOIN fs_tbl_action b with(nolock) ON a.Answer_ID = b.Answer_ID
--AND b.created_date between '2008-10-01' AND '2008-11-01'
AND b.created_date between '2008-11-01' AND '2008-11-30'
and a.Answer_ID not in (select Answer_ID From #tmp_Answers)
CROSS APPLY
answers.nodes('/Answers/Answer') AS Tabx(Colx)
end
Create Table #tmp_AnswerValue
(
Question_ID int,
QuestionValue_ID int,
value_text nvarchar(30)
)
begin
with xmlnamespaces (default 'http://foresight8.vitalinsightgroup.com/MultiLanguage.xsd')
Insert #tmp_AnswerValue
SELECT
question_id as question_id,
value_id as value_id,
Colv.value( '/', 'nvarchar(30)' ) as value_text
FROM
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_question_value with(nolock)
CROSS APPLY
value_text.nodes('/MultiLanguageText/Language') AS Tabv(Colv)
where deleted = 0
end
--select q_order, question_id, question, event_type_id, survey_id from #tmp_Questions where survey_id = 1110
--Create Input Table
if object_id('tblPivotQuestion') is not null drop table tblPivotQuestion
CREATE TABLE tblPivotQuestion(q_order int, question_id int, question nvarchar(max), event_type_id int, survey_id int, question_number int)
--Load Table
SET NOCOUNT ON
INSERT INTO tblPivotQuestion
select q_order, question_id, question, event_type_id, survey_id, question_number from #tmp_Questions with(nolock) where survey_id in (select survey_id from #tmp_survey with(nolock)) and optional = 0
INSERT INTO tblPivotQuestion
select q_order, question_id, question, event_type_id, survey_id, question_number from #tmp_Questions with(nolock) where optional = 1
--select * from tblPivotQuestion
--Create temp table to hold data in pivoted format
create table #tmp_que( event_type_id int, survey_id int, data xml)
--Load temp table
insert into #tmp_que
select DISTINCT event_type_id, survey_id, dbo.getQuestionsPivotData(survey_id) from tblPivotQuestion with(nolock)
--select * from #tmp_que
--Prepare create table and insert script based on maximum columns for given id
declare @sql_createq nvarchar(4000),@q nvarchar(4000), @maxcolq int, @sql_insertsq nvarchar(4000)
select @sql_createq = '', @q =1, @sql_insertsq = ''
select @maxcolq = max(rowcnt) from (select count(*) rowcnt from tblPivotQuestion with(nolock) group by survey_id) a
while @q <= @maxcolq
begin
select @sql_createq = @sql_createq +
',Q' + cast(@q as nvarchar(4000)) + ' nvarchar(max) '
select @sql_insertsq = @sql_insertsq +
',data.value(''data[1]/@question' + cast(@q as nvarchar(4000)) + '[1]'', ''nvarchar(max)'') question' + cast(@q as nvarchar(4000))
select @q = @q + 1
end
select @sql_createq = 'if object_id(''fs_tbl_QuestionsOutPut'') is not null drop table fs_tbl_QuestionsOutPut; create table fs_tbl_QuestionsOutPut ( event_type_id int ' + @sql_createq + ')'
select @sql_insertsq = 'select event_type_id ' + @sql_insertsq + ' from #tmp_que'
--print @sql_createq
--print @sql_insertsq
exec (@sql_createq)
insert into fs_tbl_QuestionsOutPut
exec (@sql_insertsq)
--select * from fs_tbl_QuestionsOutPut
-- END
select * from fs_tbl_QuestionsOutPut
CREATE TABLE #tmpQLabels
(
Labels Varchar(100),
EventTypeID Varchar(100),
Question_ID Varchar(100),
Question_number int,
q_order int
)
INSERT INTO #tmpQLabels(Labels, EventTypeID)
(SELECT COLUMN_NAME, '1' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='fs_tbl_QuestionsOutPut' )
INSERT INTO #tmpQLabels(Labels, EventTypeID)
(SELECT COLUMN_NAME, '2' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='fs_tbl_QuestionsOutPut')
INSERT INTO #tmpQLabels(Labels, EventTypeID)
(SELECT COLUMN_NAME, '3' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='fs_tbl_QuestionsOutPut' )
DELETE from #tmpQLabels where Labels = 'event_type_id'
Select * from #tmpQLabels
Declare @sqls Nvarchar(4000)
DECLARE @Variable nvarchar(4000)
Declare @inc nvarchar(100)
DECLARE abc cursor for SELECT Question_ID FROM tblPivotQuestion with(nolock) WHERE event_type_id = 2 ORDER BY question_number, q_order
SELECT @inc = '1'
Open abc
fetch next from abc into @Variable
while @@Fetch_Status = 0
BEGIN
set @sqls = 'UPDATE #tmpQLabels SET Question_ID = ' + Isnull(@Variable, '9999') + ' WHERE EventTypeID = 2 and Labels = ''Q' + @inc + ''''
fetch next from abc into @Variable
exec (@Sqls)
select @inc = @inc + 1
end
close abc
deallocate abc
Declare @Sqls1 Nvarchar(4000)
DECLARE @Variable1 nvarchar(4000)
Declare @inc1 nvarchar(100)
DECLARE abc1 cursor for SELECT Question_ID FROM tblPivotQuestion with(nolock) WHERE event_type_id = 1 ORDER BY question_number, q_order
SELECT @inc1 = '1'
Open abc1
fetch next from abc1 into @Variable1
while @@Fetch_Status = 0
BEGIN
set @Sqls1 = 'UPDATE #tmpQLabels SET Question_ID = ' + Isnull(@Variable1, '9999') + ' WHERE EventTypeID = 1 and Labels = ''Q' + @inc1 + ''''
fetch next from abc1 into @Variable1
exec (@Sqls1)
select @inc1 = @inc1 + 1
end
close abc1
deallocate abc1
Declare @Sqls3 Nvarchar(4000)
DECLARE @Variable3 nvarchar(4000)
Declare @inc3 nvarchar(100)
DECLARE abc3 cursor for SELECT Question_ID FROM tblPivotQuestion with(nolock) WHERE event_type_id = 3 ORDER BY question_number, q_order
SELECT @inc3 = '1'
Open abc3
fetch next from abc3 into @Variable3
while @@Fetch_Status = 0
BEGIN
set @Sqls3 = 'UPDATE #tmpQLabels SET Question_ID = ' + Isnull(@Variable3, '9999') + ' WHERE EventTypeID = 3 and Labels = ''Q' + @inc3 + ''''
fetch next from abc3 into @Variable3
exec (@Sqls3)
select @inc3 = @inc3 + 1
end
close abc3
deallocate abc3
UPDATE #tmpQLabels SET Question_id = '9999' WHERE Question_ID is null
UPDATE #tmpQLabels
SET #tmpQLabels.question_number = tblPivotQuestion.question_number, #tmpQLabels.q_order = tblPivotQuestion.q_order
FROM #tmpQLabels INNER JOIN
tblPivotQuestion ON #tmpQLabels.question_id = tblPivotQuestion.question_id
--Create Input Table
if object_id('tblPivotAnswers') is not null drop table tblPivotAnswers
CREATE TABLE tblPivotAnswers(Score nvarchar(1000), Answer_ID int, cc_comment nvarchar(max), question_id int, q_order int, question_number int, Contact_Corporate bit, position varchar(100), event_type_ID int)
--Load Table
SET NOCOUNT ON
INSERT INTO tblPivotAnswers
SELECT (CASE isnull(ftqv.value_text, '') when 'Yes' then '100' when 'NO' then '0' when '5' then '100' when '4' then '80' when '3' then '60' when '2' then '40' when '1' then '20' else '-1' end) as valuetext ,
tmpAns.Answer_ID, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(IsNull(tmpAns.cust_comments, '') , ' ' , ''), '"' , ''), '>' , ''), '<' , ''), '&' , ''), ''' , ''''), '/q5', ''), '/', ''), '"', ''), '&', 'and'), ' ', '') as cc_comment,
isnull(AL.question_id, '') as question_id, isnull(AL.q_order, 9999) as q_order, isnull(AL.question_number, 9999) as question_number,
isnull(tmpAns.Contact_Corporate, '') as contact_corporate, AL.Labels, AL.EventTypeID
FROM #tmpQLabels AL with(nolock)
LEFT JOIN #tmp_Answers tmpAns with(nolock) on AL.question_id = tmpAns.question_id
LEFT JOIN #tmp_AnswerValue ftqv with(nolock) ON tmpAns.QuestionValue_ID = ftqv.QuestionValue_ID
--WHERE tmpAns.Contact_Corporate = 1 and tmpAns.Answer_ID NOT IN(select Answer_ID from dbo.fs_tbl_DatafeedLog)
WHERE tmpAns.Answer_ID > 18200
order by AL.question_number, AL.q_order, AL.Labels
select * from tblPivotAnswers
CREATE Table #tmpAnswerID
(
[ID] [int] IDENTITY(1,1) NOT NULL,
Answer_ID int,
EventType int
)
INSERT INTO #tmpAnswerID(Answer_ID, EventType)
SELECT DISTINCT Answer_ID, event_type_id from tblPivotAnswers with(nolock)
select * from #tmpQLabels
Declare @lblCount1 int
DEclare @lblCount2 int
Declare @lblCount3 int
Declare @cnt varchar(100)
DEclare @Qlbl varchar(100)
DEclare @Que varchar(100)
Declare @MaxQNumber int
Declare @QOrderMax int
Declare @RowDiff varchar(100)
Declare @totAnswerID int
Declare @MaxAnsCount int
Declare @AnsCount int
Declare @EventType int
Declare @totEventID int
SET @MaxAnsCount = (Select Max(ID) From #tmpAnswerID)
SET @AnsCount = 1
set @cnt = 1
SET @lblCount1 = (Select count(*) from #tmpQLabels with(nolock) where eventtypeid = 1 and Question_ID = 9999 and question_number is not null and q_order is not null)
SET @lblCount2 = (Select count(*) from #tmpQLabels with(nolock) where eventtypeid = 2 and Question_ID = 9999 and question_number is not null and q_order is not null)
SET @lblCount3 = (Select count(*) from #tmpQLabels with(nolock) where eventtypeid = 3 and Question_ID = 9999 and question_number is not null and q_order is not null)
--SET @lblCount1 = (Select count(*) from #tmpQLabels with(nolock) where eventtypeid = 1 and question_number is not null and q_order is not null)
--SET @lblCount2 = (Select count(*) from #tmpQLabels with(nolock) where eventtypeid = 2 and question_number is not null and q_order is not null)
--SET @lblCount3 = (Select count(*) from #tmpQLabels with(nolock) where eventtypeid = 3 and question_number is not null and q_order is not null)
select * from #tmpAnswerID
WHILE @AnsCount <= @MaxAnsCount
BEGIN
SET @totAnswerID = (Select Answer_ID from #tmpAnswerID with(nolock) where ID = @AnsCount)
SET @totEventID = (Select EventType from #tmpAnswerID with(nolock) where ID = @AnsCount)
IF @totAnswerID is not null
BEGIN
if @totEventID = 1
BEGIN
WHILE @cnt <= @lblCount1
BEGIN
SET @Qlbl = 'Q'+@cnt
SET @Que = (SELECT position from tblPivotAnswers with(nolock) where event_type_id = 1 and Answer_ID = @totAnswerID and position = @Qlbl)
if @Que is null
BEGIN
SET @RowDiff = @cnt - 1
SET @MaxQNumber = (Select question_number from tblPivotAnswers with(nolock) where event_type_id = 1 and Answer_ID = @totAnswerID and position = 'Q'+@RowDiff)
SET @QOrderMax = (Select q_order from tblPivotAnswers with(nolock) where event_type_id = 1 and Answer_ID = @totAnswerID and position = 'Q'+@RowDiff)
INSERT INTO tblPivotAnswers
VALUES('', @totAnswerID, '', 9999, 0, @MaxQNumber + 1, 0, @Qlbl, 1)
-- print(@Qlbl)
END
SET @cnt = @cnt + 1
END
END
if @totEventID = 2
BEGIN
WHILE @cnt <= @lblCount2
BEGIN
SET @Qlbl = 'Q'+@cnt
SET @Que = (SELECT position from tblPivotAnswers with(nolock) where event_type_id = 2 and Answer_ID = @totAnswerID and position = @Qlbl)
if @Que is null
BEGIN
SET @RowDiff = @cnt - 1
SET @MaxQNumber = (Select question_number from tblPivotAnswers with(nolock) where event_type_id = 2 and Answer_ID = @totAnswerID and position = 'Q'+@RowDiff)
SET @QOrderMax = (Select q_order from tblPivotAnswers with(nolock) where event_type_id = 2 and Answer_ID = @totAnswerID and position = 'Q'+@RowDiff)
INSERT INTO tblPivotAnswers
VALUES('', @totAnswerID, '', 9999, 0, @MaxQNumber + 1, 0, @Qlbl, 2)
-- print(@Qlbl)
END
SET @cnt = @cnt + 1
END
END
if @totEventID = 3
BEGIN
WHILE @cnt <= @lblCount3
BEGIN
SET @Qlbl = 'Q'+@cnt
SET @Que = (SELECT position from tblPivotAnswers with(nolock) where event_type_id = 3 and Answer_ID = @totAnswerID and position = @Qlbl)
if @Que is null
BEGIN
SET @RowDiff = @cnt - 1
SET @MaxQNumber = (Select question_number from tblPivotAnswers with(nolock) where event_type_id = 3 and Answer_ID = @totAnswerID and position = 'Q'+@RowDiff)
SET @QOrderMax = (Select q_order from tblPivotAnswers with(nolock) where event_type_id = 3 and Answer_ID = @totAnswerID and position = 'Q'+@RowDiff)
INSERT INTO tblPivotAnswers
VALUES('', @totAnswerID, '', 9999, 0, @MaxQNumber + 1, 0, @Qlbl, 3)
-- print(@Qlbl)
END
SET @cnt = @cnt + 1
END
END
END
SET @cnt = 1
SET @AnsCount = @AnsCount + 1
END
select * from tblPivotAnswers
declare @maxcol int
select @maxcol = max(rowcnt) from (select count(*) rowcnt from tblPivotAnswers with(nolock) group by Answer_ID) a
--Create temp table to hold data in pivoted format
create table #tmp_ans(Answer_ID int, data xml)
--Load temp table
insert into #tmp_ans
select DISTINCT Answer_ID, dbo.getAnswersPivotData(Answer_ID) from tblPivotAnswers with(nolock)
--select * from #tmp_ans
--Prepare create table and insert script based on maximum columns for given id
declare @sql_creates nvarchar(max),@f nvarchar(4000), @sql_inserts nvarchar(max)
select @sql_creates = '', @f =1, @sql_inserts = ''
--select @maxcol = max(rowcnt) from (select count(*) rowcnt from tblPivotAnswers group by Answer_ID) a
while @f <= @maxcolq
begin
select @sql_creates = @sql_creates +
',Q' + cast(@f as nvarchar(4000)) + ' nvarchar(4000) ' +
',Q' + cast(@f as nvarchar(4000)) + '_COMMENT' + ' nvarchar(max) '
select @sql_inserts = @sql_inserts +
',data.value(''data[1]/@Q' + cast(@f as nvarchar(4000)) + '[1]'', ''nvarchar(4000)'') Q' + cast(@f as nvarchar(4000)) +
',data.value(''data[1]/@C' + cast(@f as nvarchar(4000)) + '[1]'', ''nvarchar(max)'') C' + cast(@f as nvarchar(4000))
select @f = @f + 1
end
select @sql_creates = 'if object_id(''fs_tbl_AnswersOutput'') is not null drop table fs_tbl_AnswersOutput; create table fs_tbl_AnswersOutput (Answer_ID int ' + @sql_creates + ')'
select @sql_inserts = 'select Answer_ID ' + isnull(@sql_inserts, '0') + ' from #tmp_ans'
--print @sql_creates
--print @sql_inserts
exec (@sql_creates)
insert into fs_tbl_AnswersOutput
exec (@sql_inserts)
--select * from fs_tbl_AnswersOutput
--drop table #tmp_ans
--INSERT INTO fs_tbl_DatafeedLog(Answer_ID)
--Select DISTINCT Answer_ID from tblPivotAnswers with(nolock)
if (select Count(*) from fs_tbl_AnswersOutput with(nolock)) > 0
BEGIN
CREATE TABLE #tmp_DataFeed
(
client_code NVARCHAR(1000),
client_name NVARCHAR(1000),
recommendation NVARCHAR(1000),
overallsat NVARCHAR(1000),
csi decimal(18, 2),
threshold NVARCHAR(1000),
created_date NVARCHAR(1000),
survey_sent_date NVARCHAR(1000),
answer_id NVARCHAR(1000),
Survey_Method NVARCHAR(20),
RESOLUTION_COST decimal(18, 2),
B_CONTACT_DEALER NVARCHAR(20),
B_CONTACT_CORPORATE NVARCHAR(20),
vin NVARCHAR(1000),
make NVARCHAR(1000),
carline NVARCHAR(1000),
model_num NVARCHAR(1000),
stock_num NVARCHAR(1000),
survey_title NVARCHAR(1000),
event_type NVARCHAR(1000),
ro NVARCHAR(1000),
ro_date NVARCHAR(1000),
ro_mileage NVARCHAR(1000),
ro_description NVARCHAR(MAX),
advisor_name NVARCHAR(1000),
advisor_number NVARCHAR(1000),
technician_name NVARCHAR(1000),
technician_number NVARCHAR(1000),
DEAL_NUMBER NVARCHAR(1000),
deal_date NVARCHAR(1000),
deal_closed_date NVARCHAR(1000),
delivery_date NVARCHAR(1000),
delivery_mileage NVARCHAR(1000),
sales_name NVARCHAR(1000),
sales_number NVARCHAR(1000),
business_manager_name NVARCHAR(1000),
business_manager_number NVARCHAR(1000),
first_name NVARCHAR(1000),
middle_name NVARCHAR(1000),
last_name NVARCHAR(1000),
full_name NVARCHAR(1000),
address_1 NVARCHAR(1000),
address_2 NVARCHAR(1000),
city NVARCHAR(1000),
state NVARCHAR(1000),
zip NVARCHAR(1000),
res_phone NVARCHAR(1000),
bus_phone NVARCHAR(1000),
bus_phone_ext NVARCHAR(1000),
cell_Phone NVARCHAR(200),
email NVARCHAR(1000),
assigned_date NVARCHAR(1000),
issue_closed_date NVARCHAR(1000),
no_follow_up_date NVARCHAR(1000),
contact_customer NVARCHAR(1000),
customer_disposition NVARCHAR(1000),
comments NVARCHAR(MAX),
staff_comments NVARCHAR(MAX)
)
INSERT INTO #tmp_DataFeed
--service
Select
client.client_code,
client.client_name,
CASE (select score from tblPivotAnswers with(nolock) where Answer_ID = Answer.Answer_ID and question_id = 78) WHEN '100' then 'P' else 'N' end as recommendation,
CASE (select score from tblPivotAnswers with(nolock) where Answer_ID = Answer.Answer_ID and question_id = 52) WHEN '100' then 'P' else 'N' end as overallsat,
answer.csi,
survey.threshold,
convert(varchar(19), answer.created_date, 121) as created_date,
convert(varchar(19), answer.survey_sent_date, 121) as survey_sent_date,
answer.answer_id,
answer.Survey_Method,
act.RESOLUTION_COST,
answer.contact_dealer,
answer.contact_corporate,
tev.vin,
tev.make,
tev.carline,
tev.model_num,
tev.stock_num,
'Service' as survey_title,
'SV' as event_type,
sv.ro,
convert(varchar(19), sv.event_date, 121) as ro_date,
sv.ro_mileage,
CONVERT(VARCHAR(MAX),sv.ro_description) AS ro_description,
(select top 1 employee_name from fs_tbl_event_data_employee with(nolock) where event_id = SV.event_ID and client_group_id = Answer.client_group_id and event_type_id = Answer.event_type_id and employee_type_id = 1 and deleted = 0 and employee_name is not null) as sales_name,
(select top 1 employee_number from fs_tbl_event_data_employee with(nolock) where event_id = SV.event_ID and client_group_id = Answer.client_group_id and event_type_id = Answer.event_type_id and employee_type_id = 1 and deleted = 0 and employee_id is not null) as sales_number,
(select top 1 employee_name from fs_tbl_event_data_employee with(nolock) where event_id = SV.event_ID and client_group_id = Answer.client_group_id and event_type_id = Answer.event_type_id and employee_type_id = 2 and deleted = 0 and employee_name is not null) as business_manager_name,
(select top 1 employee_number from fs_tbl_event_data_employee with(nolock) where event_id = SV.event_ID and client_group_id = Answer.client_group_id and event_type_id = Answer.event_type_id and employee_type_id = 2 and deleted = 0 and employee_id is not null) as business_manager_number,
-- Case employee_type_id when 1 then emp.employee_name end as advisor_name,
-- Case employee_type_id when 1 then emp.employee_id end as advisor_number,
-- Case employee_type_id when 2 then emp.employee_name end as technician_name,
-- Case employee_type_id when 2 then emp.employee_id end as technician_number,
'' AS DEAL_NUMBER,
'' AS deal_date,
'' AS deal_closed_date,
'' AS delivery_date,
'' AS delivery_mileage,
'' AS sales_name,
'' AS sales_number,
'' AS business_manager_name,
'' AS business_manager_number,
customer.first_name,
customer.middle_name,
customer.last_name,
customer.full_name,
customer.address1 as address_1,
customer.address2 as address_2,
customer.city,
customer.state,
customer.zip,
customer.res_phone,
customer.bus_phone,
customer.bus_phone_ext,
customer.cell_Phone,
isnull(ce.email, customer.email) as email,
convert(varchar(19), act.assigned_date, 121) as assigned_date,
convert(varchar(19), act.closed_date, 121) as issue_closed_date,
'' as no_follow_up_date, --convert(varchar, answer.no_follow_up_date, 120) as no_follow_up_date,
'' as contact_customer,-- case answer.contact_customer when 1 then 'Y' when 2 then 'N' end as contact_customer,
'' as customer_disposition,-- case answer.customer_disposition when 1 then 'S' when 2 then 'D' end as customer_disposition,
CONVERT(VARCHAR(MAX),answer.comments) AS comments,
CONVERT(VARCHAR(MAX),act.staff_comments) AS staff_comments
From
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_client Client With (nolock)
INNER JOIN
fs_tbl_Answer Answer With (nolock)
On
Answer.Client_ID = Client.Client_ID
INNER JOIN
fs_tbl_AnswersOutput outAns
ON Answer.Answer_ID = outAns.Answer_ID
Left Join
fs_tbl_action act With (nolock) ON
Answer.Client_ID = act.Client_ID and
Answer.Answer_ID = act.answer_id
LEFT JOIN
fs_tbl_event_service SV With (nolock)
On
Answer.Event_ID = SV.Event_ID AND
Answer.Client_ID = SV.Client_ID
left Join
fs_tbl_event_data_vehicle tev with(nolock)
ON
SV.Event_ID = tev.Event_ID and
Answer.client_group_id = tev.client_group_id and
Answer.event_type_id = tev.event_type_id and
tev.Deleted = 0
-- Left join
-- fs_tbl_event_data_employee emp
-- ON
-- SV.event_ID = emp.event_ID AND
-- Answer.client_group_id = emp.client_group_id and
-- Answer.event_type_id = emp.event_type_id and
-- emp.Deleted = 0
Inner Join
fs_tbl_customer Customer With (nolock)
On
Customer.Customer_ID = SV.Customer_ID
Inner Join
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey Survey With (nolock)
on
Answer.Survey_ID = Survey.Survey_ID
inner join
fs_tbl_contact_email ce with (nolock)
on sv.event_id = ce.event_id and
ce.event_type_id = 2
Where Answer.event_type_id = 2 and survey.status = 'A' and Client.client_code != '0000000'
-- Answer.XML_SCAN_FLAG = 0 and Survey.status = 'A' and Client.Client_ID != 1
INSERT INTO #tmp_DataFeed
--Sales
Select
client.client_code,
client.client_name,
CASE (select score from tblPivotAnswers with(nolock) where Answer_ID = Answer.Answer_ID and question_id = 51) WHEN '100' then 'P' else 'N' end as recommendation,
CASE (select score from tblPivotAnswers with(nolock) where Answer_ID = Answer.Answer_ID and question_id = 26) WHEN '100' then 'P' else 'N' end as overallsat,
answer.csi,
survey.threshold,
convert(varchar(19), answer.created_date, 121) as created_date,
convert(varchar(19), answer.survey_sent_date, 121) as survey_sent_date,
answer.answer_id,
answer.Survey_Method,
act.RESOLUTION_COST,
answer.contact_dealer,
answer.contact_corporate,
tev.vin,
tev.make,
tev.carline,
tev.model_num,
tev.stock_num,
'SALE' as survey_title,
'DL' as event_type,
'' AS RO,
'' AS ro_date,
'' AS ro_mileage,
'' AS ro_description,
'' AS advisor_name,
'' AS advisor_number,
'' AS technician_name,
'' AS technician_number,
sv.deal_number as DEAL_NUMBER,
convert(varchar(19), sv.event_date, 121) as deal_date,
convert(varchar(19), sv.closed_date, 121) AS deal_closed_date,
convert(varchar(19), sv.delivery_date, 121) AS delivery_date,
tev.delivery_mileage AS delivery_mileage,
(select top 1 employee_name from fs_tbl_event_data_employee with(nolock) where event_id = SV.event_ID and client_group_id = Answer.client_group_id and event_type_id = Answer.event_type_id and employee_type_id = 4 and deleted = 0 and employee_name is not null) as sales_name,
(select top 1 employee_number from fs_tbl_event_data_employee with(nolock) where event_id = SV.event_ID and client_group_id = Answer.client_group_id and event_type_id = Answer.event_type_id and employee_type_id = 4 and deleted = 0 and employee_id is not null) as sales_number,
(select top 1 employee_name from fs_tbl_event_data_employee with(nolock) where event_id = SV.event_ID and client_group_id = Answer.client_group_id and event_type_id = Answer.event_type_id and employee_type_id = 3 and deleted = 0 and employee_name is not null) as business_manager_name,
(select top 1 employee_number from fs_tbl_event_data_employee with(nolock) where event_id = SV.event_ID and client_group_id = Answer.client_group_id and event_type_id = Answer.event_type_id and employee_type_id = 3 and deleted = 0 and employee_id is not null) as business_manager_number,
--
-- Case emp.employee_type_id when 4 then emp.employee_name end as sales_name,
-- Case emp.employee_type_id when 4 then emp.employee_id end as sales_number,
-- Case emp1.employee_type_id when 3 then emp1.employee_name end as business_manager_name,
-- Case emp1.employee_type_id when 3 then emp1.employee_id end as business_manager_number,
customer.first_name,
customer.middle_name,
customer.last_name,
customer.full_name,
customer.address1 as address_1,
customer.address2 as address_2,
customer.city,
customer.state,
customer.zip,
customer.res_phone,
customer.bus_phone,
customer.bus_phone_ext,
customer.cell_Phone,
isnull(ce.email, customer.email) as email,
convert(varchar(19), act.assigned_date, 121) as assigned_date,
convert(varchar(19), act.closed_date, 121) as issue_closed_date,
'' as no_follow_up_date, --convert(varchar, answer.no_follow_up_date, 120) as no_follow_up_date,
'' as contact_customer,-- case answer.contact_customer when 1 then 'Y' when 2 then 'N' end as contact_customer,
'' as customer_disposition,-- case answer.customer_disposition when 1 then 'S' when 2 then 'D' end as customer_disposition,
CONVERT(VARCHAR(MAX),answer.comments) AS comments,
CONVERT(VARCHAR(MAX),act.staff_comments) AS staff_comments
From
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_client Client With (nolock)
INNER JOIN
fs_tbl_Answer Answer With (nolock)
On
Answer.Client_ID = Client.Client_ID
INNER JOIN
fs_tbl_AnswersOutput outAns
ON Answer.Answer_ID = outAns.Answer_ID
Left Join
fs_tbl_action act With (nolock) ON
Answer.Client_ID = act.Client_ID and
Answer.Answer_ID = act.answer_id
LEFT JOIN
fs_tbl_event_sales SV With (nolock)
On
Answer.Event_ID = SV.Event_ID AND
Answer.Client_ID = SV.Client_ID
left Join
fs_tbl_event_data_vehicle tev with(nolock)
ON
SV.Event_ID = tev.Event_ID and
Answer.client_group_id = tev.client_group_id and
Answer.event_type_id = tev.event_type_id and
tev.Deleted = 0
-- left outer join
-- fs_tbl_event_data_employee emp
-- ON
-- SV.event_ID = emp.event_ID AND
-- Answer.client_group_id = emp.client_group_id and
-- Answer.event_type_id = emp.event_type_id and
-- emp.Deleted = 0
-- right join
-- fs_tbl_event_data_employee emp1
-- ON
-- SV.event_ID = emp1.event_ID AND
---- Answer.client_group_id = emp1.client_group_id and
---- Answer.event_type_id = emp1.event_type_id and
-- emp1.employee_ID = emp.employee_id and
-- emp1.Deleted = 0
Inner Join
fs_tbl_customer Customer With (nolock)
On
Customer.Customer_ID = SV.Customer_ID
Inner Join
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey Survey With (nolock)
on
Answer.Survey_ID = Survey.Survey_ID
inner join
fs_tbl_contact_email ce with (nolock)
on sv.event_id = ce.event_id
and ce.event_type_id = 3
Where Answer.event_type_id = 3 and survey.status = 'A' and Client.client_code != '0000000'
-- Answer.XML_SCAN_FLAG = 0 and Survey.status = 'A' and Client.Client_ID != 1
INSERT INTO #tmp_DataFeed
Select
client.client_code,
client.client_name,
CASE (select score from tblPivotAnswers with(nolock) where Answer_ID = Answer.Answer_ID and question_id = 25) WHEN '100' then 'P' else 'N' end as recommendation,
CASE (select score from tblPivotAnswers with(nolock) where Answer_ID = Answer.Answer_ID and question_id = 1) WHEN '100' then 'P' else 'N' end as overallsat,
answer.csi,
survey.threshold,
convert(varchar(19), answer.created_date, 121) as created_date,
convert(varchar(19), answer.survey_sent_date, 121) as survey_sent_date,
answer.answer_id,
answer.Survey_Method,
act.RESOLUTION_COST,
answer.contact_dealer,
answer.contact_corporate,
tev.vin,
tev.make,
tev.carline,
tev.model_num,
tev.stock_num,
'Pre-Owned' as survey_title,
'PO' as event_type,
'' AS RO,
'' AS ro_date,
'' AS ro_mileage,
'' AS ro_description,
'' AS advisor_name,
'' AS advisor_number,
'' AS technician_name,
'' AS technician_number,
sv.deal_number as DEAL_NUMBER,
convert(varchar(19), sv.event_date, 121) as deal_date,
convert(varchar(19), sv.closed_date, 121) AS deal_closed_date,
convert(varchar(19), sv.delivery_date, 121) AS delivery_date,
tev.delivery_mileage AS delivery_mileage,
(select top 1 employee_name from fs_tbl_event_data_employee with(nolock) where event_id = SV.event_ID and client_group_id = Answer.client_group_id and event_type_id = Answer.event_type_id and employee_type_id = 4 and deleted = 0 and employee_name is not null) as sales_name,
(select top 1 employee_number from fs_tbl_event_data_employee with(nolock) where event_id = SV.event_ID and client_group_id = Answer.client_group_id and event_type_id = Answer.event_type_id and employee_type_id = 4 and deleted = 0 and employee_id is not null) as sales_number,
(select top 1 employee_name from fs_tbl_event_data_employee with(nolock) where event_id = SV.event_ID and client_group_id = Answer.client_group_id and event_type_id = Answer.event_type_id and employee_type_id = 3 and deleted = 0 and employee_name is not null) as business_manager_name,
(select top 1 employee_number from fs_tbl_event_data_employee with(nolock) where event_id = SV.event_ID and client_group_id = Answer.client_group_id and event_type_id = Answer.event_type_id and employee_type_id = 3 and deleted = 0 and employee_id is not null) as business_manager_number,
-- Case employee_type_id when 4 then emp.employee_name end as sales_name,
-- Case employee_type_id when 4 then emp.employee_id end as sales_number,
-- Case employee_type_id when 3 then emp.employee_name end as business_manager_name,
-- Case employee_type_id when 3 then emp.employee_id end as business_manager_number,
customer.first_name,
customer.middle_name,
customer.last_name,
customer.full_name,
customer.address1 as address_1,
customer.address2 as address_2,
customer.city,
customer.state,
customer.zip,
customer.res_phone,
customer.bus_phone,
customer.bus_phone_ext,
customer.cell_Phone,
isnull(ce.email, customer.email) as email,
convert(varchar(19), act.assigned_date, 121) as assigned_date,
convert(varchar(19), act.closed_date, 121) as issue_closed_date,
'' as no_follow_up_date, --convert(varchar, answer.no_follow_up_date, 120) as no_follow_up_date,
'' as contact_customer,-- case answer.contact_customer when 1 then 'Y' when 2 then 'N' end as contact_customer,
'' as customer_disposition,-- case answer.customer_disposition when 1 then 'S' when 2 then 'D' end as customer_disposition,
CONVERT(VARCHAR(MAX),answer.comments) AS comments,
CONVERT(VARCHAR(MAX),act.staff_comments) AS staff_comments
From
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_client Client With (nolock)
INNER JOIN
fs_tbl_Answer Answer With (nolock)
On
Answer.Client_ID = Client.Client_ID
INNER JOIN
fs_tbl_AnswersOutput outAns
ON Answer.Answer_ID = outAns.Answer_ID
Left Join
fs_tbl_action act With (nolock) ON
Answer.Client_ID = act.Client_ID and
Answer.Answer_ID = act.answer_id
LEFT JOIN
fs_tbl_event_sales SV With (nolock)
On
Answer.Event_ID = SV.Event_ID AND
Answer.Client_ID = SV.Client_ID
left Join
fs_tbl_event_data_vehicle tev with(nolock)
ON
SV.Event_ID = tev.Event_ID and
Answer.client_group_id = tev.client_group_id and
Answer.event_type_id = tev.event_type_id and
tev.Deleted = 0
-- Left join
-- fs_tbl_event_data_employee emp
-- ON
-- SV.event_ID = emp.event_ID AND
-- Answer.client_group_id = emp.client_group_id and
-- Answer.event_type_id = emp.event_type_id and
-- emp.Deleted = 0
Inner Join
fs_tbl_customer Customer With (nolock)
On
Customer.Customer_ID = SV.Customer_ID
Inner Join
Foresight_v8_Master_BMW_DATA.dbo.fs_tbl_survey Survey With (nolock)
on
Answer.Survey_ID = Survey.Survey_ID
inner join
fs_tbl_contact_email ce with (nolock)
on sv.event_id = ce.event_id
and ce.event_type_id = 1
Where Answer.event_type_id = 1 and survey.status = 'A' and Client.client_code != '0000000'
-- Answer.XML_SCAN_FLAG = 0 and Survey.status = 'A' and Client.Client_ID != 1
--SELECT DISTINCT * FROM #tmp_DataFeed
Create TABLE #temp_srv
(
CLIENT_CODE VARCHAR(20),
CLIENT_NAME VARCHAR(100),
LOCATION_ID VARCHAR(10)
)
INSERT INTO #temp_srv
Select DISTINCT client_code, client_name, RIGHT(client_code, 2) as LOCATION_ID from #tmp_DataFeed with(nolock)
Create TABLE #temp_survey
(
EVENT_TYPE VARCHAR(10)
)
INSERT INTO #temp_survey
SELECT DISTINCT event_type FROM #tmp_DataFeed with(nolock)
-- Extracting the Answers and comments columns only and call it in a variable for Answer XML
declare @column_num int
declare @columnname varchar(max)
declare @tablename varchar(max)
set @column_num=2
set @columnname='';
set @tablename='fs_tbl_AnswersOutput'
select @columnname=@columnname+', tao.['+column_name+']' from information_schema.columns
where table_name=@tablename and ordinal_position >= @column_num
declare @column_numq int
declare @columnnameq varchar(max)
declare @tablenameq varchar(max)
set @column_numq=2
set @columnnameq='';
set @tablenameq='fs_tbl_QuestionsOutPut'
select @columnnameq=@columnnameq+',TQO.['+column_name+']' from information_schema.columns
where table_name=@tablenameq and ordinal_position >= @column_numq
--- END---
declare @sql nvarchar(max)
set @sql = 'SELECT NULL AS "RESPONSES",
(SELECT NULL AS "SURVEY_QUESTION",
(SELECT NULL AS "SALES",
'+substring(+@columnnameq,2,len(@columnnameq))+'
FROM fs_tbl_QuestionsOutPut TQO with(Nolock) where TQO.event_type_id = 3
FOR XML PATH(''SALES''), TYPE),
(SELECT NULL AS "SERVICE",
'+substring(+@columnnameq,2,len(@columnnameq))+'
FROM fs_tbl_QuestionsOutPut TQO with(Nolock) where TQO.event_type_id = 2
FOR XML PATH(''SERVICE''), TYPE),
(SELECT NULL AS "PREOWNED",
'+substring(+@columnnameq,2,len(@columnnameq))+'
FROM fs_tbl_QuestionsOutPut TQO with(Nolock) where TQO.event_type_id = 1
FOR XML PATH(''PREOWNED''), TYPE)
FOR XML PATH(''SURVEY_QUESTION''), TYPE),
(
SELECT NULL AS "DEALERS",
(
SELECT left(client_code, 5) as "@CODE", LOCATION_ID as "@LOCATION_ID", client_name as "@NAME",
(
SELECT case event_type when ''SV'' then ''SERVICE'' when ''PO'' then ''CPO'' else ''NVD'' end as ''@TYPE'',
(
SELECT NULL AS "CUSTOMERS",
(
SELECT
'''' as GROUP_ID,
--IsNull(CSI,'''') as CSI,
round(CSI, 0) as CSI,
IsNull(THRESHOLD, '''') as THRESHOLD,
CASE B_CONTACT_DEALER WHEN NULL then ''0'' WHEN ''0'' then ''0'' else ''1'' end as DEALER_ALERT_FLAG,
IsNull(created_date, '''') as SURVEY_RECEIVED_DATE,
IsNull(SURVEY_SENT_DATE,'''') as SURVEY_SENT_DATE,
IsNull(Survey_Method,'''') as SURVEY_METHOD,
IsNull(inn.ANSWER_ID,'''') as ANSWER_ID,
IsNull(overallsat, '''') as OVERALL_SAT,
IsNull(recommendation,'''') as RECOMMENDATION,
IsNull(VIN,'''') as VIN,
IsNull(MAKE,'''') as MAKE,
IsNull(CARLINE,'''') as CARLINE,
IsNull(MODEL_NUM,'''') as MODEL_NUM,
IsNull(STOCK_NUM,'''') as STOCK_NUM,
IsNull(EVENT_TYPE,'''') as EVENT_TYPE,
IsNull(B_CONTACT_CORPORATE, '''') as CORPORATE_ALERT,
--'''' as INFORMATION_REQUEST_FLAG,
CASE CSI WHEN 100.00 then 1 else 0 end as COMPLIMENT_FLAG,
IsNull(assigned_date, '''') as ISSUE_ASSIGNED_DATE,
IsNull(ISSUE_CLOSED_DATE, '''') as ISSUE_CLOSED_DATE,
IsNull(comments,'''') as CUSTOMER_COMMENTS,
IsNull(staff_comments, '''') as STAFF_COMMENTS,
round(RESOLUTION_COST, 0) as RESOLUTION_COST,
--IsNull(RESOLUTION_COST, '''') as RESOLUTION_COST,
(Select (select '''' for xml path(''CODE''),type),
(select '''' for xml path(''CODE''),type),
(select '''' for xml path(''CODE''),type),
(select '''' for xml path(''CODE''),type)
FOR XML PATH(''TREAD''), Type
),
CASE srvy.EVENT_TYPE WHEN ''DL'' then (
Select
IsNull(DEAL_NUMBER, '''') as DEAL_NUMBER,
IsNull(DEAL_DATE, '''') as DEAL_DATE,
IsNull(deal_closed_date, '''') as CLOSED_DATE,
IsNull(DELIVERY_DATE,'''') as DELIVERY_DATE,
IsNull(DELIVERY_MILEAGE, '''') as DELIVERY_MILEAGE,
IsNull(SALES_NAME,'''') as SALES_NAME,
IsNull(SALES_NUMBER,'''') as SALES_NUMBER,
IsNull(BUSINESS_MANAGER_NAME,'''') as BUSINESS_MANAGER_NAME,
IsNull(BUSINESS_MANAGER_NUMBER, '''') as BUSINESS_MANAGER_NUMBER
FOR XML PATH(''PURCHASE''), Type
) WHEN ''PO'' then
(
Select
IsNull(DEAL_NUMBER, '''') as DEAL_NUMBER,
IsNull(DEAL_DATE, '''') as DEAL_DATE,
IsNull(deal_closed_date, '''') as CLOSED_DATE,
IsNull(DELIVERY_DATE,'''') as DELIVERY_DATE,
IsNull(DELIVERY_MILEAGE, '''') as DELIVERY_MILEAGE,
IsNull(SALES_NAME,'''') as SALES_NAME,
IsNull(SALES_NUMBER,'''') as SALES_NUMBER,
IsNull(BUSINESS_MANAGER_NAME,'''') as BUSINESS_MANAGER_NAME,
IsNull(BUSINESS_MANAGER_NUMBER, '''') as BUSINESS_MANAGER_NUMBER
FOR XML PATH(''PURCHASE''), Type
) else
(
Select
IsNull(ro, '''') as RO,
IsNull(ro_date, '''') as RO_DATE,
IsNull(ro_mileage, '''') as RO_MILEAGE,
IsNull(ro_description, '''') as RO_DESCRIPTION,
IsNull(advisor_name, '''') as ADVISOR_NAME,
IsNull(advisor_number, '''') as ADVISOR_NUMBER,
IsNull(technician_name, '''') as TECHNICIAN_NAME,
IsNull(technician_number, '''') as TECHNICIAN_NUMBER
FOR XML PATH(''REPAIR''), Type
) end,
(Select
IsNull(first_name, '''') as CUST_FIRST_NAME,
IsNull(middle_name, '''') as CUST_MIDDLE_NAME,
IsNull(last_name, '''') as CUST_LAST_NAME,
IsNull(full_name, '''') as CUST_FULL_NAME,
IsNull(address_1, '''') as CUST_ADD1,
IsNull(address_2, '''') as CUST_ADD2,
IsNull(city, '''') as CUST_CITY,
IsNull(state, '''') as CUST_STATE,
IsNull(zip, '''') as CUST_ZIP,
IsNull(res_phone, '''') as CUST_RES_PHONE,
IsNull(bus_phone, '''') as CUST_BUS_PHONE,
IsNull(bus_phone_ext, '''') as CUST_BUS_PHONE_EXTEN,
IsNull(cell_Phone, '''') as CUST_CELL_PHONE,
IsNull(email, '''') as CUST_EMAIL
FOR XML PATH(''CONTACT_INFO''), Type
),
(select '+substring(+@columnname,2,len(@columnname))+' FOR XML PATH(''QUESTIONS''), Type)
FROM #tmp_DataFeed inn with(nolock) LEFT JOIN fs_tbl_AnswersOutput tao with(nolock) ON inn.answer_id = tao.Answer_id WHERE inn.Client_Code = out.client_code and inn.Event_Type = srvy.Event_Type
FOR XML PATH(''CUSTOMER''), Type)
FOR XML PATH(''CUSTOMERS''), Type
)
FROM #temp_survey srvy with(nolock)
FOR XML PATH(''SURVEYS''), Type
)
FROM #temp_srv out with(nolock)
FOR XML PATH(''DEALER''), Type
)
FOR XML PATH(''DEALERS''), Type
)
FOR XML PATH(''RESPONSES'')'
exec (@sql)
--select * from fs_tbl_AnswersOutput order by answer_id desc
drop table #tmp_ans
Drop table #tmp_Answers
drop table #tmp_que
Drop table #tmp_Questions
Drop table #tmp_quewers
drop table #tmp_survey
drop table #tmp_AnswerValue
DROP TABLE #temp_srv
DROP TABLE #temp_survey
DROP TABLE #tmp_DataFeed
--drop table fs_tbl_AnswersOutput
drop table tblPivotAnswers
drop table tblPivotQuestion
drop table fs_tbl_QuestionsOutPut
DROP table #tmpQLabels
DROP Table #tmpAnswerID
END
END
December 10, 2008 at 5:04 pm
Ch. Arshad (12/10/2008)
Hi Steve,Thank you for your reply. Sorry i didnt copied the whole stored procedure because the stored procedure is bit lengthy. Please find the procedure below...
Actually, Steve asked you to make this shorter, not longer.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply