How do you query the database for valid survey data via SQL?723

Version 1

    Details

    As survey information for objects is split cross multiple tables, pulling down that query information can be a challenge.


    OOTB, there are a few views that can be used to look at survey data. 

    If you find that the views do not have all of the desired information, then a SQL query to join the desired tables will most likely be required.

    The query provided in this article is based on demo data. You may find that the query needs to be adjusted to accommodate for a different number and/or type of questions.

     


    Resolution

    In the query, multiple tables are referenced.

    Incident
    ServiceReq
    These tables are involved in calling the surveys.

    FRS_Survey
    This table stores the values for the separate surveys built in the application

    FRS_SurveySession
    When a survey is actually submitted, a session is created for the survey

    FRS_SurveyQuestion
    The questions for each survey are tracked in this table.

    FRS_SurveyAnswer
    The answers for each survey session are tracked in this table. Answers are matched to the questions. There can be many answers linked to a single question, so the FRS_SurveyAnswer table will be much larger than any of the other survey tables.


    1. You first need to identify the Question positions so you can better form your final query.
    The following query will help you break down the positions in relation to the survey in question

    SELECT
    S.Title
    ,Q.PositionInSurvey
    ,Q.QuestionText
    FROM Frs_SurveyQuestion Q
    INNER JOIN FRS_Survey S ON S.SurveyID = Q.SurveyID
    ORDER BY 
    S.Title
    ,Q.PositionInSurvey



    2. Once you have the positions, you can alter the final query to pull down all of the desired data.

    SELECT S.Title, R.DateSubmitted, R.IdentifierType AS ParentType,
    (CASE
    WHEN R.IdentifierType = 'Incident' THEN I.IncidentNumber
    WHEN R.IdentifierType = 'ServiceReq' THEN SR.ServiceReqNumber
    END) AS ParentId,
    (CASE
    WHEN R.IdentifierType = 'Incident' THEN I.Category
    WHEN R.IdentifierType = 'ServiceReq' THEN 'ServiceReq'
    END) AS Category,
    (CASE
    WHEN R.IdentifierType = 'Incident' THEN I.Service
    WHEN R.IdentifierType = 'ServiceReq' THEN SR.Service
    END) AS Service,
    (CASE
    WHEN R.IdentifierType = 'Incident' THEN I.IsVIP
    WHEN R.IdentifierType = 'ServiceReq' THEN SR.IsVIP
    END) AS VIP,
    (CASE
    WHEN R.IdentifierType = 'Incident' THEN I.Email
    WHEN R.IdentifierType = 'ServiceReq' THEN SR.Email
    END) AS ParentCustomerEmail,
    (CASE
    WHEN R.IdentifierType = 'Incident' THEN I.OwnerTeam
    WHEN R.IdentifierType = 'ServiceReq' THEN SR.OwnerTeam
    END) AS OwnerTeam,
    (CASE
    WHEN R.IdentifierType = 'Incident' THEN I.Owner
    WHEN R.IdentifierType = 'ServiceReq' THEN SR.Owner
    END) AS Owner,
    A2.QuestionText AS Question01, A2.NumericValue AS Answer01,
    A3.QuestionText AS Question02, A3.NumericValue AS Answer02,
    A4.QuestionText AS Question03, A4.NumericValue AS Answer03,
    A5.QuestionText AS Question04, A5.AnswerText AS Answer04
    FROM FRS_SurveySession R
    LEFT OUTER JOIN FRS_Survey S ON R.SurveyId = S.SurveyId
    LEFT OUTER JOIN Incident I ON R.Identifier = I.RecId
    LEFT OUTER JOIN ServiceReq SR ON R.Identifier = SR.RecId
    LEFT OUTER JOIN FRS_SurveyQuestion Q2 ON R.SurveyId = Q2.SurveyId AND Q2.PositionInSurvey = 1
    LEFT OUTER JOIN FRS_SurveyAnswer A2 ON Q2.QuestionId = A2.QuestionId AND R.SurveySessionId = A2.SessionId
    LEFT OUTER JOIN FRS_SurveyQuestion Q3 ON R.SurveyId = Q3.SurveyId AND Q3.PositionInSurvey = 2
    LEFT OUTER JOIN FRS_SurveyAnswer A3 ON Q3.QuestionId = A3.QuestionId AND R.SurveySessionId = A3.SessionId
    LEFT OUTER JOIN FRS_SurveyQuestion Q4 ON R.SurveyId = Q4.SurveyId AND Q4.PositionInSurvey = 3
    LEFT OUTER JOIN FRS_SurveyAnswer A4 ON Q4.QuestionId = A4.QuestionId AND R.SurveySessionId = A4.SessionId
    LEFT OUTER JOIN FRS_SurveyQuestion Q5 ON R.SurveyId = Q5.SurveyId AND Q5.PositionInSurvey = 4
    LEFT OUTER JOIN FRS_SurveyAnswer A5 ON Q5.QuestionId = A5.QuestionId AND R.SurveySessionId = A5.SessionId
    ORDER BY R.DateSubmitted DESC;