12121212

-- ====================================================================================

-- INITIAL RELEASE: 11/15/2021 AUTHOR: Jeevan Shekar

-- EXEC [CW_SURVEY_SP] 93

-- =====================================================================================

CREATE PROCEDURE [dbo].[CW_SURVEY_SP]

(

@DATASOURCEID INT

)

BEGIN

BEGIN TRANSACTION

BEGIN TRY

--DECLARE @DATASOURCEID INT = 93

DECLARE @LOAD_START_TIME DATETIME, @LAST_MODIFIED_DATE DATETIME, @MAX_LAST_MODIFIED_DATE DATETIME;

SET @LOAD_START_TIME = GETDATE();

SET @LAST_MODIFIED_DATE = (SELECT CONVERT(VARCHAR,CONVERT(DATETIME,CAST('1970-01-01 00:00:00'AS DATETIME)+ (LAST_MODIFIED_DATE/(24 * 60 *60)),100), 120) AS LAST_MODIFIED_DATE

FROM SYN_SourceTables_CW WHERE DATASOURCEID = 93 AND SOURCETABLENAME = 'STG_CW_SURVEY')

--=================TRUNCATING STAGE TABLE==================

TRUNCATE TABLE STG_CW_TASK_SURVEY

--=================LOADING DATA INTO STAGE=================

--DECLARE @LAST_MODIFIED_DATE DATETIME = '2010-01-01 00:00:00', @DATASOURCEID INT = 93;

INSERT INTO STG_CW_TASK_SURVEY

(

[SENT_DATE]

,[COMPLETED]

,[UPDATED_BY]

,[TICKET_NUMBER]

,[TICKETTYPE]

,[SURVEY_USER]

,[SURVEY_TYPE]

,[COMPANY]

,[SURVEYCLASSIFICATION]

,[SCRUBDATE]

,[SURVEYSTARTED]

,[SURVEYINVITATIONDATE]

,[INSTANCEID]

,[Domain_ID]

,[Metric_Type]

)

--DECLARE @LAST_MODIFIED_DATE DATETIME = '2010-01-01 00:00:00', @DATASOURCEID INT = 93;

SELECT

ASMT_ASSESSMENT_INSTANCE.TAKEN_ON AS [SENT_DATE]

,CAST(ASMT_ASSESSMENT_INSTANCE.DV_STATE AS NVARCHAR(40)) AS [STATE]

,ASMT_ASSESSMENT_INSTANCE.SYS_CREATED_BY AS [SURVEY_CREATED]

,rtrim(ltrim(ASMT_ASSESSMENT_INSTANCE.DV_TASK_ID)) AS [TICKET_NUMBER] -- Survey Module Update

,ASMT_ASSESSMENT_INSTANCE.[TRIGGER_TABLE] AS TICKET_TYPE

,ASMT_ASSESSMENT_INSTANCE.[USER] AS SURVEY_USER

,CAST(ASMT_ASSESSMENT_INSTANCE.DV_METRIC_TYPE AS NVARCHAR(MAX)) AS SURVEY_TYPE

,CAST(C.COMPANY AS NVARCHAR(86)) AS COMPANY

,'SERVICE DESK'

,ASMT_ASSESSMENT_INSTANCE.DUE_DATE

,ASMT_ASSESSMENT_INSTANCE.SYS_CREATED_ON

,ASMT_ASSESSMENT_INSTANCE.NUMBER AS INSTANCEID

,ASMT_ASSESSMENT_INSTANCE.SYS_DOMAIN

,ASMT_ASSESSMENT_INSTANCE.DV_METRIC_TYPE AS DV_METRIC_TYPE

FROM SYN_SN_ST_asmt_assessment_instance ASMT_ASSESSMENT_INSTANCE

LEFT OUTER JOIN SYN_ODS_CW_ITIL_TRANSACTION ITIL ON ITIL.TRANSACTION_ID = CAST(ASMT_ASSESSMENT_INSTANCE.DV_TASK_ID AS NVARCHAR(255)) AND ITIL.DATASOURCEID = @DATASOURCEID

LEFT OUTER JOIN SYN_ODS_CW_COMPANY C ON C.COMPANY_SK = ITIL.COMPANY_SK AND C.DATASOURCEID = @DATASOURCEID

WHERE ASMT_ASSESSMENT_INSTANCE.SYS_UPDATED_ON > @LAST_MODIFIED_DATE AND

ASMT_ASSESSMENT_INSTANCE.DV_TASK_ID IS NOT NULL AND CAST(ASMT_ASSESSMENT_INSTANCE.DV_TASK_ID AS NVARCHAR(255)) <> ''

--where ASMT_ASSESSMENT_INSTANCE.DV_TASK_ID in ('INC4996386','INC4909193')

--DECLARE @DATASOURCEID INT = 93

UPDATE STG

SET STG.QUESTIONSETID = C.QUESTIONSETID

FROM STG_CW_TASK_SURVEY STG

LEFT OUTER JOIN SYN_CW_CSAT_QUESTION_SETS C

ON STG.DOMAIN_ID = C.DOMAIN_ID

AND STG.METRIC_TYPE = C.METRIC_TYPE

AND C.DATASOURCEID = @DATASOURCEID

-- Survey Module Update

--DECLARE @DATASOURCEID INT = 93

UPDATE STG

SET STG.QUESTIONSETID = 1 --select *

FROM STG_CW_TASK_SURVEY STG

WHERE STG.QUESTIONSETID is NULL

--DECLARE @DATASOURCEID INT = 93

UPDATE W

SET SURVEY_USER_PERSON_SK = PERSON.PERSON_SK

FROM STG_CW_TASK_SURVEY W

LEFT OUTER JOIN

(SELECT MAX(PERSON_SK) AS PERSON_SK,PERSON_ID,DATASOURCEID FROM SYN_PERSON WHERE DATASOURCEID = @DATASOURCEID GROUP BY PERSON_ID,DATASOURCEID) PERSON

ON (PERSON.PERSON_ID = W.SURVEY_USER AND PERSON.DATASOURCEID = @DATASOURCEID)

--=================TRUNCATING STAGE TABLE==================

TRUNCATE TABLE STG_CW_TASK_SURVEY_DETAILS

--=================LOADING DATA INTO STAGE=================

--DECLARE @LAST_MODIFIED_DATE DATETIME = '2017-10-01 00:00:00', @DATASOURCEID INT = 93;

INSERT INTO STG_CW_TASK_SURVEY_DETAILS

(

QUESTION

,QUESTION_ID

,RESPONSE

,SURVEY_DATE

,UPDATED

,TASK

,STATE

,COMMENTS

,COMPANY

,DATATYPE

,[ORDER]

,INSTANCEID

)

--DECLARE @LAST_MODIFIED_DATE DATETIME = '2017-10-01 00:00:00', @DATASOURCEID INT = 93;

SELECT

CAST (ASMT.[DV_METRIC] AS NVARCHAR(MAX)) AS QUESTION

,ASMT.[METRIC] AS QUESTION

,ASMT.[ACTUAL_VALUE] AS RESPONSE

,ASMT.[SYS_UPDATED_ON] AS SURVEY_DATE

,ASMT.[SYS_UPDATED_ON] AS UPDATED

,[ASMT_ASSESSMENT_INSTANCE].DV_TASK_ID AS TASK

,CAST(ASMT_ASSESSMENT_INSTANCE.DV_STATE AS NVARCHAR(46)) AS STATE

,CAST(ASMT.STRING_VALUE AS NVARCHAR(4000)) AS COMMENTS

,CAST(C.COMPANY AS NVARCHAR(86)) AS COMPANY

,METRIC.DATATYPE

,COALESCE(METRIC.[ORDER],REVERSE(SUBSTRING(REVERSE(ASMT.ASSIGN_TO_ORDER) ,1,CHARINDEX('-',REVERSE(ASMT.ASSIGN_TO_ORDER))-2)) ) AS [ORDER]

,ASMT_ASSESSMENT_INSTANCE.NUMBER AS INSTANCEID

FROM SYN_SN_ST_ASMT_METRIC METRIC

LEFT OUTER JOIN SYN_SN_ST_asmt_assessment_instance [ASMT_ASSESSMENT_INSTANCE]

ON [ASMT_ASSESSMENT_INSTANCE].Metric_Type = METRIC.Metric_Type

LEFT OUTER JOIN SYN_SN_ST_asmt_metric_result ASMT ON ASMT.INSTANCE=ASMT_ASSESSMENT_INSTANCE.SYS_ID AND ASMT.METRIC = METRIC.SYS_ID

LEFT OUTER JOIN SYN_ODS_CW_ITIL_TRANSACTION ITIL ON ITIL.TRANSACTION_ID = CAST([ASMT_ASSESSMENT_INSTANCE].DV_TASK_ID AS NVARCHAR(255)) AND ITIL.DATASOURCEID = @DATASOURCEID

LEFT OUTER JOIN SYN_ODS_CW_COMPANY C ON C.COMPANY_SK = ITIL.COMPANY_SK AND C.DATASOURCEID = @DATASOURCEID

WHERE ISNULL(ASMT.[SYS_UPDATED_ON],GETDATE()) > @LAST_MODIFIED_DATE AND

[ASMT_ASSESSMENT_INSTANCE].DV_TASK_ID IS NOT NULL AND CAST([ASMT_ASSESSMENT_INSTANCE].DV_TASK_ID AS NVARCHAR(255)) <> ''

and ASMT.[SYS_UPDATED_ON] IS NOT NULL

--and [ASMT_ASSESSMENT_INSTANCE].DV_TASK_ID = 'INC0278167'

--where ASMT_ASSESSMENT_INSTANCE.DV_TASK_ID in ('INC4996386','INC4909193')

DELETE FROM STG_CW_TASK_SURVEY_DETAILS

WHERE QUESTION = 'NEW SCALE'

AND COMPANY ='AUTONATION'

--======================Updating Response = Null for multiple choice Questions=============

UPDATE K

SET RESPONSE = NULL

FROM STG_CW_TASK_SURVEY_DETAILS K

WHERE DATATYPE = 'multiplecheckbox'

---------====================LOADING INTO ODS_CW=====================----------------------

----DECLARE @DATASOURCEID INT = 93

;WITH SURVEY_SENT

(

SELECT

SURVEY_SENT_STAGING.[SENT_DATE]

,SURVEY_SENT_STAGING.[COMPLETED]

,SURVEY_SENT_STAGING.[UPDATED_BY]

,SURVEY_SENT_STAGING.[TICKET_NUMBER]

,SURVEY_SENT_STAGING.[TICKETTYPE]

,SURVEY_SENT_STAGING.[SURVEY_USER_PERSON_SK]

,SURVEY_SENT_STAGING.[SURVEY_TYPE]

,COALESCE(SURVEY_SENT_STAGING.[COMPANY],'') AS [COMPANY]

,SURVEY_SENT_STAGING.[SURVEYCLASSIFICATION]

,SURVEY_SENT_STAGING.[SURVEYINVITATIONDATE]

,SURVEY_SENT_STAGING.[SURVEYSTARTED]

,SURVEY_SENT_STAGING.[SCRUBDATE]

,SURVEY_SENT_STAGING.[INSTANCEID]

,SURVEY_SENT_STAGING.[QuestionSetID]

,GETDATE() AS DATELOADED

,0 AS SURVEYMETHOD

,@DATASOURCEID AS DATASOURCEID

FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY TICKET_NUMBER,INSTANCEID ORDER BY SENT_DATE DESC) AS RN

FROM STG_CW_TASK_SURVEY SURVEY_SENT_STAGING) SURVEY_SENT_STAGING WHERE

RN = 1)

INSERT INTO SYN_CW_CSAT_DATA

(

[TICKETNUMBER],

[ATTEMPTED],

[COMPLETED],

[TICKETTYPE],

[TICKET_OWNER] ,

[SURVEYORCOMMENT],

[SURVEYINVITATIONDATE],

[SURVEYSTARTED],

[DATELOADED],

[CANDIDATEID],

[COMPANY],

[SURVEYCLASSIFICATION],

[CANDIDATESTATUSDESC],

[SURVEYMETHOD],

DATASOURCEID,

SCRUBDATE,

[INSTANCEID],

[QuestionSetID]

)

--DECLARE @DATASOURCEID INT = 93

SELECT

SS.TICKET_NUMBER,

ATTEMPTED = CASE WHEN (SS.COMPLETED='COMPLETE' OR SS.COMPLETED='REQUESTED') THEN 'YES' ELSE 'NO' END ,

COMPLETED = CASE WHEN (SS.COMPLETED='COMPLETE') THEN 'YES' ELSE 'NO' END,

TICKETTYPE = CASE WHEN LEFT(SS.TICKET_NUMBER,3) ='RIT' THEN 'REQUESTED ITEM' WHEN LEFT(SS.TICKET_NUMBER,3) ='REQ' THEN 'SERVICE REQUEST' WHEN LEFT(SS.TICKET_NUMBER,3) ='CHG' THEN 'CHANGE REQUEST' WHEN LEFT(SS.TICKET_NUMBER,2) ='WO' THEN 'WORKORDER' ELSE

'INCIDENT'END, -- Survey Module Update

SS.[SURVEY_USER_PERSON_SK],

SS.[SURVEY_TYPE],

SS.[SURVEYINVITATIONDATE],

SS.[SURVEYSTARTED],

GETDATE() AS DATELOADED,

'0' AS CANDIDATE_ID,

LEFT(SS.COMPANY,50) AS COMPANY,

SS.SURVEYCLASSIFICATION AS SURVEYCLASSIFICATION,

'N/A' AS CANDIDATESTATUSDESC,

0 AS SURVEYMETHOD,

@DATASOURCEID AS DATASOURCEID,

SS.SCRUBDATE,

SS.[INSTANCEID],

SS.[QuestionSetID]

FROM SURVEY_SENT SS

LEFT OUTER JOIN SYN_CW_CSAT_DATA CD ON SS.TICKET_NUMBER=CD.TICKETNUMBER AND CD.INSTANCEID = SS.INSTANCEID

AND CD.DATASOURCEID = @DATASOURCEID

WHERE CD.TICKETNUMBER IS NULL;

----------======= UPDATING RESPONSES AS NULL FOR INVALID RESPONSES ========----------------

UPDATE STG_CW_TASK_SURVEY_DETAILS

SET RESPONSE=NULL

WHERE RESPONSE='-1.00'

update STG_CW_TASK_SURVEY_DETAILS Set [order] = convert(varchar(3),[order])+'00'

where LEN([Order]) <3

-----------------======================= UPDATING TASK SURVEY DETAILS ===================------------------------

--DECLARE @DATASOURCEID INT=93

;WITH UPDATE_CTE AS

(

SELECT

QUESTIONS.TASK AS TICKET_NUMBER,

QUESTIONS.INSTANCEID AS INSTANCEID,

QUESTIONS.UPDATED AS UPDATED,

QUESTIONS.QUESTION1,

QUESTIONS.QUESTION2,

QUESTIONS.QUESTION3,

QUESTIONS.QUESTION4,

QUESTIONS.QUESTION5,

QUESTIONS.QUESTION6,

QUESTIONS.QUESTION7,

QUESTIONS.QUESTION8,

QUESTIONS.QUESTION9,

QUESTIONS.QUESTION10,

QUESTIONS.QUESTION11,

COMMENTS.COMMENTS1 AS Q1TEXT,

COMMENTS.COMMENTS2 AS Q2TEXT,

COMMENTS.COMMENTS3 AS Q3TEXT,

COMMENTS.COMMENTS4 AS Q4TEXT,

COMMENTS.COMMENTS5 AS Q5TEXT,

COMMENTS.COMMENTS6 AS Q6TEXT,

COMMENTS.COMMENTS7 AS Q7TEXT,

COMMENTS.COMMENTS8 AS Q8TEXT,

COMMENTS.COMMENTS9 AS Q9TEXT,

COMMENTS.COMMENTS10 AS Q10TEXT,

COMMENTS.COMMENTS11 AS Q11TEXT,

COMMENTS.COMMENTS12 AS Q12TEXT,

COMMENTS.COMMENTS13 AS Q13TEXT,

COMMENTS.COMMENTS14 AS Q14TEXT,

COMMENTS.COMMENTS15 AS Q15TEXT,

COMMENTS.COMMENTS16 AS Q16TEXT,

COMMENTS.COMMENTS17 AS Q17TEXT,

COMMENTS.COMMENTS18 AS Q18TEXT,

COMMENTS.COMMENTS19 AS Q19TEXT,

COMMENTS.COMMENTS20 AS Q20TEXT,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE1)=1 THEN RESPONSES.RESPONSE1 ELSE NULL END AS Q1SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE2)=1 THEN RESPONSES.RESPONSE2 ELSE NULL END AS Q2SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE3)=1 THEN RESPONSES.RESPONSE3 ELSE NULL END AS Q3SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE4)=1 THEN RESPONSES.RESPONSE4 ELSE NULL END AS Q4SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE5)=1 THEN RESPONSES.RESPONSE5 ELSE NULL END AS Q5SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE6)=1 THEN RESPONSES.RESPONSE6 ELSE NULL END AS Q6SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE7)=1 THEN RESPONSES.RESPONSE7 ELSE NULL END AS Q7SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE8)=1 THEN RESPONSES.RESPONSE8 ELSE NULL END AS Q8SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE9)=1 THEN RESPONSES.RESPONSE9 ELSE NULL END AS Q9SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE10)=1 THEN RESPONSES.RESPONSE10 ELSE NULL END AS Q10SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE11)=1 THEN RESPONSES.RESPONSE11 ELSE NULL END AS Q11SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE12)=1 THEN RESPONSES.RESPONSE12 ELSE NULL END AS Q12SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE13)=1 THEN RESPONSES.RESPONSE13 ELSE NULL END AS Q13SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE14)=1 THEN RESPONSES.RESPONSE14 ELSE NULL END AS Q14SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE15)=1 THEN RESPONSES.RESPONSE15 ELSE NULL END AS Q15SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE16)=1 THEN RESPONSES.RESPONSE16 ELSE NULL END AS Q16SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE17)=1 THEN RESPONSES.RESPONSE17 ELSE NULL END AS Q17SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE18)=1 THEN RESPONSES.RESPONSE18 ELSE NULL END AS Q18SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE19)=1 THEN RESPONSES.RESPONSE19 ELSE NULL END AS Q19SCORE,

CASE WHEN ISNUMERIC(RESPONSES.RESPONSE20)=1 THEN RESPONSES.RESPONSE20 ELSE NULL END AS Q20SCORE

FROM

(

SELECT

TASK,

INSTANCEID,

UPDATED,

"QUESTION1",

"QUESTION2",

"QUESTION3",

"QUESTION4",

"QUESTION5",

"QUESTION6",

"QUESTION7",

"QUESTION8",

"QUESTION9",

"QUESTION10",

"QUESTION11"

FROM

(

SELECT

'QUESTION'+CASE WHEN LEN([ORDER]) >= 3 THEN CONVERT(VARCHAR, LEFT([ORDER],LEN([ORDER])-2)) ELSE CAST([ORDER] AS VARCHAR) END AS QUESTIONCOLUMN,

--'QUESTION'+CONVERT(VARCHAR, ROW_NUMBER() OVER(PARTITION BY TASK,INSTANCEID ORDER BY [ORDER])) AS QUESTIONCOLUMN,

TASK,INSTANCEID, QUESTION, UPDATED,COMPANY

FROM(

SELECT DISTINCT TASK,INSTANCEID, [ORDER], CONVERT(VARCHAR(10),UPDATED,120) AS UPDATED, COMPANY, Question

--QUESTION =

-- (STUFF

-- (

-- (SELECT ' '+ QUESTION +' | '

-- FROM [ODS_STG_CW].[DBO].[STG_GSN_TASK_SURVEY_DETAILS] t1

-- WHERE t1.TASK = t2.TASK

-- AND t1.[ORDER] = t2.[ORDER]

-- ORDER BY SURVEY_DATE

-- FOR XML PATH (''))

-- , 1, 1, ''

-- )

-- )select *

FROM STG_CW_TASK_SURVEY_DETAILS t2

--where t2.Task = 'INC0278167'

GROUP BY TASK,INSTANCEID, [ORDER], CONVERT(VARCHAR(10),UPDATED,120), COMPANY, Question

) ROWNU

) QUESTIONSET

PIVOT

(

MAX(QUESTION) FOR QUESTIONCOLUMN IN ("QUESTION1", "QUESTION2", "QUESTION3", "QUESTION4", "QUESTION5","QUESTION6","QUESTION7","QUESTION8","QUESTION9","QUESTION10","QUESTION11")

) B

WHERE UPDATED IS NOT NULL

) QUESTIONS

--------------------------===================(CRQGB0000604681) COMMENTS Q1TEXT TO Q7TEXT ======================-----------------------

INNER JOIN

(

SELECT

TASK,

UPDATED,

INSTANCEID,

"COMMENTS1",

"COMMENTS2",

"COMMENTS3",

"COMMENTS4",

"COMMENTS5",

"COMMENTS6",

"COMMENTS7",

"COMMENTS8",

"COMMENTS9",

"COMMENTS10",

"COMMENTS11",

"COMMENTS12",

"COMMENTS13",

"COMMENTS14",

"COMMENTS15",

"COMMENTS16",

"COMMENTS17",

"COMMENTS18",

"COMMENTS19",

"COMMENTS20"

FROM

(

SELECT

--CONVERT(VARCHAR, ROW_NUMBER() OVER(PARTITION BY TASK ORDER BY [ORDER])),

--'COMMENTS'+CONVERT(VARCHAR, ROW_NUMBER() OVER(PARTITION BY TASK,INSTANCEID ORDER BY [ORDER])) AS COMMENTSCOLUMN,

'COMMENTS'+CASE WHEN LEN([ORDER]) >= 3 THEN CONVERT(VARCHAR, LEFT([ORDER],LEN([ORDER])-2)) ELSE CAST([ORDER] AS VARCHAR) END AS COMMENTSCOLUMN,

TASK,

INSTANCEID,

COMMENTS,

UPDATED,

COMPANY

FROM

(

SELECT * FROM

(

SELECT DISTINCT TASK,INSTANCEID, [ORDER], CONVERT(VARCHAR(10),UPDATED,120) AS UPDATED, COMPANY,

COMMENTS = CASE WHEN DATATYPE = 'multiplecheckbox' THEN

(STUFF

(

(SELECT ' ' + COMMENTS + ' ||'

FROM [STG_CW_TASK_SURVEY_DETAILS] t1

WHERE t1.TASK = t2.TASK

AND t1.[ORDER] = t2.[ORDER]

ORDER BY SURVEY_DATE

FOR XML PATH (''))

, 1, 1, ''

)

ELSE COMMENTS END

FROM STG_CW_TASK_SURVEY_DETAILS t2

--where t2.task = 'INC0278167'

GROUP BY TASK,INSTANCEID, [ORDER], CONVERT(VARCHAR(10),UPDATED,120), COMPANY,DATATYPE,COMMENTS

)DD

) ROWNU

) COMMENTSSET

PIVOT

(

MAX(COMMENTS) FOR COMMENTSCOLUMN IN ("COMMENTS1", "COMMENTS2","COMMENTS3","COMMENTS4","COMMENTS5","COMMENTS6","COMMENTS7","COMMENTS8", "COMMENTS9","COMMENTS10","COMMENTS11","COMMENTS12","COMMENTS13","COMMENTS14","COMMENTS15","COMMENTS16","COMMENTS17",

"COMMENTS18","COMMENTS19","COMMENTS20")

) B

WHERE UPDATED IS NOT NULL

) COMMENTS

ON ISNULL(QUESTIONS.TASK,'')=ISNULL(COMMENTS.TASK, '') AND ISNULL(QUESTIONS.INSTANCEID,'')=ISNULL(COMMENTS.INSTANCEID, '')

AND ISNULL(QUESTIONS.UPDATED,'')=ISNULL(COMMENTS.UPDATED,'')

---------========================= UPDATING RESPONSES ================--------------------------

INNER JOIN

(

SELECT

TASK,

INSTANCEID,

"RESPONSE1",

"RESPONSE2",

"RESPONSE3",

"RESPONSE4",

"RESPONSE5",

"RESPONSE6",

"RESPONSE7",

"RESPONSE8",

"RESPONSE9",

"RESPONSE10",

"RESPONSE11",

"RESPONSE12",

"RESPONSE13",

"RESPONSE14",

"RESPONSE15",

"RESPONSE16",

"RESPONSE17",

"RESPONSE18",

"RESPONSE19",

"RESPONSE20"

FROM

(

SELECT

--'RESPONSE'+CONVERT(VARCHAR, ROW_NUMBER() OVER(PARTITION BY TASK,INSTANCEID ORDER BY [ORDER])) AS RESPONSECOLUMN,

'RESPONSE'+CASE WHEN LEN([ORDER]) >= 3 THEN CONVERT(VARCHAR, LEFT([ORDER],LEN([ORDER])-2)) ELSE CAST([ORDER] AS VARCHAR) END AS RESPONSECOLUMN,

TASK,INSTANCEID, RESPONSE ,COMPANY

FROM

(

SELECT DISTINCT TASK,INSTANCEID, RESPONSE, [ORDER], COMPANY

FROM STG_CW_TASK_SURVEY_DETAILS

--WHERE TASK = 'INC0278167'

) ROWNU

) RESPONSESET

PIVOT

(

MAX(RESPONSE) FOR RESPONSECOLUMN IN ("RESPONSE1", "RESPONSE2", "RESPONSE3", "RESPONSE4", "RESPONSE5","RESPONSE6","RESPONSE7", "RESPONSE8", "RESPONSE9", "RESPONSE10", "RESPONSE11","RESPONSE12", "RESPONSE13", "RESPONSE14","RESPONSE15","RESPONSE1

6","RESPONSE17", "RESPONSE18", "RESPONSE19","RESPONSE20")

) B

) RESPONSES

ON ISNULL(QUESTIONS.TASK, '')=ISNULL(RESPONSES.TASK, '') AND ISNULL(QUESTIONS.INSTANCEID,'')=ISNULL(RESPONSES.INSTANCEID, '')

)

--SELECT * FROM UPDATE_CTE

--WHERE TICKET_NUMBER = 'INC0155901' ;

--==============================================================================

--UPDATE

-- ODS_CW.[DBO].CSAT_DATA

--SET

-- QUESTION1=UC.QUESTION1,

-- QUESTION2=UC.QUESTION2,

-- QUESTION3=UC.QUESTION3,

-- QUESTION4=UC.QUESTION4,

-- QUESTION5=UC.QUESTION5,

-- QUESTION6=UC.QUESTION6,

-- QUESTION7=UC.QUESTION7,

-- QUESTION8=UC.QUESTION8,

-- QUESTION9=UC.QUESTION9,

-- QUESTION10=UC.QUESTION10,

-- QUESTION11=UC.QUESTION11,

-- Q1TEXT=NULLIF(UC.Q1TEXT,''),

-- Q2TEXT=NULLIF(UC.Q2TEXT,''),

-- Q3TEXT=NULLIF(UC.Q3TEXT,''),

-- Q4TEXT=NULLIF(UC.Q4TEXT,''),

-- Q5TEXT=NULLIF(UC.Q5TEXT,''),

-- Q6TEXT=NULLIF(UC.Q6TEXT,''),

-- Q7TEXT=NULLIF(UC.Q7TEXT,''),

-- Q8TEXT=NULLIF(UC.Q8TEXT,''),

-- Q9TEXT=NULLIF(UC.Q9TEXT,''),

-- Q10TEXT=NULLIF(UC.Q10TEXT,''),

-- Q11TEXT=NULLIF(UC.Q11TEXT,''),

-- Q12TEXT=NULLIF(UC.Q12TEXT,''),

-- Q13TEXT=NULLIF(UC.Q13TEXT,''),

-- Q14TEXT=NULLIF(UC.Q14TEXT,''),

-- Q15TEXT=NULLIF(UC.Q15TEXT,''),

-- Q16TEXT=NULLIF(UC.Q16TEXT,''),

-- Q17TEXT=NULLIF(UC.Q17TEXT,''),

-- Q18TEXT=NULLIF(UC.Q18TEXT,''),

-- Q19TEXT=NULLIF(UC.Q19TEXT,''),

-- Q20TEXT=NULLIF(UC.Q20TEXT,''),

-- Q1SCORE=(SUBSTRING(UC.Q1SCORE,0,CHARINDEX('.',UC.Q1SCORE))),

-- Q2SCORE=(SUBSTRING(UC.Q2SCORE,0,CHARINDEX('.',UC.Q2SCORE))),

-- Q3SCORE=(SUBSTRING(UC.Q3SCORE,0,CHARINDEX('.',UC.Q3SCORE))),

-- Q4SCORE=(SUBSTRING(UC.Q4SCORE,0,CHARINDEX('.',UC.Q4SCORE))),

-- Q5SCORE=(SUBSTRING(UC.Q5SCORE,0,CHARINDEX('.',UC.Q5SCORE))),

-- Q6SCORE=(SUBSTRING(UC.Q6SCORE,0,CHARINDEX('.',UC.Q6SCORE))),

-- Q7SCORE=(SUBSTRING(UC.Q7SCORE,0,CHARINDEX('.',UC.Q7SCORE))),

-- Q8SCORE=(SUBSTRING(UC.Q8SCORE,0,CHARINDEX('.',UC.Q8SCORE))),

-- Q9SCORE=(SUBSTRING(UC.Q9SCORE,0,CHARINDEX('.',UC.Q9SCORE))),

-- Q10SCORE=(SUBSTRING(UC.Q10SCORE,0,CHARINDEX('.',UC.Q10SCORE))),

-- Q11SCORE=(SUBSTRING(UC.Q11SCORE,0,CHARINDEX('.',UC.Q11SCORE))),

-- Q12SCORE=(SUBSTRING(UC.Q12SCORE,0,CHARINDEX('.',UC.Q12SCORE))),

-- Q13SCORE=(SUBSTRING(UC.Q13SCORE,0,CHARINDEX('.',UC.Q13SCORE))),

-- Q14SCORE=(SUBSTRING(UC.Q14SCORE,0,CHARINDEX('.',UC.Q14SCORE))),

-- Q15SCORE=(SUBSTRING(UC.Q15SCORE,0,CHARINDEX('.',UC.Q15SCORE))),

-- Q16SCORE=(SUBSTRING(UC.Q16SCORE,0,CHARINDEX('.',UC.Q16SCORE))),

-- Q17SCORE=(SUBSTRING(UC.Q17SCORE,0,CHARINDEX('.',UC.Q17SCORE))),

-- Q18SCORE=(SUBSTRING(UC.Q18SCORE,0,CHARINDEX('.',UC.Q18SCORE))),

-- Q19SCORE=(SUBSTRING(UC.Q19SCORE,0,CHARINDEX('.',UC.Q19SCORE))),

-- Q20SCORE=(SUBSTRING(UC.Q20SCORE,0,CHARINDEX('.',UC.Q20SCORE))),

-- COMPLETED='YES'

--FROM

-- UPDATE_CTE UC

--WHERE

-- TICKETNUMBER=UC.TICKET_NUMBER AND CSAT_DATA.INSTANCEID = UC.INSTANCEID AND DATASOURCEID = @DATASOURCEID

-- AND CSAT_DATA.INSTANCEID NOT IN (SELECT INSTANCEID FROM [ODS_STG_CW].[DBO].[STG_GSN_TASK_SURVEY] SURVEY_SENT_STAGING

-- WHERE --COMPANY NOT IN ('AUTONATION') AND

-- COMPLETED='REQUESTED')

--===========================================================================

UPDATE ODS

SET

QUESTION1=UC.QUESTION1,

QUESTION2=UC.QUESTION2,

QUESTION3=UC.QUESTION3,

QUESTION4=UC.QUESTION4,

QUESTION5=UC.QUESTION5,

QUESTION6=UC.QUESTION6,

QUESTION7=UC.QUESTION7,

QUESTION8=UC.QUESTION8,

QUESTION9=UC.QUESTION9,

QUESTION10=UC.QUESTION10,

QUESTION11=UC.QUESTION11,

Q1TEXT=NULLIF(UC.Q1TEXT,''),

Q2TEXT=NULLIF(UC.Q2TEXT,''),

Q3TEXT=NULLIF(UC.Q3TEXT,''),

Q4TEXT=NULLIF(UC.Q4TEXT,''),

Q5TEXT=NULLIF(UC.Q5TEXT,''),

Q6TEXT=NULLIF(UC.Q6TEXT,''),

Q7TEXT=NULLIF(UC.Q7TEXT,''),

Q8TEXT=NULLIF(UC.Q8TEXT,''),

Q9TEXT=NULLIF(UC.Q9TEXT,''),

Q10TEXT=NULLIF(UC.Q10TEXT,''),

Q11TEXT=NULLIF(UC.Q11TEXT,''),

Q12TEXT=NULLIF(UC.Q12TEXT,''),

Q13TEXT=NULLIF(UC.Q13TEXT,''),

Q14TEXT=NULLIF(UC.Q14TEXT,''),

Q15TEXT=NULLIF(UC.Q15TEXT,''),

Q16TEXT=NULLIF(UC.Q16TEXT,''),

Q17TEXT=NULLIF(UC.Q17TEXT,''),

Q18TEXT=NULLIF(UC.Q18TEXT,''),

Q19TEXT=NULLIF(UC.Q19TEXT,''),

Q20TEXT=NULLIF(UC.Q20TEXT,''),

Q1SCORE=(SUBSTRING(UC.Q1SCORE,0,CHARINDEX('.',UC.Q1SCORE))),

Q2SCORE=(SUBSTRING(UC.Q2SCORE,0,CHARINDEX('.',UC.Q2SCORE))),

Q3SCORE=(SUBSTRING(UC.Q3SCORE,0,CHARINDEX('.',UC.Q3SCORE))),

Q4SCORE=(SUBSTRING(UC.Q4SCORE,0,CHARINDEX('.',UC.Q4SCORE))),

Q5SCORE=(SUBSTRING(UC.Q5SCORE,0,CHARINDEX('.',UC.Q5SCORE))),

Q6SCORE=(SUBSTRING(UC.Q6SCORE,0,CHARINDEX('.',UC.Q6SCORE))),

Q7SCORE=(SUBSTRING(UC.Q7SCORE,0,CHARINDEX('.',UC.Q7SCORE))),

Q8SCORE=(SUBSTRING(UC.Q8SCORE,0,CHARINDEX('.',UC.Q8SCORE))),

Q9SCORE=(SUBSTRING(UC.Q9SCORE,0,CHARINDEX('.',UC.Q9SCORE))),

Q10SCORE=(SUBSTRING(UC.Q10SCORE,0,CHARINDEX('.',UC.Q10SCORE))),

Q11SCORE=(SUBSTRING(UC.Q11SCORE,0,CHARINDEX('.',UC.Q11SCORE))),

Q12SCORE=(SUBSTRING(UC.Q12SCORE,0,CHARINDEX('.',UC.Q12SCORE))),

Q13SCORE=(SUBSTRING(UC.Q13SCORE,0,CHARINDEX('.',UC.Q13SCORE))),

Q14SCORE=(SUBSTRING(UC.Q14SCORE,0,CHARINDEX('.',UC.Q14SCORE))),

Q15SCORE=(SUBSTRING(UC.Q15SCORE,0,CHARINDEX('.',UC.Q15SCORE))),

Q16SCORE=(SUBSTRING(UC.Q16SCORE,0,CHARINDEX('.',UC.Q16SCORE))),

Q17SCORE=(SUBSTRING(UC.Q17SCORE,0,CHARINDEX('.',UC.Q17SCORE))),

Q18SCORE=(SUBSTRING(UC.Q18SCORE,0,CHARINDEX('.',UC.Q18SCORE))),

Q19SCORE=(SUBSTRING(UC.Q19SCORE,0,CHARINDEX('.',UC.Q19SCORE))),

Q20SCORE=(SUBSTRING(UC.Q20SCORE,0,CHARINDEX('.',UC.Q20SCORE))),

DATELOADED = GetDate(), -- Survey Module Update

COMPLETED='YES'

FROM SYN_CW_CSAT_DATA ODS

INNER JOIN UPDATE_CTE UC ON ODS.TICKETNUMBER=UC.TICKET_NUMBER AND ODS.INSTANCEID = UC.INSTANCEID

AND ODS.DATASOURCEID = @DATASOURCEID

--======= UPDATED V0.3 ===========

--DECLARE @DATASOURCEID INT=93

UPDATE

SYN_CW_CSAT_DATA

SET

DATEUPDATED=UPDATED,

SURVEYCOMPLETED=UPDATED

FROM

(SELECT TASK, MAX(UPDATED) AS UPDATED,INSTANCEID FROM STG_CW_TASK_SURVEY_DETAILS GROUP BY TASK,INSTANCEID) UC

WHERE

TICKETNUMBER=UC.TASK AND DATASOURCEID = @DATASOURCEID

AND SYN_CW_CSAT_DATA.INSTANCEID = UC.INSTANCEID

--AND COMPANY IN ('AUTONATION','VOTORANTIM CEMENT NORTH AMERICA')

--DECLARE @DATASOURCEID INT=93

--UPDATE

-- ODS

--SET

-- ODS_CW.AVERAGESCORE = DD.AVERAGE

--FROM

-- ODS_CW.DBO.CSAT_DATA ODS

-- INNER JOIN

-- (SELECT

-- ROUND(CAST((ISNULL(Q3SCORE,0)+ISNULL(Q4SCORE,0)+ISNULL(Q5SCORE,0)+ISNULL(Q6SCORE,0)+ISNULL(Q7SCORE,0)+ISNULL(Q8SCORE,0)) AS FLOAT)/ 12 , 3)*5 AS AVERAGE ,

-- TICKETNUMBER,INSTANCEID FROM ODS_CW.DBO.CSAT_DATA WHERE DATASOURCEID = @DATASOURCEID

-- )DD

--ON DD.TICKETNUMBER = ODS_CW.TICKETNUMBER

--WHERE DATASOURCEID = @DATASOURCEID

--AND COMPANY ='AUTONATION'

--DECLARE @DATASOURCEID INT=93

UPDATE ODS

SET AVERAGESCORE = ROUND(CAST((ISNULL(Q3SCORE,0)+ISNULL(Q4SCORE,0)+ISNULL(Q5SCORE,0)+ISNULL(Q6SCORE,0)+ISNULL(Q7SCORE,0)+ISNULL(Q8SCORE,0)) AS FLOAT)/ 12 , 3)*5

FROM ODS_CW.DBO.CSAT_DATA ODS

Inner Join [ODS_STG_CW].[DBO].[STG_GSN_TASK_SURVEY_DETAILS] SD

ON ODS_CW.TICKETNUMBER = SD.TASK AND ODS_CW.INSTANCEID = SD.INSTANCEID AND ODS_CW.DATASOURCEID = @DATASOURCEID

WHERE ODS_CW.DATASOURCEID = @DATASOURCEID

AND ODS_CW.COMPANY ='AUTONATION'

--DECLARE @DATASOURCEID INT=93

--UPDATE

-- ODS

--SET

-- ODS_CW.AVERAGESCORE = DD.AVERAGE

--FROM

-- ODS_CW.DBO.CSAT_DATA ODS

-- INNER JOIN

-- (SELECT

-- ROUND(CAST((ISNULL(Q1SCORE,0)+ISNULL(Q2SCORE,0)+ISNULL(Q3SCORE,0)+ISNULL(Q4SCORE,0)+ISNULL(Q5SCORE,0)+ISNULL(Q6SCORE,0)) AS FLOAT)/

-- NULLIF((

-- CASE WHEN Q1SCORE IS NOT NULL AND Q1SCORE<> 0 THEN 1 ELSE 0 END + CASE WHEN Q2SCORE IS NOT NULL AND Q2SCORE<> 0 THEN 1 ELSE 0 END +

-- CASE WHEN Q3SCORE IS NOT NULL AND Q3SCORE<> 0 THEN 1 ELSE 0 END + CASE WHEN Q4SCORE IS NOT NULL AND Q4SCORE<> 0 THEN 1 ELSE 0 END +

-- CASE WHEN Q5SCORE IS NOT NULL AND Q5SCORE<> 0 THEN 1 ELSE 0 END + CASE WHEN Q6SCORE IS NOT NULL AND Q6SCORE<> 0 THEN 1 ELSE 0 END ),0),3) AS AVERAGE,TICKETNUMBER

-- FROM ODS_CW.DBO.CSAT_DATA WHERE DATASOURCEID = @DATASOURCEID

-- )DD

--ON DD.TICKETNUMBER = ODS_CW.TICKETNUMBER

--WHERE DATASOURCEID = @DATASOURCEID

--AND COMPANY NOT IN ('AUTONATION')

--=================UPDATING SOURCE_TABLES INFO=================

--DECLARE @DATASOURCEID INT =93

--DECLARE @MAX_LAST_MODIFIED_DATE DATETIME;

UPDATE ODS

SET AVERAGESCORE = ROUND(CAST((ISNULL(Q3SCORE,0)+ISNULL(Q4SCORE,0)+ISNULL(Q5SCORE,0)+ISNULL(Q6SCORE,0)+ISNULL(Q7SCORE,0)+ISNULL(Q8SCORE,0)) AS FLOAT)/ 6 , 3)

FROM SYN_CW_CSAT_DATA ODS

Inner Join STG_CW_TASK_SURVEY_DETAILS SD

ON ODS.TICKETNUMBER = SD.TASK AND ODS.INSTANCEID = SD.INSTANCEID AND ODS.DATASOURCEID = @DATASOURCEID

WHERE ODS.DATASOURCEID = @DATASOURCEID

--AND ODS_CW.COMPANY ='AUTONATION'

--declare @MAX_LAST_MODIFIED_DATE datetime

;WITH CTE AS

(SELECT

row_number() over(partition by sys_domain order by UPDATED desc ) as rn,sys_domain,

CONVERT(VARCHAR, UPDATED, 120) AS LAST_MODIFIED_DATE

FROM STG_CW_TASK_SURVEY_DETAILS

)

--SELECT @MAX_LAST_MODIFIED_DATE=

-- MIN(LAST_MODIFIED_DATE)

--FROM CTE

-- WHERE rn=1

select @MAX_LAST_MODIFIED_DATE= (select case when MIN(LAST_MODIFIED_DATE) is null then (select max(dateupdated) as last_modified_date from SYN_CW_CSAT_DATA) else MIN(LAST_MODIFIED_DATE) end )

FROM CTE WHERE rn=1

UPDATE SYN_SourceTables_CW

SET LAST_MODIFIED_DATE=DATEDIFF(SECOND, '1970-01-01 00:00:00',@MAX_LAST_MODIFIED_DATE),

LOADSTARTTIME=@LOAD_START_TIME,

LOADENDTIME=GETDATE()

WHERE DATASOURCEID=@DATASOURCEID

AND PACKAGE_NAME='STG_CW_SURVEY'

AND SOURCETABLENAME='STG_CW_SURVEY'

SELECT '0' AS RETURNCODE

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ERRORNUMBER

,ERROR_SEVERITY() AS ERRORSEVERITY

,ERROR_STATE() AS ERRORSTATE

,ERROR_PROCEDURE() AS ERRORPROCEDURE

,ERROR_LINE() AS ERRORLINE

,ERROR_MESSAGE() AS ERRORMESSAGE

,'1' AS RETURNCODE;

IF (@@TRANCOUNT > 0)

ROLLBACK TRANSACTION

END CATCH;

END;

Search This Blog

MY WORK DIARY

12121212

Comments

Post a Comment

Popular posts from this blog

java chapter11 practice question on abstruct class and interfaces

DAY 12 -AZURE DP900(Microsoft Azure Data Fundamentals: Explore non-relational data in Azure)

java exercise4