Notes

Quota

RPQ is registered in the quota_resource_project_questionnaire table when:

checkAllQuotas() cgs function is called.
UpdateQuota function on quota tab is called
User moves to the last question of the rpq. In this case, rpq must be completed to be in the quota_resource_project_questionnaire table. However, if user moves to the previous question from the last question, the rpq is still in the table even though it is not completed.

The difference between checkAllQuotas(), CGS function, and UpdateQuota, GUI feature:

CheckAllQuotas(): update the RPQ's quotas status.
UpdateQuota: update all RPQs' quotas status

Testing tips

Property Definition

Questionnaire

CREATE TABLE #Questionnaire_Property(
   P_Id int,
   P_Description nvarchar(50)
) 

INSERT INTO #Questionnaire_Property VALUES(1,'Required')
INSERT INTO #Questionnaire_Property VALUES(16,'AllRequired')
INSERT INTO #Questionnaire_Property VALUES(17,'NotRequired')
INSERT INTO #Questionnaire_Property VALUES(18,'BackButtonVisible')
INSERT INTO #Questionnaire_Property VALUES(19,'BackButtonText')
INSERT INTO #Questionnaire_Property VALUES(20,'CloseButtonVisible')
INSERT INTO #Questionnaire_Property VALUES(21,'CloseButtonText')
INSERT INTO #Questionnaire_Property VALUES(22,'NextButtonVisible')
INSERT INTO #Questionnaire_Property VALUES(23,'NextButtonText')
INSERT INTO #Questionnaire_Property VALUES(32,'Script')
INSERT INTO #Questionnaire_Property VALUES(34,'QuestionnaireNotOpenText')
INSERT INTO #Questionnaire_Property VALUES(35,'QuestionnaireUnauthorizedAccessText')
INSERT INTO #Questionnaire_Property VALUES(36,'QuestionnaireClosedText')
INSERT INTO #Questionnaire_Property VALUES(37,'QuestionnaireEndText')
INSERT INTO #Questionnaire_Property VALUES(38,'QuestionnairePausedText')
INSERT INTO #Questionnaire_Property VALUES(39,'QuestionnaireCompletedText')
INSERT INTO #Questionnaire_Property VALUES(40,'QuestionnaireBeforeStartDateText')
INSERT INTO #Questionnaire_Property VALUES(41,'QuestionnaireAfterEndDateText')
INSERT INTO #Questionnaire_Property VALUES(42,'NotRequiredAll')
INSERT INTO #Questionnaire_Property VALUES(43,'ResetButtonVisible')
INSERT INTO #Questionnaire_Property VALUES(44,'ResetButtonText')
INSERT INTO #Questionnaire_Property VALUES(47,'QuestionnaireRequiredText')
INSERT INTO #Questionnaire_Property VALUES(48,'QuestionnaireMinRequiredText')
INSERT INTO #Questionnaire_Property VALUES(49,'QuestionnaireMaxRequiredText')
INSERT INTO #Questionnaire_Property VALUES(50,'QuestionnaireNumberRequiredText')
INSERT INTO #Questionnaire_Property VALUES(51,'QuestionnaireMinValRequiredText')
INSERT INTO #Questionnaire_Property VALUES(52,'QuestionnaireMaxValRequiredText')
INSERT INTO #Questionnaire_Property VALUES(53,'QuestionnaireNoFloatValText')
INSERT INTO #Questionnaire_Property VALUES(54,'QuestionnaireNumberOverflow')
INSERT INTO #Questionnaire_Property VALUES(55,'QuestionnaireInGridRequiredText')
INSERT INTO #Questionnaire_Property VALUES(56,'QuestionnaireMinInGridRequiredText')
INSERT INTO #Questionnaire_Property VALUES(57,'QuestionnaireMaxInGridRequiredText')
INSERT INTO #Questionnaire_Property VALUES(58,'QuestionnaireIllegalTypeText')
INSERT INTO #Questionnaire_Property VALUES(68,'Countdown') 


DROP TABLE #Questionnaire_Property

Question

CREATE TABLE #Question_Property(
P_Id int,
P_Description nvarchar(50)
) 

INSERT INTO #Questionnaire_Property VALUES(2,'RandomizeAnswerOptions')
INSERT INTO #Questionnaire_Property VALUES(3,'RotateAnswerOptions')
INSERT INTO #Questionnaire_Property VALUES(4,'Maximum')
INSERT INTO #Questionnaire_Property VALUES(5,'Minimum')
INSERT INTO #Questionnaire_Property VALUES(6,'Dummy')
INSERT INTO #Questionnaire_Property VALUES(8,'End')
INSERT INTO #Questionnaire_Property VALUES(10,'Step')
INSERT INTO #Questionnaire_Property VALUES(11,'Discrete')
INSERT INTO #Questionnaire_Property VALUES(13,'MinimumText')
INSERT INTO #Questionnaire_Property VALUES(14,'MaximumText')
INSERT INTO #Questionnaire_Property VALUES(15,'Reverse')
INSERT INTO #Questionnaire_Property VALUES(18,'BackButtonVisible')
INSERT INTO #Questionnaire_Property VALUES(19,'BackButtonText')
INSERT INTO #Questionnaire_Property VALUES(20,'CloseButtonVisible')
INSERT INTO #Questionnaire_Property VALUES(21,'CloseButtonText')
INSERT INTO #Questionnaire_Property VALUES(22,'NextButtonVisible')
INSERT INTO #Questionnaire_Property VALUES(23,'NextButtonText')
INSERT INTO #Questionnaire_Property VALUES(24,'RandomizeSubQuestions')
INSERT INTO #Questionnaire_Property VALUES(25,'RotateSubQuestions')
INSERT INTO #Questionnaire_Property VALUES(32,'Script')
INSERT INTO #Questionnaire_Property VALUES(43,'ResetButtonVisible')
INSERT INTO #Questionnaire_Property VALUES(44,'ResetButtonText')
INSERT INTO #Questionnaire_Property VALUES(45,'Layout')
INSERT INTO #Questionnaire_Property VALUES(46,'Impsys')
INSERT INTO #Questionnaire_Property VALUES(47,'QuestionnaireRequiredText')
INSERT INTO #Questionnaire_Property VALUES(48,'QuestionnaireMinRequiredText')
INSERT INTO #Questionnaire_Property VALUES(49,'QuestionnaireMaxRequiredText')
INSERT INTO #Questionnaire_Property VALUES(50,'QuestionnaireNumberRequiredText')
INSERT INTO #Questionnaire_Property VALUES(51,'QuestionnaireMinValRequiredText')
INSERT INTO #Questionnaire_Property VALUES(52,'QuestionnaireMaxValRequiredText')
INSERT INTO #Questionnaire_Property VALUES(53,'QuestionnaireNoFloatValText')
INSERT INTO #Questionnaire_Property VALUES(54,'QuestionnaireNumberOverflow')
INSERT INTO #Questionnaire_Property VALUES(55,'QuestionnaireInGridRequiredText')
INSERT INTO #Questionnaire_Property VALUES(56,'QuestionnaireMinInGridRequiredText')
INSERT INTO #Questionnaire_Property VALUES(57,'QuestionnaireMaxInGridRequiredText')
INSERT INTO #Questionnaire_Property VALUES(58,'QuestionnaireIllegalTypeText')
INSERT INTO #Questionnaire_Property VALUES(59,'EndPoints')
INSERT INTO #Questionnaire_Property VALUES(60,'PointText')
INSERT INTO #Questionnaire_Property VALUES(61,'Unit')
INSERT INTO #Questionnaire_Property VALUES(64,'AutoArrangeAnswerOptions')
INSERT INTO #Questionnaire_Property VALUES(65,'AnswerOptionColumns')
INSERT INTO #Questionnaire_Property VALUES(66,'AnswerOptionRows')
INSERT INTO #Questionnaire_Property VALUES(68,'Countdown')
INSERT INTO #Questionnaire_Property VALUES(69,'AnswerOptionUniqueChoice')
INSERT INTO #Questionnaire_Property VALUES(70,'QuestionStylesheet')
INSERT INTO #Questionnaire_Property VALUES(71,'FlashPath')
INSERT INTO #Questionnaire_Property VALUES(72,'FlashHeight')
INSERT INTO #Questionnaire_Property VALUES(73,'FlashWidth')
INSERT INTO #Questionnaire_Property VALUES(76,'RPQStatus')
INSERT INTO #Questionnaire_Property VALUES(77,'ImpSysRequiredText')
INSERT INTO #Questionnaire_Property VALUES(78,'ImpSysSelectionText')
INSERT INTO #Questionnaire_Property VALUES(79,'ImpSysChoiceText')
INSERT INTO #Questionnaire_Property VALUES(80,'ImpSysGender')
INSERT INTO #Questionnaire_Property VALUES(81,'AllowDecimals')
INSERT INTO #Questionnaire_Property VALUES(82,'AllowDecimalsInterval')
INSERT INTO #Questionnaire_Property VALUES(83,'NumericalInterval')
INSERT INTO #Questionnaire_Property VALUES(84,'ExportPosition')
INSERT INTO #Questionnaire_Property VALUES(85,'ExportLength')
INSERT INTO #Questionnaire_Property VALUES(86,'OpenAnswerRequired')
INSERT INTO #Questionnaire_Property VALUES(87,'AlphabeticalTab')
INSERT INTO #Questionnaire_Property VALUES(88,'RepresentAsDropDown')
INSERT INTO #Questionnaire_Property VALUES(89,'IsShadowQuestion')
INSERT INTO #Questionnaire_Property VALUES(90,'QuestionInCludeInPage')

DROP TABLE #Question_Property

Questionnaire Question

DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)

SELECT q.*, 
       (CASE q.[Question_Type]
          WHEN 1 THEN (SELECT 'Single')
          WHEN 2 THEN (SELECT 'Multi')
          WHEN 3 THEN (SELECT 'Number')
          WHEN 4 THEN (SELECT 'Text')
          WHEN 5 THEN (SELECT 'Open')
          WHEN 6 THEN (SELECT 'SingleGrid')
          WHEN 7 THEN (SELECT 'MultiGrid')
          WHEN 8 THEN (SELECT 'TextGrid')
          WHEN 9 THEN (SELECT 'Page')
          WHEN 10 THEN (SELECT 'Multimedia')   
          WHEN 11 THEN (SELECT 'Scale')
          WHEN 12 THEN (SELECT 'ScaleGrid')
          ELSE ''
       END) AS [Type_Description],
      qq.[Question_Index]
FROM  [Question] q INNER JOIN
      [Questionnaire_Question] qq ON q.[id] = qq.[Question_Id]
WHERE qq.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)
ORDER BY qq.[Question_Index]

Questionnaire Group

DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)

-- Question Group 

SELECT qg.[Id],qg.[Name],qg.[Description], 
       (CASE qg.[Sequence_Command_Id]
           WHEN 0 THEN (SELECT 'Fixed')
           WHEN 1 THEN (SELECT 'Randomized')
           WHEN 2 THEN (SELECT 'Rotated')
           WHEN 3 THEN (SELECT 'Randomized Subset')
           WHEN 4 THEN (SELECT 'Rotated Subset')
           ELSE ''
        END
       ) AS 'Group_Type',
       qg.[Select_Count], 
       (SELECT [Question_Index] FROM [Questionnaire_Question] WHERE [Question_Id] = qq.[Question_Id]
       ) AS 'Question_Index',
       (SELECT [Label] FROM [Question] WHERE [Id] = qq.[Question_Id]
       ) AS 'Question_Label'
FROM [Questiongroup] qg LEFT JOIN
     [Questionnaire_Question]qq ON qg.[Id] = qq.[Questiongroup_Id] 
WHERE qg.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)
      AND qg.[Id] NOT IN (SELECT [Parent_Questiongroup_Id] FROM [Questiongroup] WHERE [Parent_Questiongroup_Id] IS NOT NULL)
ORDER BY qg.[Id] 

-- Higher Level Question Group 

SELECT qg_1.[Id],qg_1.[Name] AS 'Higher Group',qg_1.[description], 
       (CASE qg_1.[Sequence_Command_Id]
           WHEN 0 THEN (SELECT 'Fixed')
           WHEN 1 THEN (SELECT 'Randomized')
           WHEN 2 THEN (SELECT 'Rotated')
           WHEN 3 THEN (SELECT 'Randomized Subset')
           WHEN 4 THEN (SELECT 'Rotated Subset')
           ELSE ''
        END
       ) AS 'Group_Type',
       qg_1.[Select_Count],
       qg_2.[Name] AS 'Sub_Group'
FROM [Questiongroup] qg_1 INNER JOIN 
     [Questiongroup] qg_2 ON qg_1.[Id] = qg_2.[Parent_Questiongroup_Id]
WHERE qg_1.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)
ORDER BY qg_1.[Id]

Questionnaire Property

DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)


SELECT qnp.[Id] AS 'Qn_P_Id',qnp.[Property_Type], qnp.[Value] AS 'Qn_P_Value',
       qnpl.[Id] AS 'Qn_P_L_Id', qnpl.[Language_Id], qnpl.[Is_Default],qnpl.[Questionnaire_Property_Text] 

FROM [Questionnaire_Property] qnp LEFT JOIN 
     [Questionnaire_Property_Language] qnpl ON qnp.[Id] = qnpl.[Questionnaire_Property_Id] 
WHERE qnp.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)
ORDER BY qnp.[Property_Type]

Language Independent

DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)


SELECT qnp.[Id],qnp.[Property_Type], qnp.[Value]
FROM [Questionnaire_Property] qnp 
WHERE qnp.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)
   AND qnp.[id] NOT IN (SELECT [Questionnaire_Property_id] FROM [Questionnaire_Property_Language])
ORDER BY qnp.[Property_Type]

Language Dependent

DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)


SELECT qnp.[Id] AS 'Qn_P_Id',qnp.[Property_Type], qnp.[Value] AS 'Qn_P_Value',
       qnpl.[Id] AS 'Qn_P_L_Id', qnpl.[Language_Id], qnpl.[Is_Default],qnpl.[Questionnaire_Property_Text] 

FROM [Questionnaire_Property] qnp INNER JOIN 
     [Questionnaire_Property_Language] qnpl ON qnp.[Id] = qnpl.[Questionnaire_Property_Id] 
WHERE qnp.[Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)
ORDER BY qnp.[Property_Type]

Questionnaire Condition

DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)

SELECT c.[Id], qq.[Question_Index],qq.[Question_Id],
       c.[Condition_Type_Id], 
       (CASE (c.[Condition_Type_Id])
          WHEN 1 THEN (SELECT 'Show Question If')
          WHEN 2 THEN (SELECT 'GoTo Question If')
          WHEN 3 THEN (SELECT 'Exclude Answer Options If')
          WHEN 4 THEN (SELECT 'Include Answer Options If')
          WHEN 5 THEN (SELECT 'Exclude Answer Options From')
          WHEN 6 THEN (SELECT 'Include Answer Options From')
          WHEN 7 THEN (SELECT 'Text Conditions')
          WHEN 8 THEN (SELECT 'Exclude sub-question If')
          WHEN 9 THEN (SELECT 'Include sub-question If')
          ELSE ''
        END
       )AS 'Condition Type',
       c.[Expression], c.[Label], c.[Interval],
       rp.[Label], 
       rpl.[Language_Id],
       rpl.[Replacement_Text]
from [Condition] c INNER JOIN
     [Questionnaire_Question] qq ON c.[Questionnaire_Question_Id] = qq.[Id] LEFT JOIN
     [Replacement] rp ON rp.[Condition_Id] = c.[Id] LEFT JOIN
     [Replacement_Language] rpl ON rpl.[Replacement_Id] = rp.[Id]
WHERE qq.[Questionnaire_Id] = (SELECT [Questionnaire_Id] from [Project_Questionnaire] WHERE [Id] = @Q_Id)
ORDER BY qq.[Question_Index]

Project Questionnaire's Constant/Property values

Constant

DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)

SELECT c.[Id], co.[Label] AS 'Constant Option',
      (CASE co.[Type]            
        WHEN 1 THEN (SELECT 'Number')            
        WHEN 2 THEN (SELECT 'Boolean')            
        WHEN 3 THEN (SELECT 'String')            
        WHEN 4 THEN (SELECT 'DateTime')            
        WHEN 5 THEN (SELECT 'Single')            
        WHEN 6 THEN (SELECT 'Multi')            
        WHEN 7 THEN (SELECT 'Resource')             
        ELSE ''        
       END           
      )AS 'Constant_Type',
      c.[Value],
      ocl.[Name] as 'Option Choice Value', co.[Description]
FROM [Constant] c INNER JOIN
     [Constant_Option] co ON c.[Constant_Option_Id] = co.[Id] LEFT JOIN
     [Constant_Option_Choice] coc ON coc.[Constant_Id] = c.[Id] LEFT JOIN
     [Option_Choice] oc ON oc.[Id] = coc.[Option_Choice_Id] LEFT JOIN
     [Option_Choice_Language] ocl ON ocl.[Option_Choice_Id] = oc.[Id]
WHERE c.[Resource_Id_fk] = (SELECT [Resource_Uniqueid] FROM [Project_Questionnaire_Resource] WHERE [Id] = @Q_Id)
  AND co.[Resource_Id_fk] = (SELECT [Resource_UniqueId] 
                              FROM [Questionnaire_Resource] 
                              WHERE [Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id))
ORDER BY co.[Index]

Property

DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)

SELECT c.[Id], co.[Label] AS 'Constant Option',
      (CASE co.[Type]            
        WHEN 1 THEN (SELECT 'Number')            
        WHEN 2 THEN (SELECT 'Boolean')            
        WHEN 3 THEN (SELECT 'String')            
        WHEN 4 THEN (SELECT 'DateTime')            
        WHEN 5 THEN (SELECT 'Single')            
        WHEN 6 THEN (SELECT 'Multi')            
        WHEN 7 THEN (SELECT 'Resource')             
        ELSE ''        
       END           
      )AS 'Constant_Type',
      c.[Value],
      ocl.[Name] as 'Option Choice Value', co.[Description]
FROM [Constant] c INNER JOIN
    [Constant_Option] co ON c.[Constant_Option_Id] = co.[Id] LEFT JOIN
    [Constant_Option_Choice] coc ON coc.[Constant_Id] = c.[Id] LEFT JOIN
    [Option_Choice] oc ON oc.[Id] = coc.[Option_Choice_Id] LEFT JOIN
    [Option_Choice_Language] ocl ON ocl.[Option_Choice_Id] = oc.[Id]
WHERE c.[Resource_Id_fk] = (SELECT [Resource_Uniqueid] FROM [Project_Questionnaire_Resource] WHERE [Id] = @Q_Id)
  AND co.[Resource_Id_fk] = (SELECT [Resource_Uniqueid] 
                              FROM [Resource_template_Resource] 
                              WHERE [Id] = (SELECT [Resource_template_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id))
ORDER BY co.[Index]

Questionnaire's Question Property

DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId) 

SELECT  q.[Id], qq.[Question_Index],q.[Label], 
        qp.[Property_Type], qp.[Value], 
        qpl.[Language_Id], qpl.[Question_Property_Text],  qpl.[Is_Default]
FROM [Question] q INNER JOIN
     [Questionnaire_Question] qq ON q.[Id] = qq.[Question_Id] LEFT JOIN
     [Question_Property] qp ON q.[Id] = qp.[Question_Id] LEFT JOIN 
     [Question_Property_language] qpl ON qp.[Id] = qpl.[Question_Property_Id]
WHERE qq.[Questionnaire_Id] =  (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)
ORDER BY qq.[Question_Index],qp.[Property_Type],qpl.[Question_Property_Text]

PQ's Data

DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)

SELECT rpq.[id] AS 'RPQ_ID',d.[Id],rpq.[Completed], rpq.[Status], rpq.[Test],
      (SELECT [Question_Index]
        FROM [Questionnaire_Question]
        WHERE [Question_Id] = d.[Question_Id]
          AND [Questionnaire_Id] = (SELECT [Questionnaire_Id] FROM [Project_Questionnaire] WHERE [Id] = @Q_Id)
      )AS 'Question Index',
      (SELECT [Label] FROM [Question] WHERE [Id] = d.[Question_Id])AS 'Question Label',
      (CASE (SELECT [Question_Type] FROM [Question] WHERE [Id] = d.[Question_Id])          
          WHEN 1 THEN (SELECT 'Single')          
          WHEN 2 THEN (SELECT 'Multi')          
          WHEN 3 THEN (SELECT 'Number')          
          WHEN 4 THEN (SELECT 'Text')          
          WHEN 5 THEN (SELECT 'Open')          
          WHEN 6 THEN (SELECT 'SingleGrid')          
          WHEN 7 THEN (SELECT 'MultiGrid')          
          WHEN 8 THEN (SELECT 'TextGrid')          
          WHEN 9 THEN (SELECT 'Page')          
          WHEN 10 THEN (SELECT 'Multimedia')             
          WHEN 11 THEN (SELECT 'Scale')          
          WHEN 12 THEN (SELECT 'ScaleGrid')          
          ELSE ''       
       END) AS 'Question Type',
      d.[Sub_Question_Id],d.[Grid_Number],d.[Option_Number],d.[Selection_Order],d.[Value],
      do.[Value]AS 'Open Values',d.[TimeStamp], d.[Duration_Seconds],d.[User_Id],d.[Resource_Id_fk]
FROM [Data] d LEFT JOIN
     [Data_Open] do ON d.[Id] = do.[Data_Id] INNER JOIN
     [Resource_Project_Questionnaire] rpq on d.[Resource_Project_Questionnaire_Id] = rpq.[Id]
     
WHERE d.[Project_Questionnaire_Id] = @Q_Id
  AND rpq.[Deleted_Date] IS NULL
  AND rpq.[Test] = 0

Sample query

Template Property

DECLARE @Q_Id INT
DECLARE @Q_UId INT
SET @Q_UId = <Project_Questionnaire_UniqueId>
SET @Q_Id = (SELECT [Id] FROM [Project_Questionnaire_Resource] WHERE [Resource_Id] = @Q_UId)

SELECT rtr.[Id] AS 'Template_Id', rtr.[Short_Name] AS 'Template',
       co.[Index],co.[Id] AS 'Constant_Option_Id', co.[Label] AS 'Constant_Option_Label',
       ( CASE co.[Type]
            WHEN 1 THEN (SELECT 'Number')
            WHEN 2 THEN (SELECT 'Boolean')
            WHEN 3 THEN (SELECT 'String')
            WHEN 4 THEN (SELECT 'DateTime')
            WHEN 5 THEN (SELECT 'Single')
            WHEN 6 THEN (SELECT 'Multi')
            WHEN 7 THEN (SELECT 'Resource') 
            ELSE ''
        END    
       )AS 'Constant_Type',
       ocl.[Option_Choice_Id], ocl.[Language_Id],
       (SELECT [Name] FROM [Language] WHERE [Id] = ocl.[Language_Id]) AS 'Language Name',
       ocl.[Name] AS 'Option_Choice_Name'
FROM [Resource_Template_Resource] rtr INNER JOIN
      [Constant_Option]co ON rtr.[Resource_UniqueId] = co.[Resource_Id_fk] LEFT JOIN
      [Option_Choice] oc ON co.[Id] = oc.[Constant_Option_Id] LEFT JOIN
      [Option_Choice_Language] ocl ON oc.[Id] = ocl.[Option_Choice_Id]
WHERE rtr.[Id] = @Template_Id
ORDER BY co.[Index]

Notes

Contents

Quota

Testing tips

Property Definition

Questionnaire

Question

Questionnaire Question

Questionnaire Group

Questionnaire Property

Language Independent

Language Dependent

Questionnaire Condition

Project Questionnaire's Constant/Property values

Constant

Property

Questionnaire's Question Property

PQ's Data

Sample query

Template Property

Navigation menu

Personal tools

Namespaces

Variants

Views

More

Search

Categories

Tools