SurveyDefinitionPossibleAnswers

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

SurveyKey

uniqueidentifier

16

√

null

SurveyName

varchar

250

√

null

SurveyVersion

varchar

250

√

null

StepType

varchar

100

√

null

StepTitle

varchar

100

√

null

StepText

varchar

1000

√

null

FormItemIdentifier

varchar

1000

√

null

FormItemText

varchar

1000

√

null

PossibleAnswers

nvarchar

2147483647

√

null

Relationships

Close relationships within degrees of separation

One

View Definition

CREATE VIEW [CareEv].[SurveyDefinitionPossibleAnswers]
AS 

	SELECT
	[SurveyKey], 
	[SurveyName],			
	[SurveyVersion],
	[StepType], 
	[StepTitle],
	[StepText],		 
	[FormItemIdentifier],
	[FormItemText],	
	[PossibleAnswers]
FROM
(
	SELECT DISTINCT 
		SD2.SurveyKey			AS [SurveyKey], 
		SD2.SurveyName			AS [SurveyName],			
		SD2.VersionNumber		AS [SurveyVersion],
		s2.Steptype				AS [StepType], 
		s2.StepTitle			AS [StepTitle],
		s2.StepText				AS [StepText],		 
		f2.FormItemIdentifier	AS [FormItemIdentifier],
		f2.FormItemText			AS [FormItemText],	
		STUFF(
			(
				SELECT ', ' + 'MinimumAnswerText - ' + CAST(faf.[FormItemAnswerMinimumValueDescription] AS VARCHAR) + ' : ' + 'MinimumAnswerValue - ' + CAST(faf.[FormItemAnswerMinimumValue] AS VARCHAR) + 
					' | MaximumAnswerText - ' + CAST( faf.[FormItemAnswerMaximumValueDescription] AS VARCHAR) + ' : ' + 'MaximumAnswerValue - ' + CAST(faf.[FormItemAnswerMaximumValue] AS VARCHAR)
				FROM
					PROMPT.CareEv.SurveyDefinitions SD
					CROSS APPLY OPENJSON(SD.Definition)
					WITH 
					(	
						[Steps]											NVARCHAR(MAX)			'$.steps' AS JSON
					) SDS
					CROSS APPLY OPENJSON([Steps])
					WITH
					(
						[Steptype]										VARCHAR(100)			'$.type',
						[StepTitle]										VARCHAR(100)			'$.title',
						[StepText]										VARCHAR(1000)			'$.text',
						[StepIdentifier]								VARCHAR(1000)			'$.identifier',
						[FormItems]										NVARCHAR(MAX)			'$.formItems' AS JSON
					) s
					OUTER APPLY OPENJSON([FormItems])
					WITH
					(
						[FormItemIdentifier]							VARCHAR(100)			'$.identifier',
						[FormItemType]									VARCHAR(100)			'$.type',
						[FormItemText]									VARCHAR(1000)			'$.text',
						[FormItemOptional]								VARCHAR(1000)			'$.optional',
						[FormItemsAnswerFormat]							NVARCHAR(MAX)			'$.answerFormat' AS JSON
					) f
					OUTER APPLY OPENJSON([FormItemsAnswerFormat])
					WITH
					(
						[FormItemAnswertype]							VARCHAR(100)			'$.type',
						[FormItemAnswerMinimumValue]					INT						'$.minimumValue',
						[FormItemAnswerMaximumValue]					INT						'$.maximumValue',
						[FormItemAnswerMaximumFractionDigits]			INT						'$.maximumFractionDigits',
						[FormItemAnswerMinimumValueDescription]			VARCHAR(1000)			'$.minimumValueDescription',
						[FormItemAnswerMaximumValueDescription]			VARCHAR(1000)			'$.maximumValueDescription',
						[FormItemAnswerDefaultValue]					VARCHAR(1000)			'$.defaultvalue'
					) faf
				WHERE
					sd.SurveyDefinitionKey = sd2.SurveyDefinitionKey AND
					s.Steptype = s2.StepType AND
					s.StepIdentifier = s2.StepIdentifier AND
					f.FormItemIdentifier = f2.FormItemIdentifier
				FOR XML PATH('')
			),1,1,''
		) AS [PossibleAnswers]
	FROM
					PROMPT.CareEv.SurveyDefinitions SD2
					OUTER APPLY OPENJSON(SD2.Definition)
					WITH 
					(	
						[Steps]										NVARCHAR(MAX)			'$.steps' AS JSON
					) SDS2
					OUTER APPLY OPENJSON([Steps])
					WITH
					(
					
						[Steptype]										VARCHAR(100)			'$.type',
						[StepTitle]										VARCHAR(100)			'$.title',
						[StepText]										VARCHAR(1000)			'$.text',
						[StepIdentifier]								VARCHAR(1000)			'$.identifier',
						[FormItems]										NVARCHAR(MAX)			'$.formItems' AS JSON,
						[StepsAnswerFormat]								NVARCHAR(MAX)			'$.answerFormat' AS JSON
					) s2
					OUTER APPLY OPENJSON([FormItems])
					WITH
					(
						[FormItemIdentifier]							VARCHAR(100)			'$.identifier',
						[FormItemType]									VARCHAR(100)			'$.type',
						[FormItemText]									VARCHAR(1000)			'$.text',
						[FormItemOptional]								VARCHAR(1000)			'$.optional',
						[FormItemsAnswerFormat]							NVARCHAR(MAX)			'$.answerFormat' AS JSON
					) f2
					OUTER APPLY OPENJSON([FormItemsAnswerFormat])
					WITH
					(
						[FormItemAnswertype]							VARCHAR(100)			'$.type',
						[FormItemAnswerMinimumValue]					INT						'$.minimumValue',
						[FormItemAnswerMaximumValue]					INT						'$.maximumValue',
						[FormItemAnswerMaximumFractionDigits]			INT						'$.maximumFractionDigits',
						[FormItemAnswerMinimumValueDescription]			VARCHAR(1000)			'$.minimumValueDescription',
						[FormItemAnswerMaximumValueDescription]			VARCHAR(1000)			'$.maximumValueDescription',
						[FormItemAnswerDefaultValue]					VARCHAR(1000)			'$.defaultvalue'
					) faf2
	WHERE
		s2.Steptype IN ('FormStep')
	UNION ALL
	SELECT DISTINCT 
		SD2.SurveyKey 					 AS [SurveyKey], 
		SD2.SurveyName 					 AS [SurveyName],			
		SD2.VersionNumber				 AS [SurveyVersion],
		s2.Steptype 					 AS [StepType], 
		s2.StepTitle					 AS [StepTitle],
		s2.StepText 					 AS [StepText],		 
		s2.StepIdentifier				 AS [FormItemIdentifier],
		NULL							 AS [FormItemText],	
		STUFF(
			(
				SELECT ISNULL(
								', ' + 'AnswerText - ' + satc.[StepAnswerTextChoiceText] + ' : ' + 'AnswerValue - ' + satc.[StepAnswerTextChoiceValue], 
								', ' + 'MinimumAnswerText - ' + CAST(saf.[QuestionAnswerMinimumValueDescription] AS VARCHAR) + ' : ' + 'MinimumAnswerValue - ' + CAST(saf.[QuestionAnswerMinimumValue] AS VARCHAR) + 
								' | MaximumAnswerText - ' + CAST( saf.[QuestionAnswerMaximumValueDescription] AS VARCHAR) + ' : ' + 'MaximumAnswerValue - ' + CAST(saf.[QuestionAnswerMaximumValue] AS VARCHAR)
							)
				FROM
					PROMPT.CareEv.SurveyDefinitions SD
					CROSS APPLY OPENJSON(SD.Definition)
					WITH 
					(	
						[Steps]										NVARCHAR(MAX)			'$.steps' AS JSON
					) SDS
					CROSS APPLY OPENJSON([Steps])
					WITH
					(
						[Steptype]										VARCHAR(100)			'$.type',
						[StepTitle]										VARCHAR(100)			'$.title',
						[StepText]										VARCHAR(1000)			'$.text',
						[StepIdentifier]								VARCHAR(1000)			'$.identifier',
						[StepsAnswerFormat]								NVARCHAR(MAX)			'$.answerFormat' AS JSON
					) s
					CROSS APPLY OPENJSON([StepsAnswerFormat])
					WITH
					(
						[QuestionAnswerMinimumValue]					INT						'$.minimumValue',
						[QuestionAnswerMaximumValue]					INT						'$.maximumValue',
						[QuestionAnswerMinimumValueDescription]			VARCHAR(1000)			'$.minimumValueDescription',
						[QuestionAnswerMaximumValueDescription]			VARCHAR(1000)			'$.maximumValueDescription',
						[StepAnswerTextChoices]							NVARCHAR(MAX)			'$.textChoices' AS JSON
					) saf
					CROSS APPLY OPENJSON([StepAnswerTextChoices]) 
					WITH
					(
						[StepAnswerTextChoiceType]						VARCHAR(100)			'$.type',
						[StepAnswerTextChoiceValue]						VARCHAR(1000)			'$.value',
						[StepAnswerTextChoiceText]						VARCHAR(1000)			'$.text'
					) satc			
				WHERE
					sd.SurveyDefinitionKey = sd2.SurveyDefinitionKey AND
					s.Steptype = s2.StepType AND
					s.StepIdentifier = s2.StepIdentifier
				FOR XML PATH('')
			),1,1,''
		) AS [PossibleAnswers]
	FROM
					PROMPT.CareEv.SurveyDefinitions SD2
					OUTER APPLY OPENJSON(SD2.Definition)
					WITH 
					(	
						[Steps]										NVARCHAR(MAX)			'$.steps' AS JSON
					) SDS2
					OUTER APPLY OPENJSON([Steps])
					WITH
					(
					
						[Steptype]										VARCHAR(100)			'$.type',
						[StepTitle]										VARCHAR(100)			'$.title',
						[StepText]										VARCHAR(1000)			'$.text',
						[StepIdentifier]								VARCHAR(1000)			'$.identifier',
						[StepsAnswerFormat]								NVARCHAR(MAX)			'$.answerFormat' AS JSON
					) s2
	WHERE
		s2.Steptype IN ('QuestionStep')
) Surveys

Possibly Referenced Tables/Views