Hi All,
I"m a beginner in the world of SQL. I've made a basic query to find all our survey responses to the postcode / country question in last FY.
However, the results aren't showing what I'm expecting.
>They don't show any numbers above 3000 (postcodes go up to 9999 in australia) and
>they don't show any countries.
I've checked that this question is assigned as #1 in the TR_SURVEY_QUESTION TABLE
QUERY:
SELECT * FROM [dbo].[T_SURVEY_RESPONSE] WHERE question_no=1 AND create_dt BETWEEN '2017-07-01' AND '2018-06-30';
SAMPLE OF RESULTS:
We can get this info in T-stats but keen to compare in SSMS.
Thanks for any advice!
When you fill in the survey question, are you making entry directly into the field, or are you selecting a value from a dropdown? If the latter, then the value saved to the response table is the ID of the table used as the reference for the dropdown. Check the TR_SURVEY_QUESTION table and note the Ref Table, and Ref Idcol columns. If they're filled in, then the value in the response table will be the selected value from that column and table.
HI Brian,
Thanks for this. The response (postcode or country name) is typed in manually by the ticket seller at point of purchase.
Hi Ronan,
Greetings from London, I hope Hobart is being kind to you!
You might also check that the data type for the question in TR_SURVEY_QUESTION is set for string values. Also, try taking out the part of your code which reads AND create_dt BETWEEN '2017-07-01' AND '2018-06-30' so it checks ALL responses for that question. Try the following which checks for data not containing digits, telling you if any countries exist as answers:
select * from T_SURVEY_RESPONSEwhere question_no = 1and answer not like '%[0-9]%'
Cheers,
Kevin
If your goal is just to report on these survey responses, you could consider configuring T-Stats to grab the questions you're interested in. It would then show you both the question and answer in T-Stats. You just need to use a System Table to tell T-Stats which questions you want included. By default T-Stats does not grab survey responses, but can be set to take up to 10 questions. There are more details on this functionality here: https://community.tessituranetwork.com/topical_groups/tstats/w/tstats-wiki/578/can-we-analyze-order-survey-responses-in-t-stats
I realize that won't necessarily shed light on what changes you may need to make to your own query, but it may obviate the need for it.
Thanks Kevin! Nice to hear from you.
We ended up going with T-Stats. Leaving this one in the mystery box for now as even with your sql code above, no luck getting the range I'd expect