Help Documents

Basic queries to extract Survey Answers from Database

Note: be carefull to apply without first reading and understanding the queries.....


-- QUERIES TO EXPORT Survey RESULTS

-- Open MSSqlserver query

-- select database:
use [databasename]


-- To identify surevy ID:
Select * From dbo.vts_tbSurvey


-- default tables:
select * from vts_tbVoter where surveyid =
select * from vts_tbVoterAnswers where voterid =
select * from vts_tbAnswer
select * from vts_tbQuestion where surveyid =

-- first user/ tester: voterid = .....
select VA.*, A.*
from vts_tbVoterAnswers VA left join vts_tbAnswer A on VA.answerid = A.answerid
where VA.voterid = ....


-- INSTRUCTIONS
-- Follow steps 1. to 6. (check in between results of #temptables)
-- copy endresult to Access or Excell
-- preceding every rerun remove table FWS_Survey: drop table FWS_Survey

drop table FWS_Survey


-- RUN QUERIES 1, 2, 3 one after the other:
-- NR. 1 Temptable 01 Questions

select
distinct
q1.displayorder,
q1.questionid qid1,
q1.questiontext qt1,
q2.questionid qid2,
q2.questiontext qt2

into #temp01_Questions

from vts_tbQuestion Q1
left join vts_tbQuestion Q2 on Q1.questionid = Q2.parentquestionid
left join vts_tbAnswer A on Q1.questionid = A.questionid
where

q1.surveyid = [...]
and q1.selectionmodeid <> 5
and q1.parentquestionid is null

order by q1.displayorder


-- test:
-- select * from #temp01_Questions


-- NR 2. Temptable 02 Answers

select

V.surveyid,
V.voterid,
V.startdate,
V.VoteDate,
datediff(ss, v.startdate, v.votedate)/60 as minutes,
V.IPSource,

A.questionid,
A.answerid Aaid,

VA.answerid VAaid,
A.Answertext Aat,-- answers to radiobutton questions
VA.answertext Vat -- answers to 100 pnt distribution

into #temp02_Answers

from

vts_tbAnswer A left join vts_tbVoterAnswers VA on A.answerid = VA.answerid
left join vts_tbvoter V on va.voterid = V.voterid
where
-- V.voterid = ...
V.surveyid = .....
and votedate is not null
order by A.questionid, VA.answerid


-- test:
-- select * from #temp02_Answers

 

-- NR 3. Temptable Questions and Answers:

select

surveyid,
voterid,
startdate,
VoteDate,
minutes,
IPSource,

isnull(displayorder, (select max(displayorder) from #temp01_Question) ) displayorder,

qid1,
qt1,
qid2,
qt2,

VAaid,
Aaid,

Aat,
Vat

into #temp03_qidupdate

from #temp01_Questions V right join #temp02_Answers A on V.qid2 = A.questionid
and vaaid = aaid

order by voterid, displayorder, qid2


-- test
-- select * from #temp03_qidupdate


-- NR 4. Temp04 Table #temp03_qidupdate + update qid

select

g.surveyid,
g.voterid,
g.startdate,
g.VoteDate,
g.minutes,
g.IPSource,

g.displayorder,

g.qid1,
g.qt1,
-- g.qid2,
t.questionid qid2,
g.qt2,

g.VAaid,
g.Aaid,

g.Aat,
g.Vat

into FWS_Survey

from #temp03_qidupdate g left join #temp02_Answers t on g.vaaid = t.vaaid
order by g.displayorder, g.qid1

-- test
-- select * from FWS_Survey

 

-- NR 5. update FWS_SQSurvey qid1

update FWS_Survey
set qid1 = qid2
where qid1 is null

update FWS_Survey
set qid2 = null
where qid2 = qid1


-- check:
select * from FWS_survey order by voterid

-- clean #temp tables
drop table #temp02_Answers
drop table #temp01_Questions
drop table #temp03_qidupdate

-- NR 6. Final Results
select * from FWS_SQsurvey

-- ==END of QUERIES ==

 


Previous   BackToCategory    9 of 9