If you are requested for an spreadsheet containing translations for other languages within the system, you can follow these steps to return the questions required.
Declare the Culture & Survey Required
Firstly, you need to declare the Culture of the translations to be returned and for what survey they are for.
For example, here to return the Russian Translations for the BCI Survey 2015:
DECLARE @CultureID int = (SELECT CultureID FROM Ref_Culture WHERE [Description] = 'Russian'),
@SurveyID int = (SELECT SurveyID FROM Ref_Survey WHERE Name = 'BCI Survey 2015')
Returning the BC70 Questions
Set the headings for the tables to be returned, in this case the Question Numbers, the English translations alongside the Culture Translations:
SELECT sq.Number AS [QuestionNumber], ISNULL(q.Question, qco.Question) AS English, ISNULL(qco.Question, q.Question) AS Russian
Returning the English questions from the Ref_Survey_Questions table, and joining this with the matching questions ID's from the culture tables for the culture ID specified above:
FROM Ref_Survey_Questions sq
LEFT JOIN Ref_Survey_Question_Text_Override sqto ON sq.SurveyQuestionID = sqto.SurveyQuestionID
LEFT JOIN Ref_Survey_Question_Text_Culture_Override sqtco ON sqto.SurveyQuestionID = sqtco.SurveyQuestionID
AND sqtco.CultureID = @CultureID
INNER JOIN Ref_Questions q ON sq.QuestionID = q.QuestionID
LEFT JOIN Ref_Question_Culture_Override qco ON q.QuestionID = qco.QuestionID
AND qco.CultureID = @CultureID
Finally restricting the query to the designated Survey ID and ordering by the BC70 question number:
WHERE sq.SurveyID = @SurveyID
ORDER BY sq.Number
Returning the Management Questions
Next, the Management Questions can be returned using the following script for the selected culture ID, again setting the headings as before. The Management questions are retrieved from the Ref_Survey_General_Questions table and matched with their translations.
SELECT ISNULL(rq.Question, rqco.Question) AS English, ISNULL(rqco.Question, rq.Question) AS Russian
FROM Ref_Questions rq
INNER JOIN Ref_Survey_General_Questions rsgq ON rsgq.QuestionID = rq.QuestionID
AND rsgq.SurveyID = @SurveyID
INNER JOIN Ref_Question_Type rqt ON rqt.QuestionTypeID = rq.QuestionTypeID
AND rqt.Name = 'Management Question'
INNER JOIN Ref_Question_Culture_Override rqco ON rqco.QuestionID = rq.QuestionID
AND rqco.CultureID = @CultureID
Returning the Service Climate Questions.
The Service Climate Questions are returned in the same way as the Management Questions from the General Questions tables and joined to their translations.
SELECT ISNULL(rq.Question, rqco.Question) AS English, ISNULL(rqco.Question, rq.Question) AS Russian
FROM Ref_Questions rq
INNER JOIN Ref_Survey_General_Questions rsgq ON rsgq.QuestionID = rq.QuestionID
AND rsgq.SurveyID = @SurveyID
INNER JOIN Ref_Question_Type rqt ON rqt.QuestionTypeID = rq.QuestionTypeID
AND rqt.Name = 'Service Climate Question'
INNER JOIN Ref_Question_Culture_Override rqco ON rqco.QuestionID = rq.QuestionID
AND rqco.CultureID = @CultureID
Finally, execute the script sections from above, and copy each of the results into a spreadsheet with their headers and return the spreadsheet to the requester.
Comments
0 comments
Please sign in to leave a comment.