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 ==