SurveyDefinitionConfiguration_vw

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

SurveyDefinitionKey

uniqueidentifier

√

null

SurveyDefinitionFileName

varchar

250

√

null

SurveyKey

uniqueidentifier

√

null

SurveyName

varchar

250

√

null

VersionNumber

varchar

250

√

null

DefinitionJSON

nvarchar

2147483647

√

null

DefinitionType

varchar

500

√

null

DefinitionName

varchar

500

√

null

DefinitionIdentifier

varchar

500

√

null

DefinitionKeepAwake

varchar

500

√

null

StepType

varchar

100

√

null

StepActionType

varchar

255

√

null

StepDuration

varchar

100

√

null

StepTitle

varchar

2000

√

null

StepText

varchar

2000

√

null

StepIdentifier

varchar

1000

√

null

StepHTML

nvarchar

2147483647

√

null

StepHeight

varchar

100

√

null

StepParameterWorkoutDescription

varchar

1000

√

null

StepParameterNoAppleWatchMessage

varchar

1000

√

null

StepParameterFailureMessage

varchar

1000

√

null

StepParameterTimeoutMessage

varchar

1000

√

null

FormItemIdentifier

varchar

100

√

null

FormItemType

varchar

100

√

null

FormItemText

varchar

2000

√

null

FormItemOptional

varchar

1000

√

null

FormItemAnswertype

varchar

100

√

null

FormItemAnswerMinimumValue

int

√

null

FormItemAnswerMinimumValueDescription

varchar

1000

√

null

FormItemAnswerMaximumValue

int

√

null

FormItemAnswerMaximumValueDescription

varchar

1000

√

null

FormItemAnswerMaximumFractionDigits

int

√

null

FormItemAnswerDefaultValue

varchar

1000

√

null

StepAnswertype

varchar

100

√

null

StepAnswerTextChoiceType

varchar

100

√

null

StepAnswerTextChoiceText

varchar

1000

√

null

StepAnswerTextChoiceValue

varchar

1000

√

null

Relationships

Close relationships within degrees of separation

View Definition

CREATE VIEW [CareEv].[SurveyDefinitionConfiguration_vw]
AS 

	SELECT DISTINCT
		SD.SurveyDefinitionKey,	SD.FileName AS [SurveyDefinitionFileName], SD.SurveyKey, SD.SurveyName, SD.VersionNumber, SD.Definition AS [DefinitionJSON],
		[DefinitionType], [DefinitionName], [DefinitionIdentifier], [DefinitionKeepAwake],
		[StepType], [StepActionType], [StepDuration], [StepTitle], [StepText],[StepIdentifier], [StepHTML], [StepHeight], 
		--[PostTaskActionType],[PostTaskRequiredStepIdentifier], [PostTaskParameters], [PostTaskFinishReasons],
		[StepParameterWorkoutDescription], [StepParameterNoAppleWatchMessage], [StepParameterFailureMessage], [StepParameterTimeoutMessage],
		[FormItemIdentifier], [FormItemType], [FormItemText], [FormItemOptional], 
		[FormItemAnswertype], [FormItemAnswerMinimumValue], [FormItemAnswerMinimumValueDescription], 
		[FormItemAnswerMaximumValue], [FormItemAnswerMaximumValueDescription], [FormItemAnswerMaximumFractionDigits], [FormItemAnswerDefaultValue],
		[StepAnswertype], [StepAnswerTextChoiceType], [StepAnswerTextChoiceText], [StepAnswerTextChoiceValue]
		
	FROM
		PROMPT.CareEv.SurveyDefinitions SD
		OUTER APPLY OPENJSON(SD.Definition)
		WITH (
				[DefinitionType]							VARCHAR(500)			'$.type',
				[DefinitionName]							VARCHAR(500)			'$.name',
				[DefinitionIdentifier]						VARCHAR(500)			'$.identifier',
				[DefinitionKeepAwake]						VARCHAR(500)			'$.keepScreenAwake',
				[PostTaskActions]							NVARCHAR(MAX)			'$.postTaskActions' AS JSON,
				[Steps]										NVARCHAR(MAX)			'$.steps' AS JSON
		)
		OUTER APPLY OPENJSON([PostTaskActions])
		WITH
		(
			[PostTaskActionType]							VARCHAR(255)			'$.actionType',
			[PostTaskRequiredStepIdentifier]				VARCHAR(255)			'$.requiredStepIdentifier',
			[PostTaskParameters]							VARCHAR(255)			'$.parameters.url',
			[PostTaskFinishReasons]							NVARCHAR(MAX)			'$.finishReasons' AS JSON
		)
		OUTER APPLY OPENJSON([Steps])
		WITH
		(
			[Steptype]										VARCHAR(100)			'$.type',
			[StepDuration]									VARCHAR(100)			'$.stepDuration',
			[StepActionType]								VARCHAR(255)			'$.actionType',
			[StepTitle]										VARCHAR(2000)			'$.title',
			[StepText]										VARCHAR(2000)			'$.text',
			[StepIdentifier]								VARCHAR(1000)			'$.identifier',
			[StepHTML]										NVARCHAR(MAX)			'$.html',
			[StepHeight]									VARCHAR(100)			'$.height',
			[StepParameters]								NVARCHAR(MAX)			'$.parameters' AS JSON,
			[FormItems]										NVARCHAR(MAX)			'$.formItems' AS JSON,
			[StepsAnswerFormat]								NVARCHAR(MAX)			'$.answerFormat' AS JSON
		)
		OUTER APPLY OPENJSON([FormItems])
		WITH
		(
			[FormItemIdentifier]							VARCHAR(100)			'$.identifier',
			[FormItemType]									VARCHAR(100)			'$.type',
			[FormItemText]									VARCHAR(2000)			'$.text',
			[FormItemOptional]								VARCHAR(1000)			'$.optional',
			[FormItemsAnswerFormat]							NVARCHAR(MAX)			'$.answerFormat' AS JSON
		)
		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'
		)
		OUTER APPLY OPENJSON([StepParameters])
		WITH
		(
			[StepParameterWorkoutDescription]				VARCHAR(1000)			'$.workoutDescription',
			[StepParameterNoAppleWatchMessage]				VARCHAR(1000)			'$.noAppleWatchMessage',
			[StepParameterFailureMessage]					VARCHAR(1000)			'$.failureMessage',
			[StepParameterTimeoutMessage]					VARCHAR(1000)			'$.timeoutMessage'
		)
		OUTER APPLY OPENJSON([StepsAnswerFormat])
		WITH
		(
			[StepAnswertype]								VARCHAR(100)			'$.type',
			[StepAnswerTextChoices]							NVARCHAR(MAX)			'$.textChoices' AS JSON
		)
		OUTER APPLY OPENJSON([StepAnswerTextChoices])
		WITH
		(
			[StepAnswerTextChoiceType]						VARCHAR(100)			'$.type',
			[StepAnswerTextChoiceValue]						VARCHAR(1000)			'$.value',
			[StepAnswerTextChoiceText]						VARCHAR(1000)			'$.text'
		)

/*
	WHERE
		[SurveyDefinitionKey] IN 
		(
			SELECT 
				D.SurveyDefinitionKey
			FROM 
				EPIQ.CareEV.SurveyDefinitions SD
				JOIN 
				(
					SELECT 
						SurveyDefinitionKey,
						ROW_NUMBER() OVER (PARTITION BY SurveyKey ORDER BY InsertedDate DESC) AS [RN]
					FROM 
						EPIQ.CareEV.SurveyDefinitions 
				) D ON D.SurveyDefinitionKey = SD.SurveyDefinitionKey
			WHERE
				D.RN = 1 
		) 
*/

Possibly Referenced Tables/Views

SurveyDefinitionConfiguration_vw