-- Default Survey VTS tables:

select * from vts_tbVoter where surveyid = 5
select * from vts_tbVoterAnswers where voterid = 143
select * from vts_tbAnswer
select * from vts_tbQuestion where surveyid = 5


-- To identify survey ID: 

Select * From dbo.vts_tbSurvey


-- List of QUERIES to create an overview 
-- of one survey/users/questions/answers details:

-- NR. 1 Temptabel 01 defaultquestions

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

into #temp01_question

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 = 5
and q1.selectionmodeid <> 5		-- static text 
and q1.parentquestionid is null         -- answeroptions

order by q1.displayorder


-- check:
-- select * from #temp01_question


-- NR 2. Temptable 02 answers

select 

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

A.questionid,
A.answerid Aaid,

VA.answerid VAaid,
A.Answertext Aat,		
VA.answertext Vat		

into #temp02_answer

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 = 9
 V.surveyid = 5
and votedate is not null
order by  A.questionid, VA.answerid


-- test:
-- select * from #temp02_answer



-- NR 3. Temptable COMBINED 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_question V right join #temp02_answer A on V.qid1 = A.questionid
and vaaid = aaid

order by voterid, displayorder, qid1


-- check
-- select * from #temp03_qidupdate


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

select 

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

g.displayorder,

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

g.VAaid,
g.Aaid,

g.Aat,
convert(nvarchar(1000), g.Vat) as Vat

into FWS_survey  -- fixed table

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


-- checks:
-- select * from FWS_Survey


-- NR 5. update FWS_survey qid1

update FWS_Survey
set qid1 = qid2
where qid1 is null

update FWS_Survey
set qid2 = null
where qid2 = qid1 


-- check endresult:

select distinct * from FWS_survey order by voterid, qid1, vaaid


-- clean #temp tables
-- drop table #temp02_answer
-- drop table #temp01_question
-- drop table #temp03_qidupdate

-- clean fixed table
-- drop table FWS_Survey


-- ==END of QUERIES ==